declare @n table (i int,ii int,iii int)
insert into @n select 11,2,1
insert into @n select 1,2,13
insert into @n select 3,11,2
insert into @n select 2,12,11
insert into @n select 11,3,3
insert into @n select 11,2,3
select n,sum(counts) as 次数
from
(
(select i as n,count(i) as counts from @n group by i)
union all
(select ii,count(ii) from @n group by ii)
union all
(select iii,count(iii) from @n group by iii)
) as n
group by n
改正:
select id,sum(id) from (
select id1 id,count(id1) num from @a group by id1
union all
select id2 id, count(id2) num from @a group by id2
union all
select id3 id, count(id3) num from @a group by id3
) a
group by id
下面的效率应该高一些:
select id,sum(*) from (
select id1 id,count(id1) num from @a group by id1
union all
select id2 id, count(id2) num from @a group by id2
union all
select id3 id, count(id3) num from @a group by id3
) a
group by id
select 数据=isnull(a.id1,isnull(b.id2,c.id3))
,次数=sum(isnull(a.次数,0)+isnull(b.次数,0)+isnull(c.次数,0))
from(
select id1,次数=count(*) from @a group by id1
) a full join (
select id2,次数=count(*) from @a group by id2
) b on a.id1=b.id2 full join(
select id3,次数=count(*) from @a group by id3
) c on a.id1=c.id3
group by isnull(a.id1,isnull(b.id2,c.id3))
select 数据=isnull(a.id1,isnull(b.id2,c.id3))
,次数=sum(isnull(a.次数,0)+isnull(b.次数,0)+isnull(c.次数,0))
from(
select id1,次数=count(*) from MYTable group by id1
) a full join (
select id2,次数=count(*) from MYTable group by id2
) b on a.id1=b.id2 full join(
select id3,次数=count(*) from MYTable group by id3
) c on a.id1=c.id3
group by isnull(a.id1,isnull(b.id2,c.id3))