SQLServer任意列之間的聚合

sql的max之類的聚合函數隻能針對同一列的n行運算，如果對n列運算，一般都用case 語句來判斷，如果列少還比較容易寫，列多了就麻煩了。這裡介紹一個通過xml合并列并轉為行集後直接用聚合函數求值的方法，測試用例和代碼如下

/*

測試名稱：利用

XML

求任意列之間的聚合

測試功能：對一張表的列資料做

min

、

max

sum

和

avg

運算

運作原理：字段合并為

xml

後做

xquery

查詢轉為行集後聚合

作者：

jinjazz

（近身剪）

*/

--

建立測試環境

declare

@t table

(

id smallint

,

a smallint

b smallint

c smallint

d smallint

e smallint

f smallint

)

insert

into

@t

select

1,

1,

2,

3,

4,

6,

7 union

all

2,

34,

45,

56,

54,

9,

6

測試語句

a.*,

c.*

from

@t a outer

apply(

doc=(

*

from

@t as

doc where

id=

a.

id 

for

xml

path

''

),

type

b

outer

r)

as

minValue,

maxValue,

sumValue,

avgValue

cast

cast

d.

n.

query(

'text()'

varchar

))

int

r

doc.

nodes(

'/a,b,c,d,e,f'

D(

n))

tt

c

測試結果

id    

a     

b     

c     

d     

e     

f     

minValue   

maxValue   

sumValue   

------ ------

------ ------ ------ ------ ------ ----------- ----------- -----------

-----------

1     

1     

2     

3     

4     

6     

7     

1          

7          

23         

3