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