--
统计交叉相等两列元祖的次数 去掉重复
if object_id ( ' [tb] ' ) is not null drop table [ tb ]
go
create table [ tb ] ( [ num1 ] varchar ( 10 ), [ num2 ] varchar ( 10 ))
insert [ tb ]
select ' a ' , ' b ' union all
select ' b ' , ' c ' union all
select ' b ' , ' a ' union all
select ' c ' , ' b ' union all
select ' c ' , ' d ' union all
select * from tb
select t.num1,t.num2, count ( * ) as 重复
from (
select num1,num2 from tb
union all
select num2,num1 from tb
) t
where t.num1 < t.num2
group by t.num1,t.num2
/*
num1 num2
a b
b c
b a
c b
c d
----------------------------
num1 num2 (重复)
a b 2
b c 2
c d 1
if object_id ( ' [tb] ' ) is not null drop table [ tb ]
go
create table [ tb ] ( [ num1 ] varchar ( 10 ), [ num2 ] varchar ( 10 ))
insert [ tb ]
select ' a ' , ' b ' union all
select ' b ' , ' c ' union all
select ' b ' , ' a ' union all
select ' c ' , ' b ' union all
select ' c ' , ' d ' union all
select * from tb
select t.num1,t.num2, count ( * ) as 重复
from (
select num1,num2 from tb
union all
select num2,num1 from tb
) t
where t.num1 < t.num2
group by t.num1,t.num2
/*
num1 num2
a b
b c
b a
c b
c d
----------------------------
num1 num2 (重复)
a b 2
b c 2
c d 1
*/
本文转自曾祥展博客园博客，原文链接：http://www.cnblogs.com/zengxiangzhan/archive/2010/01/03/1638150.html，如需转载请自行联系原作者
