SQLSERVER 使用 ROLLUP 汇总数据，实现分组统计，总计（合计），小计-蒲公英云

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

怼烎@ 2024-02-18 21:12 158阅读 0赞

在工作中遇到了这样的一个问题，就是需要将查询出来的数据进行分类汇总。这里用到了with rollup。下面是效果图。
这里写图片描述

为了得到这样的效果，我们该如何做呢，我们来做这个小demo。

数据库字样

这里写图片描述

需求分析

本实例为demo，没有任何意义。仅仅是为了完成功能。
1、依次按照住址，部门，性别三个条件进行汇总，汇总出他们的月薪总金额，和纳税总金额。

代码实现1——依次按照住址，部门，性别三个条件显示他们的每人详细信息

select 
    emp.address as 住址,
    emp.dep as 部门,
    case when emp.sex='1' then '男'else '女' end as 性别,
    emp.name as 姓名,
    emp.monthly_pay as 税前月薪,
    emp.monthly_pay *emp.rate_duty as 纳税金额,
    emp.monthly_pay *(1-emp.rate_duty) as 实际月薪金额
from Employees emp
order by
    emp.address ,
    emp.dep ,
    emp.sex

效果如下：
这里写图片描述

代码实现1——依次按照住址，部门，性别三个条件进行分类汇总

select 
            CASE WHEN grouping(emp.address) = '1' THEN '总计'
                WHEN grouping(emp.address) = '0' AND grouping(emp.dep) = '1' THEN emp.address + '小计'
                ELSE emp.address 
            END 住址,
        --------------------------------------  
            CASE WHEN grouping(emp.dep) = '1' THEN ''
                WHEN grouping(emp.dep) = '0' AND grouping(emp.sex) = '1' THEN emp.dep + '小计'
                ELSE emp.dep
            END 部门,     
        ------------------------------------------------------------------
            case when emp.sex='1' then '男' when emp.sex='0' then '女' end as 性别, 
            sum(cast(emp.monthly_pay as int)) as 税前月薪,
            sum(emp.monthly_pay *emp.rate_duty) as 纳税金额,
            sum(emp.monthly_pay *(1-emp.rate_duty) )as 实际月薪金额
            from Employees emp
            group by
                emp.address,
                emp.dep,
                emp.sex
                with rollup
            order by
                emp.address desc,
                emp.dep desc,
                emp.sex desc

效果图：
这里写图片描述

总结：
1、with rollup 要和case when 一起搭配使用。
2、因为使用with rollup 要用到group by 进行分组，所以这样的汇总必须结合聚合函数，比如，sum ，max等等。常用的聚合函数参考菜鸟教程：http://www.runoob.com/sqlite/sqlite-functions.html
3、对于汇总，强烈建议，使用“小计”，“总计”这样的字，坚决不建议使用“合计”。因为从代码中我们可以看出来，我们使用了order by ，order by 是以汉字拼音排序的，有的是时候就容易出不来效果，尤其是，我们汇总完了再进行分页的时候。（小编因为这个问题，曾经郁闷了一个下午）

demo 数据库和sql脚本下载地址：
https://download.csdn.net/download/aiming66/10483246