-- 统计交叉相等两列元祖的次数 去掉重复
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
*/