SQL多级汇总统计

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