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

在工作中遇到了这样的一个问题,就是需要将查询出来的数据进行分类汇总。这里用到了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

相关文章
相关标签/搜索