上代码
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 就是用的 十进制那列， 是可以通过二进制那列转换来的
