有难度哦？怎么把三个字段中的相同数据一起统计？

[wei_gogo]

我有三个字段
MYTable(id1,id2,id3)都是int型的。
怎么统计在这三个字段中所有出现过的数据的次数
比如
id1 id2 id3
1 2 4
1 1 2
2 4 1

统计的结果为
数据 次数
1 4
2 3
4 2

[j9988]

declare @a table (id1 int, id2 int, id3 int)
insert @a select 1, 2, 4
insert @a select 1, 1, 2
insert @a select 2, 4, 1


select id,count(*) from (
select id1 as id from @a
union all
select id2 as id from @a
union all
select id3 as id from @a
) a
group by id

[j9988]

wei_gogo?

你叫喂狗狗？哈哈

[welyngj]

学习中......

[zjcxc]

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))

[zjcxc]

数据测试:

declare @a table (id1 int, id2 int, id3 int)
insert @a select 1, 2, 4
insert @a select 1, 1, 2
insert @a select 2, 4, 1

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))

[wei_gogo]

j9988(j9988) ,谢谢你，我的很多朋友都这样笑我的。

[wei_gogo]

如果我在VC中，如何来执行你们上面写的语句呢？
不会是sql = "declare @a table............
.............................................group by id"
然后m_pConn->Excute(sql,....)吧？

[j9988]

晕。你在VC中只要执行下面就行了。把＠A改成你的表。

select id,count(*) from (
select id1 as id from @a
union all
select id2 as id from @a
union all
select id3 as id from @a
) a
group by id

[WQLu]

下面的效率应该高一些:
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

[WQLu]

改正:
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

[aierong]

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