两个字段A和B，不管A=1，B=2, 还是B=2,A=1 都group by 一条出来

[goodgame365]

如题，不知能不能用一条查询语句实现，只取一条出来就可以了，请高人指点，万分感谢！

如

ID A B
1 1 2
2 2 1
------------------------
查询后,只要一条记录就可以了
1 1 2

[文盲老顾]

top 1 ????

[笑着宣泄悲伤]

select top 1 * from yourtable

[文盲老顾]

with t as (
	select 1 as id,1 as a,2 as b
	union all
	select 2,2,1
)
select * from t a
cross apply (
	select (
		select ','+convert(varchar,val)
		from t
		unpivot(val for col in (a,b)) p
		where id=a.id
		order by val
		for xml path('')
	) as group_val
) b

不知道你是不是这个想法，group_val相同即认为所有值相同

[goodgame365]

引用 2 楼 笑着宣泄悲伤 的回复:

select top 1 * from yourtable

可能我没表达清楚，我再补充下，看看大家看得明白我的问题没，我换个说法，比如 A=“张三”，B="李四" 这条记录，和 A=“李四”，B="张三" 这条记录，程序都认为是相同的条件，所以只取其中一条就可以了，忽略其他的 ID A B 1 张三 李四 2 李四 张三 3 刘晶 周杰 按我的构思查询后，我想得出这样的结果 ID A B 1 张三 李四 3 刘晶 周杰

[文盲老顾]

with t as (
    select 4 as id,'1' as a,'2' as b
    union all select 5,'2','1'
    union all select 1,'张三','李四'
    union all select 2,'李四','张三'
    union all select 3,'刘晶','周杰'
)
select t.* from (
	select MIN(id) as id 
	from t a
	cross apply (
		select (
			select ','+convert(varchar,val)
			from t
			unpivot(val for col in (a,b)) p
			where id=a.id
			order by val
			for xml path('')
		) as group_val
	) b
	group by group_val
) a
left join t on a.id=t.id
order by t.id

[RINK_1]

CREATE TABLE #T
(ID INT IDENTITY(1,1),
 NAME_1 NVARCHAR(10),
 NAME_2 NVARCHAR(10))

INSERT INTO #T
SELECT '张三','李四' UNION ALL
SELECT '李四','张三' UNION ALL
SELECT '刘晶','周杰'

SELECT MIN(ID) AS ID,NAME_1,NAME_2 
FROM 
(SELECT ID,
CASE WHEN NAME_1<NAME_2 THEN NAME_1 ELSE NAME_2 END AS NAME_1,
CASE WHEN NAME_1>NAME_2 THEN NAME_1 ELSE NAME_2 END AS NAME_2 
FROM #T) AS A
GROUP BY NAME_1,NAME_2

[goodgame365]

引用 5 楼 文盲老顾 的回复:

with t as (
    select 4 as id,'1' as a,'2' as b
    union all select 5,'2','1'
    union all select 1,'张三','李四'
    union all select 2,'李四','张三'
    union all select 3,'刘晶','周杰'
)
select t.* from (
	select MIN(id) as id 
	from t a
	cross apply (
		select (
			select ','+convert(varchar,val)
			from t
			unpivot(val for col in (a,b)) p
			where id=a.id
			order by val
			for xml path('')
		) as group_val
	) b
	group by group_val
) a
left join t on a.id=t.id
order by t.id

非常感谢这位前辈，需要不明白俱体细节，依葫芦照搬，的确是我想要的结果，给力，自己加了分页，完美查询！