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和游標實現。

?