USE AdventureWorks;
GO
SELECT SalesQuota, SUM(SalesYTD) 'TotalSalesYTD', GROUPING(SalesQuota) AS 'Grouping'
FROM Sales.SalesPerson
GROUP BY SalesQuota WITH ROLLUP;
GO
結果集在
SalesQuota
下面顯示兩個空值。第一個
NULL
代表從表中的這一列得到的空值組。第二個
NULL
位于 ROLLUP 操作所添加的彙總行之中。彙總行顯示所有
SalesQuota
組的
TotalSalesYTD
數量,并以
Grouping
列中的
1
進行訓示。
下面是結果集:
SalesQuota TotalSalesYTD Grouping
--------- ------------- --------
NULL 1533087.5999 0
250000.00 33461260.59 0
300000.00 9299677.9445 0
NULL 44294026.1344 1
(4 row(s) affected)
利用GROUPING來做分組彙總
在用SQL時,我們經常會碰到這樣的問題,要求分級求合計數,你是不是經常為怎麼在一張表裡分級統計而煩惱?這裡我們可以用GROUPING()函數來解決該問題。
下面用實際的例子來說明,例子是一個行政區、機關、銷售額的資料表。
--建立表并插入資料
Create Table T_SendMoney(StateCode varchar(6),DepCode varchar(6),SendMoney Money)
Insert Into T_SendMoney
Select '100001','310001',1000
UNION ALL
Select '100001','310001',2000
UNION ALL
Select '100001','310002',1500
UNION ALL
Select '100002','320001',3000
UNION ALL
Select '100002','320001',1200
UNION ALL
Select '100003','330001',1800
UNION ALL
Select '100003','330002',2100
UNION ALL
Select '100004','340001',2500
--按GROUPING來實作分級彙總資料
Select
CASE WHEN GROUPING(StateCode)=1 THEN 'Total:' ELSE StateCode END as StateCode
,CASE WHEN GROUPING(DepCode)=1 THEN 'State Total:' ELSE DepCode END as DepCode
,Sum(SendMoney) AS SendMoney
From T_SendMoney
GROUP BY StateCode,DepCode WITH ROLLUP
--查詢結果
StateCode DepCode SendMoney
-----------------------------------------
100001 310001 3000.00
100001 310002 1500.00
100001 State Total: 4500.00
100002 320001 4200.00
100002 State Total: 4200.00
100003 330001 1800.00
100003 330002 2100.00
100003 State Total: 3900.00
100004 340001 2500.00
100004 State Total: 2500.00
Total: State Total: 15100.00
----------------------------
WITH ROLLUP
----------------------------
先建立測試表、添加資料。
create table t(a int,b int,c int,d int,e int)
insert into t values(1,2,3,4,5)
insert into t values(1,2,3,4,6)
insert into t values(1,2,3,4,7)
insert into t values(1,2,3,4,8)
insert into t values(1,3,3,4,5)
insert into t values(1,3,3,4,6)
insert into t values(1,3,3,4,8)
insert into t values(1,3,3,4,7)
insert into t values(2,2,2,4,5)
insert into t values(2,2,3,4,6)
insert into t values(2,2,4,4,7)
insert into t values(2,2,5,4,8)
insert into t values(2,3,6,4,5)
insert into t values(2,3,3,4,6)
insert into t values(2,3,3,4,8)
insert into t values(2,3,3,4,7)
情況一:隻有一個分類彙總列時,隻需要一個合計。隻需要增加with rollup即可。
![](https://img.laitimes.com/img/_0nNw4CM6IyYiwiM6ICdiwiIml2ZuUmbv50LcNncvRXYjlGZul0ZulmbpxGd190LcNXZnFWbJ9CXt92YuM3ZvxmYuNmL3d3dvw1LcpDc0RHaiojIsJye.gif)
select case when grouping(a)=1 then '合計' else cast(a as varchar) end a,
![](https://img.laitimes.com/img/_0nNw4CM6IyYiwiM6ICdiwiIml2ZuUmbv50LcNncvRXYjlGZul0ZulmbpxGd190LcNXZnFWbJ9CXt92YuM3ZvxmYuNmL3d3dvw1LcpDc0RHaiojIsJye.gif)
sum(b),sum(c),sum(d),sum(e) from #t group by a with rollup
情況二:有多個分類彙總列,隻需要一個合計.增加rollup之後,需要增加判斷。
![](https://img.laitimes.com/img/_0nNw4CM6IyYiwiM6ICdiwiIml2ZuUmbv50LcNncvRXYjlGZul0ZulmbpxGd190LcNXZnFWbJ9CXt92YuM3ZvxmYuNmL3d3dvw1LcpDc0RHaiojIsJye.gif)
select case when grouping(a)=1 then '合計' else cast(a as varchar) end a,
![](https://img.laitimes.com/img/_0nNw4CM6IyYiwiM6ICdiwiIml2ZuUmbv50LcNncvRXYjlGZul0ZulmbpxGd190LcNXZnFWbJ9CXt92YuM3ZvxmYuNmL3d3dvw1LcpDc0RHaiojIsJye.gif)
b,
![](https://img.laitimes.com/img/_0nNw4CM6IyYiwiM6ICdiwiIml2ZuUmbv50LcNncvRXYjlGZul0ZulmbpxGd190LcNXZnFWbJ9CXt92YuM3ZvxmYuNmL3d3dvw1LcpDc0RHaiojIsJye.gif)
sum(c),sum(d),sum(e) from #t
![](https://img.laitimes.com/img/_0nNw4CM6IyYiwiM6ICdiwiIml2ZuUmbv50LcNncvRXYjlGZul0ZulmbpxGd190LcNXZnFWbJ9CXt92YuM3ZvxmYuNmL3d3dvw1LcpDc0RHaiojIsJye.gif)
group by a,b with rollup
![](https://img.laitimes.com/img/_0nNw4CM6IyYiwiM6ICdiwiIml2ZuUmbv50LcNncvRXYjlGZul0ZulmbpxGd190LcNXZnFWbJ9CXt92YuM3ZvxmYuNmL3d3dvw1LcpDc0RHaiojIsJye.gif)
having grouping(b)=0 or grouping(a)=1
![](https://img.laitimes.com/img/_0nNw4CM6IyYiwiM6ICdiwiIml2ZuUmbv50LcNncvRXYjlGZul0ZulmbpxGd190LcNXZnFWbJ9CXt92YuM3ZvxmYuNmL3d3dvw1LcpDc0RHaiojIsJye.gif)
select case when grouping(a)=1 then '合計' else cast(a as varchar) end a,
![](https://img.laitimes.com/img/_0nNw4CM6IyYiwiM6ICdiwiIml2ZuUmbv50LcNncvRXYjlGZul0ZulmbpxGd190LcNXZnFWbJ9CXt92YuM3ZvxmYuNmL3d3dvw1LcpDc0RHaiojIsJye.gif)
b,
![](https://img.laitimes.com/img/_0nNw4CM6IyYiwiM6ICdiwiIml2ZuUmbv50LcNncvRXYjlGZul0ZulmbpxGd190LcNXZnFWbJ9CXt92YuM3ZvxmYuNmL3d3dvw1LcpDc0RHaiojIsJye.gif)
c,
![](https://img.laitimes.com/img/_0nNw4CM6IyYiwiM6ICdiwiIml2ZuUmbv50LcNncvRXYjlGZul0ZulmbpxGd190LcNXZnFWbJ9CXt92YuM3ZvxmYuNmL3d3dvw1LcpDc0RHaiojIsJye.gif)
sum(d),sum(e) from #t
![](https://img.laitimes.com/img/_0nNw4CM6IyYiwiM6ICdiwiIml2ZuUmbv50LcNncvRXYjlGZul0ZulmbpxGd190LcNXZnFWbJ9CXt92YuM3ZvxmYuNmL3d3dvw1LcpDc0RHaiojIsJye.gif)
group by a,b,c with rollup
![](https://img.laitimes.com/img/_0nNw4CM6IyYiwiM6ICdiwiIml2ZuUmbv50LcNncvRXYjlGZul0ZulmbpxGd190LcNXZnFWbJ9CXt92YuM3ZvxmYuNmL3d3dvw1LcpDc0RHaiojIsJye.gif)
having grouping(c)=0 or grouping(a)=1
情況三:有多個分類彙總列,需要全部的小計和合計。
![](https://img.laitimes.com/img/_0nNw4CM6IyYiwiM6ICdiwiIml2ZuUmbv50LcNncvRXYjlGZul0ZulmbpxGd190LcNXZnFWbJ9CXt92YuM3ZvxmYuNmL3d3dvw1LcpDc0RHaiojIsJye.gif)
select case when grouping(a)=1 then '合計' else cast(a as varchar) end a,
![](https://img.laitimes.com/img/_0nNw4CM6IyYiwiM6ICdiwiIml2ZuUmbv50LcNncvRXYjlGZul0ZulmbpxGd190LcNXZnFWbJ9CXt92YuM3ZvxmYuNmL3d3dvw1LcpDc0RHaiojIsJye.gif)
case when grouping(b)=1 and grouping(a)=0 then '小計' else cast(b as varchar) end b,
![](https://img.laitimes.com/img/_0nNw4CM6IyYiwiM6ICdiwiIml2ZuUmbv50LcNncvRXYjlGZul0ZulmbpxGd190LcNXZnFWbJ9CXt92YuM3ZvxmYuNmL3d3dvw1LcpDc0RHaiojIsJye.gif)
case when grouping(c)=1 and grouping(b)=0 then '小計' else cast(c as varchar) end c,
![](https://img.laitimes.com/img/_0nNw4CM6IyYiwiM6ICdiwiIml2ZuUmbv50LcNncvRXYjlGZul0ZulmbpxGd190LcNXZnFWbJ9CXt92YuM3ZvxmYuNmL3d3dvw1LcpDc0RHaiojIsJye.gif)
sum(d),sum(e) from #t
![](https://img.laitimes.com/img/_0nNw4CM6IyYiwiM6ICdiwiIml2ZuUmbv50LcNncvRXYjlGZul0ZulmbpxGd190LcNXZnFWbJ9CXt92YuM3ZvxmYuNmL3d3dvw1LcpDc0RHaiojIsJye.gif)
group by a,b,c with rollup
另外一種顯示小計的方式
![](https://img.laitimes.com/img/_0nNw4CM6IyYiwiM6ICdiwiIml2ZuUmbv50LcNncvRXYjlGZul0ZulmbpxGd190LcNXZnFWbJ9CXt92YuM3ZvxmYuNmL3d3dvw1LcpDc0RHaiojIsJye.gif)
select case when grouping(a)=1 then '合計'
![](https://img.laitimes.com/img/_0nNw4CM6IyYiwiM6ICdiwiIml2ZuUmbv50LcNncvRXYjlGZul0ZulmbpxGd190LcNXZnFWbJ9CXt92YuM3ZvxmYuNmL3d3dvw1LcpDc0RHaiojIsJye.gif)
when grouping(b)=1 then cast(a as varchar)+'小計'
![](https://img.laitimes.com/img/_0nNw4CM6IyYiwiM6ICdiwiIml2ZuUmbv50LcNncvRXYjlGZul0ZulmbpxGd190LcNXZnFWbJ9CXt92YuM3ZvxmYuNmL3d3dvw1LcpDc0RHaiojIsJye.gif)
else cast(a as varchar) end a,
![](https://img.laitimes.com/img/_0nNw4CM6IyYiwiM6ICdiwiIml2ZuUmbv50LcNncvRXYjlGZul0ZulmbpxGd190LcNXZnFWbJ9CXt92YuM3ZvxmYuNmL3d3dvw1LcpDc0RHaiojIsJye.gif)
case when grouping(b)=0 and grouping(c)=1
![](https://img.laitimes.com/img/_0nNw4CM6IyYiwiM6ICdiwiIml2ZuUmbv50LcNncvRXYjlGZul0ZulmbpxGd190LcNXZnFWbJ9CXt92YuM3ZvxmYuNmL3d3dvw1LcpDc0RHaiojIsJye.gif)
then cast(b as varchar)+'小計' else cast(b as varchar) end b,
![](https://img.laitimes.com/img/_0nNw4CM6IyYiwiM6ICdiwiIml2ZuUmbv50LcNncvRXYjlGZul0ZulmbpxGd190LcNXZnFWbJ9CXt92YuM3ZvxmYuNmL3d3dvw1LcpDc0RHaiojIsJye.gif)
case when grouping(c)=1 and grouping(b)=0
![](https://img.laitimes.com/img/_0nNw4CM6IyYiwiM6ICdiwiIml2ZuUmbv50LcNncvRXYjlGZul0ZulmbpxGd190LcNXZnFWbJ9CXt92YuM3ZvxmYuNmL3d3dvw1LcpDc0RHaiojIsJye.gif)
then '' else cast(c as varchar) end c,
![](https://img.laitimes.com/img/_0nNw4CM6IyYiwiM6ICdiwiIml2ZuUmbv50LcNncvRXYjlGZul0ZulmbpxGd190LcNXZnFWbJ9CXt92YuM3ZvxmYuNmL3d3dvw1LcpDc0RHaiojIsJye.gif)
sum(d),sum(e) from #t
![](https://img.laitimes.com/img/_0nNw4CM6IyYiwiM6ICdiwiIml2ZuUmbv50LcNncvRXYjlGZul0ZulmbpxGd190LcNXZnFWbJ9CXt92YuM3ZvxmYuNmL3d3dvw1LcpDc0RHaiojIsJye.gif)
group by a,b,c with rollup
情況四:有多個分類彙總列,需要部分的小計和合計
![](https://img.laitimes.com/img/_0nNw4CM6IyYiwiM6ICdiwiIml2ZuUmbv50LcNncvRXYjlGZul0ZulmbpxGd190LcNXZnFWbJ9CXt92YuM3ZvxmYuNmL3d3dvw1LcpDc0RHaiojIsJye.gif)
select case when grouping(a)=1 then '合計' else cast(a as varchar) end a,
![](https://img.laitimes.com/img/_0nNw4CM6IyYiwiM6ICdiwiIml2ZuUmbv50LcNncvRXYjlGZul0ZulmbpxGd190LcNXZnFWbJ9CXt92YuM3ZvxmYuNmL3d3dvw1LcpDc0RHaiojIsJye.gif)
b,
![](https://img.laitimes.com/img/_0nNw4CM6IyYiwiM6ICdiwiIml2ZuUmbv50LcNncvRXYjlGZul0ZulmbpxGd190LcNXZnFWbJ9CXt92YuM3ZvxmYuNmL3d3dvw1LcpDc0RHaiojIsJye.gif)
case when grouping(c)=1 and grouping(b)=0 then '小計' else cast(c as varchar) end c,
![](https://img.laitimes.com/img/_0nNw4CM6IyYiwiM6ICdiwiIml2ZuUmbv50LcNncvRXYjlGZul0ZulmbpxGd190LcNXZnFWbJ9CXt92YuM3ZvxmYuNmL3d3dvw1LcpDc0RHaiojIsJye.gif)
sum(d),sum(e) from #t
![](https://img.laitimes.com/img/_0nNw4CM6IyYiwiM6ICdiwiIml2ZuUmbv50LcNncvRXYjlGZul0ZulmbpxGd190LcNXZnFWbJ9CXt92YuM3ZvxmYuNmL3d3dvw1LcpDc0RHaiojIsJye.gif)
group by a,b,c with rollup
![](https://img.laitimes.com/img/_0nNw4CM6IyYiwiM6ICdiwiIml2ZuUmbv50LcNncvRXYjlGZul0ZulmbpxGd190LcNXZnFWbJ9CXt92YuM3ZvxmYuNmL3d3dvw1LcpDc0RHaiojIsJye.gif)
having grouping(a)=1 or grouping(b)=0
![](https://img.laitimes.com/img/_0nNw4CM6IyYiwiM6ICdiwiIml2ZuUmbv50LcNncvRXYjlGZul0ZulmbpxGd190LcNXZnFWbJ9CXt92YuM3ZvxmYuNmL3d3dvw1LcpDc0RHaiojIsJye.gif)
select case when grouping(a)=1 then '合計' else cast(a as varchar) end a,
![](https://img.laitimes.com/img/_0nNw4CM6IyYiwiM6ICdiwiIml2ZuUmbv50LcNncvRXYjlGZul0ZulmbpxGd190LcNXZnFWbJ9CXt92YuM3ZvxmYuNmL3d3dvw1LcpDc0RHaiojIsJye.gif)
case when grouping(b)=1 and grouping(a)=0 then '小計' else cast(b as varchar) end b,
![](https://img.laitimes.com/img/_0nNw4CM6IyYiwiM6ICdiwiIml2ZuUmbv50LcNncvRXYjlGZul0ZulmbpxGd190LcNXZnFWbJ9CXt92YuM3ZvxmYuNmL3d3dvw1LcpDc0RHaiojIsJye.gif)
c,
![](https://img.laitimes.com/img/_0nNw4CM6IyYiwiM6ICdiwiIml2ZuUmbv50LcNncvRXYjlGZul0ZulmbpxGd190LcNXZnFWbJ9CXt92YuM3ZvxmYuNmL3d3dvw1LcpDc0RHaiojIsJye.gif)
sum(d),sum(e) from #t
![](https://img.laitimes.com/img/_0nNw4CM6IyYiwiM6ICdiwiIml2ZuUmbv50LcNncvRXYjlGZul0ZulmbpxGd190LcNXZnFWbJ9CXt92YuM3ZvxmYuNmL3d3dvw1LcpDc0RHaiojIsJye.gif)
group by a,b,c with rollup
![](https://img.laitimes.com/img/_0nNw4CM6IyYiwiM6ICdiwiIml2ZuUmbv50LcNncvRXYjlGZul0ZulmbpxGd190LcNXZnFWbJ9CXt92YuM3ZvxmYuNmL3d3dvw1LcpDc0RHaiojIsJye.gif)
having grouping(a)=1 or grouping(b)=1 or grouping(c)=0