你的位置:首页 > 数据库

[数据库]求多个列的最大值/最小值


数据库环境:SQL SERVER 2008R2 

  如题,现有数据如图1,要求求出每行相同数据类型的最大值/最小值。即图2的效果。

图1      图2

  Oracle里有专门的greatest()、least()函数求多个列的最大、最小值,但是,在Sql Server里,

还没有对应实现的函数。我想到的方法是通过想列转行、行转列实现。

1.数据准备

WITH  x0     AS ( SELECT  1 AS id ,            3 AS c1 ,            4 AS c2 ,            0 AS c3 ,            5 AS c4 ,            2 AS c5        UNION ALL        SELECT  2 AS id ,            2 AS c1 ,            3 AS c2 ,            1 AS c3 ,            6 AS c4 ,            4 AS c5        UNION ALL        SELECT  3 AS id ,            6 AS c1 ,            4 AS c2 ,            11 AS c3 ,            2 AS c4 ,            9 AS c5       )

View Code

2.列转行

, x1     AS ( SELECT  *        FROM   x0 UNPIVOT( c FOR attr IN ( c1, c2, c3, c4, c5 ) ) t       )

View Code

3.union all合并每行的最大、最小值

,x2     AS ( SELECT  id ,            attr ,            c        FROM   x1        UNION ALL        SELECT  id ,            'c6' AS attr ,            MAX(c)        FROM   x1        GROUP BY id        UNION ALL        SELECT  id ,            'c7' AS attr ,            MIN(c)        FROM   x1        GROUP BY id       )

View Code

4.行转列实现最终结果

 SELECT id ,      c1 ,      c2 ,      c3 ,      c4 ,      c5 ,      c6 AS c_max ,      c7 AS c_min  FROM  ( SELECT  *       FROM   x2      ) AS t1 PIVOT( MAX(c) FOR attr IN ( c1, c2, c3, c4, c5, c6, c7 ) ) t2

View Code

  SQL脚本是合在一起执行的,这里为了说明思路,把SQL拆开讲了。

当然,实现该功能的方法不止这一种,具体可以参看这篇文章 http://blog.csdn.net/wufeng4552/article/details/4681510/。