上代碼
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實(shí)現(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實(shí)現(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 '十進(jìn)制',
CAST(GROUPING(year(billdate)) AS CHAR(1)) + CAST(GROUPING(MONTH(billdate)) AS CHAR(1)) + CAST(GROUPING(day(billdate)) AS CHAR(1)) as '二進(jìn)制',
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 就是用的 十進(jìn)制那列， 是可以通過二進(jìn)制那列轉(zhuǎn)換來的
