17、SQL Server 摘要資料之計算總計資料

SQL Server 摘要資料之計算總計資料

雖然Reportin Service能夠輕鬆地計算總計和小計，而無須查詢做額外的工作。但實

踐表明，嚮應用程式提供總計，讓其在表單或網頁底端顯示它可能很有用。

接下來的三個彙總命令將可以提供很好的解決方案。

一、Rollup 與Cube

兩者基本文法一致：

group by ....

with  rollup | cube

彙總函式Rollup 和Cube在一個獨立的行中計算出小計和總計，並在分組依據列中包

含空值，以指出對應的值為總計。Rollup產生分組依據列的小計和合計列，而Cube擴充

了這種功能，為每個分組依據列產生總計和小計行，有一個名為grouping()的函數，它在行為總計或小計時返回真（1），否則返回0。

如：

--查出工資，並根據部門進行分組，得出總計小計

select 

case grouping(C.bmname)

when 0 then C.bmname

when 1 then  '工資總計'

end as 部門名稱

,sum(A.basic_gz+A.jiaban_gz+A.jiangjin) 部門工資 from gongzi A 

inner join yuangong B on A.ygid=B.id

inner join bumen C on B.bmID=c.id

group by C.bmname

with Rollup

--如果group by有多列，則還會返回小計，不只是總計

結果：

部門名稱 部門工資

管理部      702

技術部      5469

客戶部      1878

銷售部      2200

工資總計  10249

 

 

分組依據多列：

--查出工資，並根據部門進行分組，得出總計小計

select 

case grouping(C.bmname)

when 0 then C.bmname

when 1 then  '工資總計'

end as 部門名稱

,sum(A.basic_gz+A.jiaban_gz+A.jiangjin) 部門工資 from gongzi A 

inner join yuangong B on A.ygid=B.id

inner join bumen C on B.bmID=c.id

group by C.ID,C.bmname

with Rollup

--如果group by有多列，則還會返回小計，不只是總計

結果：

部門名稱 部門工資

技術部     5469

工資總計 5469

管理部      702

工資總計  702

銷售部      2200

工資總計 2200

客戶部     1878

工資總計 1878

工資總計 10249

註：rollup放在group by 子句的後面，命令SQL Server產生一個合計列

如果使用的是cube，那麼合計列與小計行的顯示全部放在最後，且支援像rollup分組小計

，直接跟著分組後。這是對rollup的一個擴充。

二、compute 與compute by 

 

Compute子句，不是建立彙總查詢，而是在常規查詢後面添加一個彙總查詢。該查詢

返回一個包含詳細資料行的常規結果集，然後加上幾行，其中包含該結果集的匯總資訊。

compute  彙總函式(列名1)，彙總函式（列名2） [by]  分組依據列

註：加上了by就有了分組小計，而不只是總計,且不可與group by 共同使用，這是為了

向後相容才提供的，一般使用Rollup 與cube

三、建立交叉資料表查詢

雖然彙總查詢能夠根據多個列進行分組，但結果分行排列的，不太方便快速查看資料

。交叉資料表查詢將分組依據列（或一維）逆時針旋轉90°，將其變成結果集中的列。

如：

Category South East West Total

X 100  0  20  100

Y 200  300  50  550

Z 0 0   100     100

這是一個根據種類來進行分組，其中每個小組計算的是這種類別各個地區對應的銷售量，

以及這種類別總銷售量。

局限性：分行排列的Group by查詢可以有多個彙總函式，而交叉查詢只能顯示一種

度量方式（只能計算一種彙總函式）。

註：術語：交叉資料表查詢，描述的是結果集的外觀，而不是建立交叉表的方法。

有多種方法產生交叉表。

四、固定列交叉資料表查詢

建立包含已知的固定列交叉資料表查詢的方法有三種。

1、使用相互關聯的子查詢。2、使用Case運算式

3、使用透視

1、使用相互關聯的子查詢

效能差，對於每個分組依據列的每個度量方式執行個體執行一次子查詢。

如：

Select R.Category,

(select Sum(Amount) from RawData where Region='South' and 

Category=R.category ) as 'South',

(select Sum(Amount) from RawData where Region='North' and 

Category=R.category ) as 'North',

(select Sum(Amount) from RawData where Region='East' and 

Category=R.category ) as 'East',

(select Sum(Amount) from RawData where Region='West' and 

Category=R.category ) as 'West',

Sum(Amount) as Total

from RawData R

group by Category

2、使用Case運算式

使用Case運算式來篩選將被匯總的資料，而不是在相互關聯的子查詢中篩選資料，這樣

查詢引擎可以將整個交叉資料表查詢作為單個基於資料集的操作進行處理。

註：最適用方便的建立交叉資料表查詢的方法

如：

Select R.Category,

sum(Case Region when 'South' then Amount else 0 end) as South,

sum(Case Region when 'North' then Amount else 0 end) as North,

sum(Case Region when 'East' then Amount else 0 end) as East,

sum(Case Region when 'West' then Amount else 0 end) as West,

Sum(Amount) as Total

from RawData R

group by Category

order by Category

這個查詢沒有使用任何Where子句來篩選RawData表中的資料，Group by 子句根據類

別對資料集進行劃分。然後執資料列彙總函式為每個類別建立一個結果行。

註：該查詢使用了一個技巧，在Sum()中使用了一個Case運算式，這樣每列只考慮

一個地區的值。

3、使用透視

這種方法它在From子句執資料列彙總函式。並建立一個交叉表，將其作為資料來源，

如將Pivot看作一個用作資料來源的資料表值函式，則它接受兩個參數。

第一個參數是用於對交叉表的值進行計算的彙總函式，第一個參數列出了被透視

的列。

基本文法：

(資料來源)

PIVOT

(彙總函式)

條件

五、動態交叉資料表查詢

使用PIVOT和遊標實現。