22,207
社区成员
发帖
与我相关
我的任务
分享
if not object_id('tb') is null
drop table tb
Go
Create table tb([c2] int,[c1] int,[uid] int,[uid2] int)
Insert tb
select 3,4,2,1 union all
select null,3,3,1 union all
select 3,4,1,2 union all
select 2,3,3,2 union all
select null,3,1,3 union all
select 2,3,2,3
Go
select c2,
c1,
min([Uid])[Uid],
max([Uid])[Uid2]
from(
Select [c2],[c1],[Uid] from tb
union
Select [c2],[c1],[Uid2] from tb
)t
group by c2,c1
/*
c2 c1 Uid Uid2
----------- ----------- ----------- -----------
NULL 3 1 3
2 3 2 3
3 4 1 2
*/
create table tb(c2 int,c1 int,uid int,uid2 int)
insert into tb values(3 ,4 ,2 ,1)
insert into tb values(NULL ,3 ,3 ,1)
insert into tb values(3 ,4 ,1 ,2)
insert into tb values(2 ,3 ,3 ,2)
insert into tb values(NULL ,3 ,1 ,3)
insert into tb values(2 ,3 ,2 ,3)
go
select m.* from tb m, tb n where m.uid = n.uid2 and m.uid2 = n.uid and m.uid < m.uid2
drop table tb
/*
c2 c1 uid uid2
----------- ----------- ----------- -----------
3 4 1 2
NULL 3 1 3
2 3 2 3
(所影响的行数为 3 行)
*/