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

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

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

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

<table> 
 <tbody> 
  <tr> 
   <td>数据库字样</td> 
  </tr> 
 </tbody> 
</table>

![这里写图片描述][70 1]

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

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

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

效果如下：  
![这里写图片描述][70 2]

<table> 
 <tbody> 
  <tr> 
   <td>代码实现1——依次按照住址，部门，性别三个条件进行分类汇总</td> 
  </tr> 
 </tbody> 
</table>

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

效果图：  
![这里写图片描述][70 3]

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

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

[70]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/29/1966da43dd394e89b8f368ccded149f0.png
[70 1]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/29/8f487c9bc3e24993ab95ddae3fd6e6fa.png
[70 2]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/29/8cd275f5ba65490a88c55034e1835fa7.png
[70 3]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/29/65e9fdb8467b4a1db37cb4d66b55c1fc.png
[http_www.runoob.com_sqlite_sqlite-functions.html]: http://www.runoob.com/sqlite/sqlite-functions.html
[https_download.csdn.net_download_aiming66_10483246]: https://download.csdn.net/download/aiming66/10483246