sql查询语句中分组添加小计，GROUPING的使用

CREATE TABLE #tb_student_score(
c_grade NVARCHAR(10),
c_class NVARCHAR(10),
c_student NVARCHAR(10),
c_subject NVARCHAR(10),
c_score DECIMAL(10,1)
)
GO

INSERT INTO #tb_student_score(c_grade,c_class,c_student,c_subject,c_score)
VALUES ('一年级','一班','学生甲一','语文',95)
GO
INSERT INTO #tb_student_score(c_grade,c_class,c_student,c_subject,c_score)
VALUES ('一年级','一班','学生甲一','数学',90)
GO
INSERT INTO #tb_student_score(c_grade,c_class,c_student,c_subject,c_score)
VALUES ('一年级','一班','学生乙一','语文',85)
GO
INSERT INTO #tb_student_score(c_grade,c_class,c_student,c_subject,c_score)
VALUES ('一年级','一班','学生乙一','数学',80)
GO
INSERT INTO #tb_student_score(c_grade,c_class,c_student,c_subject,c_score)
VALUES ('一年级','二班','学生甲二','语文',96)
GO
INSERT INTO #tb_student_score(c_grade,c_class,c_student,c_subject,c_score)
VALUES ('一年级','二班','学生甲二','数学',88)
GO
INSERT INTO #tb_student_score(c_grade,c_class,c_student,c_subject,c_score)
VALUES ('一年级','二班','学生乙二','语文',93)
GO
INSERT INTO #tb_student_score(c_grade,c_class,c_student,c_subject,c_score)
VALUES ('一年级','二班','学生乙二','数学',79)
GO
INSERT INTO #tb_student_score(c_grade,c_class,c_student,c_subject,c_score)
VALUES ('二年级','一班','学生丙一','语文',79)
GO
INSERT INTO #tb_student_score(c_grade,c_class,c_student,c_subject,c_score)
VALUES ('二年级','一班','学生丙一','数学',98)
GO
INSERT INTO #tb_student_score(c_grade,c_class,c_student,c_subject,c_score)
VALUES ('二年级','一班','学生丁一','语文',86)
GO
INSERT INTO #tb_student_score(c_grade,c_class,c_student,c_subject,c_score)
VALUES ('二年级','一班','学生丁一','数学',78)
GO
INSERT INTO #tb_student_score(c_grade,c_class,c_student,c_subject,c_score)
VALUES ('二年级','二班','学生丙二','语文',92)
GO
INSERT INTO #tb_student_score(c_grade,c_class,c_student,c_subject,c_score)
VALUES ('二年级','二班','学生丙二','数学',88)
GO
INSERT INTO #tb_student_score(c_grade,c_class,c_student,c_subject,c_score)
VALUES ('二年级','二班','学生丁二','语文',91)
GO
INSERT INTO #tb_student_score(c_grade,c_class,c_student,c_subject,c_score)
VALUES ('二年级','二班','学生丁二','数学',97)

GO

–基础数据
SELECT * FROM #tb_student_score

–按年级+班级+科目汇总，按年级小计
SELECT
c_grade,c_class,c_subject,SUM(c_score)
FROM #tb_student_score
GROUP BY GROUPING SETS ((c_grade,c_class,c_subject),ROLLUP(c_grade))

–按年级+班级+科目汇总，按年级小计, 小计行显示”小计”
SELECT
CASE WHEN c_grade IS NULL THEN ‘总计’ ELSE c_grade END c_grade,
CASE WHEN c_class IS NULL AND c_grade IS NOT NULL THEN ‘小计’
WHEN c_class IS NOT NULL THEN c_class END c_class,
c_subject,SUM(c_score)
FROM #tb_student_score
GROUP BY GROUPING SETS ((c_grade,c_class,c_subject),ROLLUP(c_grade))

–按年级+班级+科目汇总，按年级+班级小计
SELECT
c_grade,c_class,c_subject,SUM(c_score)
FROM #tb_student_score

GROUP BY GROUPING SETS ((c_grade,c_class,c_subject),ROLLUP(c_grade,c_class))