sql的max之类的聚合函数只能针对同一列的n行运算如果对n列运算一般都用case 语句来判断如果列少还比较容易写列多了就麻烦了
/*
测试名称利用 XML 求任意列之间的聚合
测试功能对一张表的列数据做 min max sum 和 avg 运算
运行原理字段合并为 xml 后做 xquery 查询转为行集后聚合
*/
建立测试环境
declare @t table (
id smallint
a smallint b smallint
c smallint d smallint
e smallint f smallint )
insert into @t
select union all
select
测试语句
select a* c*
from @t a outer apply(
select doc=(
select * from @t as doc where id= a id for xml path ( ) type )
) b
outer apply(
select
min ( r) as minValue
max ( r) as maxValue
sum ( r) as sumValue
avg ( r) as avgValue
from (
select cast ( cast ( d n query( text() ) as varchar ( max )) as int ) as r
from doc nodes( /abcdef ) D( n)) tt
) c
/* 测试结果
id a b c d e f minValue maxValue sumValue avgValue
*/
