SQL Server 事半功倍的group Grouping Sets与 GROUPING函数的应用

上代码

declare @bill  table
( id int identity(1,1),
  billdate varchar(10),
  salesman varchar(20),
  customer varchar(20),
  qty int
)  

insert into  @bill
select '2021-01-01','张三','北京客户',100 union all
select  '2021-02-01','张三','成都客户',90  union all
select '2021-03-01','张三','上海客户',80 union all

select '2021-01-01','李四','北京客户',222 union all
select  '2021-02-01','李四','成都客户',33  union all
select '2021-03-01','李四','上海客户',85 

有以下需求 我需要看2021年 客户 +销售员 按年汇总、客户按年汇总，销售员按年汇总，最终合计
等四个汇总信息

GROUPING1.png

需要用几个group by 再union all实现

select  customer,salesman,year(billdate),sum(qty),'按客户+销售员 年汇总' from @bill
group by customer,salesman,year(billdate)
 union all  
select  customer,null,year(billdate),sum(qty),'按客户 年汇总' from @bill
group by customer ,year(billdate)
union all
select  null,salesman,year(billdate),sum(qty),'按销售员 年汇总' from @bill
group by salesman,year(billdate)
union all
select  null,null,null,sum(qty),'总计' from @bill

用 Grouping Sets实现

select customer,salesman,YEAR(billdate) as year,sum(qty),GROUPING_ID(customer,salesman,YEAR(billdate)),
CASE WHEN GROUPING_ID(customer,salesman,YEAR(billdate)) = 0 THEN '按客户+销售员 年汇总' 
    WHEN GROUPING_ID(customer,salesman,YEAR(billdate)) = 2 THEN '按客户 年汇总' 
    WHEN GROUPING_ID(customer,salesman,YEAR(billdate)) = 4 THEN '按销售员 年汇总' 
    WHEN GROUPING_ID(customer,salesman,YEAR(billdate)) = 7 THEN '总计' 
END 
from @bill
GROUP BY GROUPING SETS
(
  (customer,salesman,year(billdate)),
  (customer,year(billdate)),
  (salesman,year(billdate)),
  ()
)

GROUPING SETS 子句很好理解，我就不讲了，特别难的是 GROUPING_ID的值。这个值放在 下一个语句中讲，一看就明白了

select year(billdate) as [year],MONTH(billdate) as [month],day(billdate) as [day],sum(qty) as qty,GROUPING_ID(year(billdate),MONTH(billdate),day(billdate)) as '十进制', 
CAST(GROUPING(year(billdate)) AS CHAR(1)) + CAST(GROUPING(MONTH(billdate)) AS CHAR(1)) + CAST(GROUPING(day(billdate)) AS CHAR(1)) as '二进制',
case when GROUPING_ID(year(billdate),MONTH(billdate),day(billdate)) = 0 then '按日汇总'
     when GROUPING_ID(year(billdate),MONTH(billdate),day(billdate)) = 1 then '按月汇总'
     when GROUPING_ID(year(billdate),MONTH(billdate),day(billdate)) = 3 then '按年汇总'
     when GROUPING_ID(year(billdate),MONTH(billdate),day(billdate)) = 7 then '总计'
end as ment
from @bill
group by rollup(year(billdate),MONTH(billdate),day(billdate))

grouping2.png

grouping_id 就是用的 十进制那列， 是可以通过二进制那列转换来的