MS-SQL 按年季度集锦

2013-01-09

MS-SQL 按年季度汇总本帖最后由 pctonc 于 2012-12-19 16:21:45 编辑表名：userTableidusernamecatalogcrea

MS-SQL 按年季度汇总
本帖最后由 pctonc 于 2012-12-19 16:21:45 编辑

表名：userTable
id  username  catalog  create_time
1   a_1       1       2012-1-10 16:16:26.123
2   a_2       2       2012-2-11 16:16:26.123
3   a_3       2       2012-2-5  16:16:26.123
4   a_4       3       2012-2-7  16:16:26.123
5   a_5       4       2012-1-12 16:16:26.123
6   a_6       3       2012-3-17 16:16:26.123
7   a_7       4       2012-4-5 16:16:26.123
8   a_8       1       2012-4-10 16:16:26.123
9   a_9       2       2012-5-10 16:16:26.123
10  a_10      3       2012-6-10 16:16:26.123
11  a_11      4       2012-7-20 16:16:26.123
12  a_12      5       2012-7-10 16:16:26.123
13  a_13      2       2012-8-10 16:16:26.123
14  a_14      5       2012-8-10 16:16:26.123
15  a_15      1       2012-7-10 16:16:26.123
16  a_16      2       2012-9-10 16:16:26.123
17  a_17      3       2012-8-10  16:16:26.123
18  a_17      3       2012-10-10 16:16:26.123
19  a_18      4       2012-11-10 16:16:26.123
20  a_19      5       2012-12-10 16:16:26.123

catalog=1 A人数
catalog=2 B人数
catalog=3 C人数
catalog=4 D人数
catalog=5 E人数

查询结果：统计2012年或某一年4个季度catalog分别为(A,B,C,D,E)人数，按季度这样显示
测试数据我就一，二季度给出具体人数，三四季度以此类推

-----------------------------
季度     项目         人数
------------------------------
|     |   A人数      1个
|     |   B人数      2个
| 一   |   C人数      2个
|     |   D人数      1个
|     |   E人数      null(这个记季度没有显示空就可以)

|     |   A人数      1个

[解决办法]

SELECT  CASE WHEN MONTH(create_time) BETWEEN 1 AND 3 THEN '1季度'
             WHEN MONTH(create_time) BETWEEN 4 AND 6 THEN '2季度'
             WHEN MONTH(create_time) BETWEEN 7 AND 9 THEN '3季度'
             WHEN MONTH(create_time) BETWEEN 10 AND 12 THEN '4季度'
        END AS 季度 ,
        CASE CATALOG
          WHEN 1 THEN 'A人数'
          WHEN 2 THEN 'B人数'
          WHEN 3 THEN 'C人数'
          WHEN 4 THEN 'D人数'
          WHEN 5 THEN 'E人数'
        END AS '类别' ,
        COUNT(1) AS '人数'
FROM    [userTable] A
GROUP BY CASE WHEN MONTH(create_time) BETWEEN 1 AND 3 THEN '1季度'
              WHEN MONTH(create_time) BETWEEN 4 AND 6 THEN '2季度'
              WHEN MONTH(create_time) BETWEEN 7 AND 9 THEN '3季度'
              WHEN MONTH(create_time) BETWEEN 10 AND 12 THEN '4季度'
         END ,
        CATALOG
ORDER BY CASE WHEN MONTH(create_time) BETWEEN 1 AND 3 THEN '1季度'
              WHEN MONTH(create_time) BETWEEN 4 AND 6 THEN '2季度'

              WHEN MONTH(create_time) BETWEEN 7 AND 9 THEN '3季度'
              WHEN MONTH(create_time) BETWEEN 10 AND 12 THEN '4季度'
         END ,
        CATALOG

/*
1季度A人数1
1季度B人数2
1季度C人数2
1季度D人数1
2季度A人数1
2季度B人数1
2季度C人数1
2季度D人数1
3季度A人数1
3季度B人数2
3季度C人数1
3季度D人数1
3季度E人数2
4季度C人数1
4季度D人数1
4季度E人数1*/

热点排行

SQL Server

MS-SQL 按年季度集锦