如何计算字段中各数字出现的次数

[fahe3]

db表,n1-n7为整数字段:
n1 n2 n3 n4 n5 n6 n7
7 15 20 22 25 26 28
1 14 28 29 32 33 34
2 4 13 15 18 23 36
10 13 17 19 20 30 35
11 13 14 16 22 26 28
2 5 7 11 16 21 30
5 6 19 25 31 32 35
15 19 25 27 28 30 33

如何用sql查询出n1->n7中各数字出现的次数.(不使用程序循环)

[laowang2]

id n1 n2 n3 n4 n5 n6 n7
1 7 15 20 22 25 26 28
2 1 14 28 29 32 33 34
3 2 4 13 15 18 23 36
4 10 13 17 19 20 30 35
5 11 13 14 16 22 26 28
6 2 5 7 11 16 21 30
7 5 6 19 25 31 32 35
8 15 19 25 27 28 30 33

select n1 ,count(id) as cnt group by n1
union all select n2 ,count(id) as cnt group by n2
union all select n3 ,count(id) as cnt group by n3
union all select n4 ,count(id) as cnt group by n4
union all select n5 ,count(id) as cnt group by n5
union all select n6 ,count(id) as cnt group by n6
union all select n7 ,count(id) as cnt group by n7

[WWWWA]

全部记录还是一条记录

[WWWWA]

全部记录:
select n1,count(*) as cs from (
SELECT n1 from cs
union all
SELECT n2 from cs
union all
SELECT n3 from cs
union all
SELECT n4 from cs
union all
SELECT n5 from cs
union all
SELECT n6 from cs
union all
SELECT n7 from cs) group by n1

[OracleRoob]

select 数字,count(*) as 次数
from 
    (
        select n1 as 数字 from db
        union all
        select n2 as 数字 from db
        union all
        select n3 as 数字 from db
        union all
        select n4 as 数字 from db
        union all
        select n5 as 数字 from db
        union all
        select n6 as 数字 from db
        union all
        select n7 as 数字 from db
    ) as t
group by 数字

[OracleRoob]

如果列数不固定，需要动态生成这个SQL语句。

[OracleRoob]

楼上的，ID哪儿来的？

[OracleRoob]

to 5楼：

而且，少from 表名，也没有对结果进行合并，结果应该是不对的。

[OracleRoob]

select 数字,count(*) as 次数
from 
    (
        select n1 as 数字 from db
        union all
        select n2 as 数字 from db
        union all
        select n3 as 数字 from db
        union all
        select n4 as 数字 from db
        union all
        select n5 as 数字 from db
        union all
        select n6 as 数字 from db
        union all
        select n7 as 数字 from db
    ) as t
group by 数字


--或者用楼上说的left join + right join 方式

这两种方式都可以

[laowang2]

呵呵,这种表肯定要建立一个自增列作为唯一主键,否则效率不佳,统计也困难.仅仅提供一个思路...

[OracleRoob]

有没有id,对于统计各个数字的次数，没有关系

[fahe3]

事实上确实还有一个id

好,回头试试上面的方法