SQL?Server?匯總數(shù)據(jù)之計算總計數(shù)據(jù)

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

踐表明，向應(yīng)用程序提供總計，讓其在窗體或網(wǎng)頁底端顯示它可能很有用。

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

一、Rollup?與Cube

兩者基本語法一致：

group?by?....

with??rollup?|?cube

聚合函數(shù)Rollup?和Cube在一個獨立的行中計算出小計和總計，并在分組依據(jù)列中包

含空值，以指出對應(yīng)的值為總計。Rollup生成分組依據(jù)列的小計和總計行，而Cube擴(kuò)展

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

如：

--查出工資，并根據(jù)部門進(jìn)行分組，得出總計小計

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有多列，則還會返回小計，不只是總計

結(jié)果：

部門名稱 部門工資

管理部 ?????702

技術(shù)部 ?????5469

客戶部 ?????1878

銷售部 ?????2200

工資總計 ?10249

?

?

分組依據(jù)多列：

--查出工資，并根據(jù)部門進(jìn)行分組，得出總計小計

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有多列，則還會返回小計，不只是總計

結(jié)果：

部門名稱 部門工資

技術(shù)部 ????5469

工資總計 5469

管理部 ?????702

工資總計 ?702

銷售部 ?????2200

工資總計 2200

客戶部 ????1878

工資總計 1878

工資總計 10249

注：rollup放在group?by?子句的后面，命令SQL?Server生成一個總計行

如果使用的是cube，那么總計行與小計行的顯示全部放在最后，且支持像rollup分組小計

，直接跟著分組后。這是對rollup的一個擴(kuò)展。

二、compute?與compute?by?

?

Compute子句，不是創(chuàng)建聚合查詢，而是在常規(guī)查詢后面添加一個聚合查詢。該查詢

返回一個包含明細(xì)數(shù)據(jù)行的常規(guī)結(jié)果集，然后加上幾行，其中包含該結(jié)果集的匯總信息。

compute??聚合函數(shù)(列名1)，聚合函數(shù)（列名2）?[by]??分組依據(jù)列

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

向后兼容才提供的，一般使用Rollup?與cube

三、創(chuàng)建交叉表查詢

雖然聚合查詢能夠根據(jù)多個列進(jìn)行分組，但結(jié)果分行排列的，不太方便快速查看數(shù)據(jù)

。交叉表查詢將分組依據(jù)列（或一維）逆時針旋轉(zhuǎn)90°，將其變成結(jié)果集中的列。

如：

Category South East West Total

X 100 ?0 ?20 ?100

Y 200? 300 ?50 ?550

Z 0 0 ??100???? 100

這是一個根據(jù)種類來進(jìn)行分組，其中每個小組計算的是這種類別各個地區(qū)對應(yīng)的銷售量，

以及這種類別總銷售量。

局限性：分行排列的Group?by查詢可以有多個聚合函數(shù)，而交叉查詢只能顯示一種

度量方式（只能計算一種聚合函數(shù)）。

注：術(shù)語：交叉表查詢，描述的是結(jié)果集的外觀，而不是創(chuàng)建交叉表的方法。

有多種方法生成交叉表。

四、固定列交叉表查詢

創(chuàng)建包含已知的固定列交叉表查詢的方法有三種。

1、使用相關(guān)子查詢。2、使用Case表達(dá)式

3、使用透視

1、使用相關(guān)子查詢

性能差，對于每個分組依據(jù)列的每個度量方式實例執(zhí)行一次子查詢。

如：

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表達(dá)式

使用Case表達(dá)式來篩選將被匯總的數(shù)據(jù)，而不是在相關(guān)子查詢中篩選數(shù)據(jù)，這樣

查詢引擎可以將整個交叉表查詢作為單個基于數(shù)據(jù)集的操作進(jìn)行處理。

注：最適用方便的創(chuàng)建交叉表查詢的方法

如：

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表中的數(shù)據(jù)，Group?by?子句根據(jù)類

別對數(shù)據(jù)集進(jìn)行劃分。然后執(zhí)行聚合函數(shù)為每個類別創(chuàng)建一個結(jié)果行。

注：該查詢使用了一個技巧，在Sum()中使用了一個Case表達(dá)式，這樣每列只考慮

一個地區(qū)的值。

3、使用透視

這種方法它在From子句執(zhí)行聚合函數(shù)。并創(chuàng)建一個交叉表，將其作為數(shù)據(jù)源，

如將Pivot看作一個用作數(shù)據(jù)源的表值函數(shù)，則它接受兩個參數(shù)。

第一個參數(shù)是用于對交叉表的值進(jìn)行計算的聚合函數(shù)，第一個參數(shù)列出了被透視

的列。

基本語法：

(數(shù)據(jù)源)

PIVOT

(聚合函數(shù))

條件

五、動態(tài)交叉表查詢

使用PIVOT和游標(biāo)實現(xiàn)。

?