上代碼

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實現(xiàn)

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實現(xiàn)

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 就是用的 十進制那列， 是可以通過二進制那列轉(zhuǎn)換來的