SQLSERVER 使用 ROLLUP 汇总数据，实现分组统计，合计，小计

一时失言乱红尘 2021-09-30 15:12 453阅读 0赞

**1、只有一个分类统计列，只需要一个合计。只需要增加with rollup即可**

SELECT CASE WHEN GROUPING(GradeName)=1 THEN '合计' ELSE GradeName END AS 年级 ,
          SUM(CASE WHEN Sex=1 THEN 1 ELSE 0 END) AS 男生数,
          SUM(CASE WHEN Sex=0 THEN 1 ELSE 0 END) AS 女生数,
          COUNT(Sex) AS 总数
    FROM dbo.Students
    GROUP BY GradeName WITH ROLLUP
    ORDER BY GradeName DESC

![20190806145306333.png][]

** 2、有多个分类汇总列，只需要一个合计。增加rollup之后，需要使用GROUPING函数判断。**

GROUPING函数 指示是否聚合 GROUP BY 列表中的指定列表达式。 在结果集中，如果 GROUPING 返回 1 则指示聚合；返回 0 则指示不聚合。 如果指定了 GROUP BY，则 GROUPING 只能用在 SELECT <select> 列表、HAVING 和 ORDER BY 子句中。

SELECT CASE WHEN GROUPING(GradeName)=1 THEN '合计' ELSE GradeName END AS 年级 ,
            ClassName AS 班级 ,
          SUM(CASE WHEN Sex=1 THEN 1 ELSE 0 END) AS 男生数,
          SUM(CASE WHEN Sex=0 THEN 1 ELSE 0 END) AS 女生数,
          COUNT(Sex) AS 总数
    FROM dbo.Students
    GROUP BY GradeName,ClassName WITH ROLLUP
    HAVING GROUPING(GradeName)=1 OR GROUPING(ClassName)=0
    ORDER BY GradeName DESC

![20190806145238178.png][]

SELECT CASE WHEN GROUPING(GradeName)=1 THEN '合计' ELSE GradeName END AS 年级 ,
           ClassName AS 班级 ,
           Area AS 地区 ,
          SUM(CASE WHEN Sex=1 THEN 1 ELSE 0 END) AS 男生数,
          SUM(CASE WHEN Sex=0 THEN 1 ELSE 0 END) AS 女生数,
          COUNT(Sex) AS 总数
    FROM dbo.Students
    GROUP BY GradeName,ClassName,Area WITH ROLLUP
    HAVING GROUPING(GradeName)=1 OR (GROUPING(ClassName)=0 AND GROUPING(Area) =0)
    ORDER BY GradeName DESC

![20190806145217424.png][]

**3、有多个分类汇总列，需要显示全部的合计和小计。不需要增加判断。**

SELECT CASE WHEN GROUPING(GradeName)=1 THEN '合计' ELSE GradeName END AS 年级 ,
           CASE WHEN GROUPING(GradeName)=0 AND GROUPING(ClassName)=1 THEN '小计' ELSE ClassName END AS 班级 ,
          SUM(CASE WHEN Sex=1 THEN 1 ELSE 0 END) AS 男生数,
          SUM(CASE WHEN Sex=0 THEN 1 ELSE 0 END) AS 女生数,
          COUNT(Sex) AS 总数
    FROM dbo.Students
    GROUP BY GradeName,ClassName WITH ROLLUP
    ORDER BY GradeName DESC

![20190806145148369.png][]

** 4、有多个分类汇总列，需要显示部分的合计和小计。增加rollup之后，需要增加判断**

SELECT CASE WHEN GROUPING(GradeName)=1 THEN '年级合计' ELSE GradeName END AS 年级 ,
           CASE WHEN GROUPING(GradeName)=0 AND GROUPING(ClassName)=1 THEN '班级小计' ELSE ClassName END AS 班级 ,
           CASE WHEN GROUPING(ClassName)=0 AND GROUPING(Area)=1 THEN '地区小计' ELSE Area END AS 地区 ,
          SUM(CASE WHEN Sex=1 THEN 1 ELSE 0 END) AS 男生数,
          SUM(CASE WHEN Sex=0 THEN 1 ELSE 0 END) AS 女生数,
          COUNT(Sex) AS 总数,
          GROUPING(GradeName) AS GradeName_G,
          GROUPING(ClassName) AS ClassName_G,
          GROUPING(Area) AS Area_G
    FROM dbo.Students
    GROUP BY GradeName,ClassName,Area WITH ROLLUP
    HAVING GROUPING(GradeName)=1 OR GROUPING(Area)=0 OR GROUPING(ClassName)=0
    ORDER BY GradeName DESC

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzI4MjU2Nzgz_size_16_color_FFFFFF_t_70][]

[20190806145306333.png]: /images/20210724/becc2515a9a3402bb2aa07077691bff3.png
[20190806145238178.png]: /images/20210724/61569d71d2f84df3b437ecd5b38d349a.png
[20190806145217424.png]: /images/20210724/c3a878a71a1944c2a74556958eb25477.png
[20190806145148369.png]: /images/20210724/0eea29c34b5b48f49e7f691f2129263d.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzI4MjU2Nzgz_size_16_color_FFFFFF_t_70]: /images/20210724/f2e2b86fe2ce4b2293711ec14e513887.png