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

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

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

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

一、Rollup?與Cube

兩者基本語法一致：

group?by?....

with??rollup?|?cube

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

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

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

如：

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

select?

case?grouping(C.bmname)

when?0?then?C.bmname

when?1?then??'工資總計(jì)'

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

結(jié)果：

部門名稱 部門工資

管理部 ?????702

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

客戶部 ?????1878

銷售部 ?????2200

工資總計(jì) ?10249

?

?

分組依據(jù)多列：

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

select?

case?grouping(C.bmname)

when?0?then?C.bmname

when?1?then??'工資總計(jì)'

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

結(jié)果：

部門名稱 部門工資

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

工資總計(jì) 5469

管理部 ?????702

工資總計(jì) ?702

銷售部 ?????2200

工資總計(jì) 2200

客戶部 ????1878

工資總計(jì) 1878

工資總計(jì) 10249

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

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

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

二、compute?與compute?by?

?

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

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

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

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

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

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

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

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

如：

Category South East West Total

X 100 ?0 ?20 ?100

Y 200? 300 ?50 ?550

Z 0 0 ??100???? 100

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

以及這種類別總銷售量。

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

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

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

有多種方法生成交叉表。

四、固定列交叉表查詢

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

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

3、使用透視

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

性能差，對(duì)于每個(gè)分組依據(jù)列的每個(gè)度量方式實(shí)例執(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ù)，這樣

查詢引擎可以將整個(gè)交叉表查詢作為單個(gè)基于數(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

這個(gè)查詢沒有使用任何Where子句來篩選RawData表中的數(shù)據(jù)，Group?by?子句根據(jù)類

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

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

一個(gè)地區(qū)的值。

3、使用透視

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

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

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

的列。

基本語法：

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

PIVOT

(聚合函數(shù))

條件

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

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

?