求一个SQL语句。对每一列分别汇总

[pcgenius]

表为三列，N1 ，N2 ，N3
每一列为0-9的随机数
汇总出N1 0-9出现多少次，N2 0-9出现多少次，N3 0-9出现多少次，

有个条件就是不能用三次GROUP BY 进行全连接
我认为有更好的SQL语句。请高手看一下。


其实就是福彩3D。我要统计每列每个数出现的次数

[linguojin11]

学习

[liangCK]

SELECT
    A.number,
    ISNULL(B.N1_CNT,0) AS N1_CNT,
    ISNULL(C.N2_CNT,0) AS N2_CNT,
    ISNULL(D.N3_CNT,0) AS N3_CNT
FROM 
(
    SELECT number = 0 UNION ALL
    SELECT number = 1 UNION ALL
    SELECT number = 2 UNION ALL
    SELECT number = 3 UNION ALL
    SELECT number = 4 UNION ALL
    SELECT number = 5 UNION ALL
    SELECT number = 6 UNION ALL
    SELECT number = 7 UNION ALL
    SELECT number = 8 UNION ALL
    SELECT number = 9 
) AS A
    LEFT JOIN (SELECT N1,COUNT(*) AS N1_CNT FROM tb GROUP BY N1) AS B
ON A.number = B.N1
    LEFT JOIN (SELECT N2,COUNT(*) AS N2_CNT FROM tb GROUP BY N2) AS C
ON A.number = C.N2
    LEFT JOIN (SELECT N3,COUNT(*) AS N3_CNT FROM tb GROUP BY N3) AS D
ON A.number = D.N3

[--小F--]

这个不用连接还真不好做 就用小梁的好了

[liangCK]

--生成测试数据
DECLARE @t TABLE(N1 INT,N2 INT,N3 INT);
INSERT @t 
    SELECT TOP(1000)
        ABS(CHECKSUM(NEWID())) % 10,
        ABS(CHECKSUM(NEWID())) % 10,
        ABS(CHECKSUM(NEWID())) % 10
    FROM sys.objects,sys.columns;

--SQL 查询如下:

SELECT
    A.number,
    ISNULL(B.N1_CNT,0) AS N1_CNT,
    ISNULL(C.N2_CNT,0) AS N2_CNT,
    ISNULL(D.N3_CNT,0) AS N3_CNT
FROM 
(
    SELECT number = 0 UNION ALL
    SELECT number = 1 UNION ALL
    SELECT number = 2 UNION ALL
    SELECT number = 3 UNION ALL
    SELECT number = 4 UNION ALL
    SELECT number = 5 UNION ALL
    SELECT number = 6 UNION ALL
    SELECT number = 7 UNION ALL
    SELECT number = 8 UNION ALL
    SELECT number = 9 
) AS A
    LEFT JOIN (SELECT N1,COUNT(*) AS N1_CNT FROM @t GROUP BY N1) AS B
ON A.number = B.N1
    LEFT JOIN (SELECT N2,COUNT(*) AS N2_CNT FROM @t GROUP BY N2) AS C
ON A.number = C.N2
    LEFT JOIN (SELECT N3,COUNT(*) AS N3_CNT FROM @t GROUP BY N3) AS D
ON A.number = D.N3

/*
number      N1_CNT      N2_CNT      N3_CNT
----------- ----------- ----------- -----------
0           109         107         88
1           101         109         110
2           108         109         94
3           108         100         107
4           93          95          92
5           88          95          98
6           93          78          116
7           92          114         91
8           98          107         102
9           110         86          102

(10 行受影响)
*/

[让你望见影子的墙]

[Quote=引用楼主 pcgenius 的帖子:]
表为三列，N1 ，N2 ，N3
每一列为0-9的随机数
汇总出N1 0-9出现多少次，N2 0-9出现多少次，N3 0-9出现多少次，

有个条件就是不能用三次GROUP BY 进行全连接
我认为有更好的SQL语句。请高手看一下。


其实就是福彩3D。我要统计每列每个数出现的次数
[/Quote]

select 次数1=sum(case when  n1 between 0 and 9 then 1 else 0),
       次数2=sum(case when  n2 between 0 and 9 then 1 else 0),
       次数3=sum(case when  n3 between 0 and 9 then 1 else 0)
feom tb

[让你望见影子的墙]

feom tb-->>from tb

[让你望见影子的墙]

e,理解的有歧义？是要总共0到9 的次数，还是每个数字的次数

[ai_li7758521]

[Quote=引用楼主 pcgenius 的帖子:]
有个条件就是不能用三次GROUP BY 进行全连接
[/Quote]貌似没有办法。每一列都得单独统计，怎么再减少呢。

[pcgenius]

TO liangCK
用了左连接，和全连接其实一样。可能没有更好的方法了。

不过从你的语句中看出你是高手。

你考虑问题很全面。包括没出现的数字都在其中了。


那就用这个吧。

[mhc1314]

向高手学习

[zqx_0432]

学习~~~~~~~~~~~~