SQL Server - ROLLUP、GROUPING、CUBE、GROUPING SET

写在前面：如果我们想要对分组之后的数据进行类似小计的计算，那么就需要使用到下面的函数

SQL Server - ROLLUP、GROUPING、CUBE、GROUPING SET

参考来源： https://www.cnblogs.com/linfangnan/p/14785644.html

ROLLUP函数

SELECT SNO,CNO,SUM(DEGREE) 总分  FROM SCORE GROUP BY SNO,CNO WITH ROLLUP

SELECT SNO,CNO,SUM(DEGREE) 总分
FROM SCORE GROUP BY SNO,CNO WITH ROLLUP
--也可以写成
SELECT SNO,CNO,SUM(DEGREE) 总分 FROM SCORE GROUP BY ROLLUP(SNO,CNO);

GROUPING函数

SELECT CASE WHEN (GROUPING(SNO)) = 1 THEN '所有总分' ELSE ISNULL(SNO,'UNKNOWN') END AS SNO,
CASE WHEN CNO IS NULL AND SNO IS NULL THEN '' ELSE ISNULL(CAST(CNO AS CHAR(8)),'小计') END AS CNO,
SUM(DEGREE) AS 成绩总和
FROM SCORE GROUP BY SNO,CNO WITH ROLLUP;

GROUPING SET函数

SELECT SNO,CNO,SUM(DEGREE) FROM SCORE  GROUP BY GROUPING SETS(SNO,CNO);

SELECT SNO,CNO,SUM(DEGREE) FROM SCORE  GROUP BY GROUPING SETS((SNO,CNO));

SELECT SNO,CNO,SUM(DEGREE) FROM SCORE  GROUP BY GROUPING SETS(SNO,CNO,(SNO,CNO));

CUBE函数

SELECT SNO,CNO,SUM(DEGREE) FROM SCORE GROUP BY CUBE(SNO,CNO);

网上例子

SELECT isnull(CAST(Sno aschar(8)), '总平均分') Sno,    
        --若该数据为所有分组的汇总数据，用“总平均分”替换 NULL
       CASE	WHEN  Sno isnull  AND  Cno ISnull	THEN ''	ELSE  isnull(CAST(CNO aschar(6)), '平均分') END CNO,
        --若该数据为某个分组的汇总数据，用“平均分”替换 NULLAVG(Degree)
FROM Score
GROUP BY GROUPING SETS (ROLLUP(Sno, Cno))

SELECT 
  CASE  WHEN  S.Sname isnull  AND  Cno ISnull THEN  '总平均分'  ELSE  isnull(CAST(Sname aschar(10)), '课程平均分') END    Sname,
   --总汇总数据用“总平均分”替换 NULL，Cno 字段汇总数据用“课程平均分”替换 NULL
  CASE  WHEN   Sname isnull  AND  Cno ISnull  THEN  ''  ELSE  isnull(CAST(Cno aschar(10)), '学生平均分') END
Cno,
       --若该数据为某个分组的汇总数据，汇总数据用“学生平均分”替换 NULLAVG(Degree) 成绩
FROM  Score SC
JOIN  Student  S ON S.Sno =  SC.Sno
GROUP BY  Sname, Cno WITH CUBE

SELECT  CASE  WHEN  SC.Sno ISnull  AND  SC.Cno ISnull  THEN  '总'  ELSE  isnull(CAST(SC.Sno aschar(10)), '课程') END    Sno,
    CASE  WHEN  SC.Sno ISnull   AND   SC.Cno ISnull  THEN  '平均分'  ELSE  isnull(CAST(S.Sname aschar(10)), '平均分') END    Sname,
    CASE  WHEN  SC.Sno ISnull  AND   SC.Cno ISnull  THEN  '' ELSE  isnull(CAST(SC.Cno aschar(10)), '学生') END    Cno,
    CASE  WHEN  SC.Sno ISnull  AND  SC.Cno ISnull  THEN  '' ELSE  isnull(CAST(C.Cname aschar(10)), '平均分') END    Cname,
    AVG(Degree) 成绩
FROM  Score SC
JOIN  Student S ON  S.Sno =  SC.Sno
JOIN  Course C ON  C.Cno  =   SC.Cno
GROUP  BY  GROUPING   SETS ((sc.Cno, sc.Sno, Sname, Cname), (SC.Sno, S.Sname), (SC.Cno, C.Cname))