背景:
阅读新闻

SQL多级汇总统计

  作者:mikebai.com 今日评论: [字体: ]

 

DB数据:

ID DEPT_ID ClassID M_YEAR M_Month M_SALARY M_SCORE
32 1 1 1 1 20 10
33 1 1 1 2 20 123
34 1 1 1 3 20 12
35 1 1 1 4 20 123
36 1 1 1 5 21 12
37 1 1 2 1 22 312
38 1 1 2 2 23 2
39 1 1 2 3 15 23
40 1 1 2 4 15 123
41 1 1 2 5 90 24
42 1 1 3 1 55 123
43 1 1 3 2 43 12
44 1 1 3 3 20 22
45 1 1 3 4 20 2
46 1 1 3 5 20 2
47 1 2 1 1 20 2
48 1 2 1 2 20 21
49 1 2 1 3 20 2
50 1 2 1 4 20 2
51 1 2 1 5 21 3123
52 1 2 2 1 22 3
53 1 2 2 2 23 23
54 1 2 2 3 15 123
55 1 2 2 4 15 54
56 1 2 2 5 90 123
57 1 2 3 1 55 787
58 1 2 3 2 43 12
59 1 2 3 3 20 32
60 1 2 3 4 20 435
61 1 2 3 5 20 234
62 2 1 1 1 20 10
63 2 1 1 2 20 123
64 2 1 1 3 20 12
65 2 1 1 4 20 123
66 2 1 1 5 21 12
67 2 1 2 1 22 312
68 2 1 2 2 23 2
69 2 1 2 3 15 23
70 2 1 2 4 15 123
71 2 1 2 5 90 24
72 2 1 3 1 55 123
73 2 1 3 2 43 12
74 2 1 3 3 20 22
75 2 1 3 4 20 2
76 2 1 3 5 20 2
77 2 2 1 1 20 2
78 2 2 1 2 20 21
79 2 2 1 3 20 2
80 2 2 1 4 20 2
81 2 2 1 5 21 3123
82 2 2 2 1 22 3
83 2 2 2 2 23 23
84 2 2 2 3 15 123
85 2 2 2 4 15 54
86 2 2 2 5 90 123
87 2 2 3 1 55 787
88 2 2 3 2 43 12
89 2 2 3 3 20 32
90 2 2 3 4 20 435
91 2 2 3 5 20 234

 

SQL文

select
case when grouping(DEPT_ID)=1 then 'zongheji'
else cast(DEPT_ID as varchar) end DEPT_ID,

case when grouping(ClassID)=1 and grouping(DEPT_ID)=0 then 'ClassIDxiaoji'
else cast(ClassID as varchar) end ClassID,

case when grouping(M_YEAR)=1 and grouping(ClassID)=0 and grouping(DEPT_ID)=0 then 'yearxiaoji'
else cast(M_YEAR as varchar) end M_YEAR,

sum(M_SALARY) as M_SALARY,sum(M_SCORE)  as M_SCORE,
grouping(DEPT_ID) as 'aFlag',grouping(ClassID) as 'bFlag',grouping(M_YEAR) as 'cFlag'
 from test2
group by DEPT_ID,ClassID,M_YEAR with rollup

显示结果

DEPT_ID ClassID M_YEAR M_SALARY M_SCORE aFlag bFlag cFlag
1 1 1 101 280 0 0 0
1 1 2 165 484 0 0 0
1 1 3 158 161 0 0 0
1 1 yearxiaoji 424 925 0 0 1
1 2 1 101 3150 0 0 0
1 2 2 165 326 0 0 0
1 2 3 158 1500 0 0 0
1 2 yearxiaoji 424 4976 0 0 1
1 ClassIDxiaoji   848 5901 0 1 1
2 1 1 101 280 0 0 0
2 1 2 165 484 0 0 0
2 1 3 158 161 0 0 0
2 1 yearxiaoji 424 925 0 0 1
2 2 1 101 3150 0 0 0
2 2 2 165 326 0 0 0
2 2 3 158 1500 0 0 0
2 2 yearxiaoji 424 4976 0 0 1
2 ClassIDxiaoji   848 5901 0 1 1
zongheji     1696 11802 1 1 1
来源:本站
录入日期:[2009/05/10 21:31:00]
收藏 推荐 打印 | 录入:mikebai | 阅读:
文章评论      
正在加载评论列表...
评论表单加载中...