选出N个列值中最大的三个列值

原帖：http://blog.youkuaiyun.com/htl258/archive/2009/09/21/4574309.aspx

 

现在有表A，有d1-dx N个int的字段，现在需要求id=1的 dx 中，最大的三个数值。sql语句怎样写呢？？

A

id    d1    d2    d3    d4    d5  ....  dx
1    4    6    9    5      2    ....

--SQL2005 UNPIVOT函数解法：

/* ---------------------------------
--  Author : htl258(Tony)
--  Date   : 2009-09-20 20:19:09
--  Version: Microsoft SQL Server 2008 (SP1) - 10.0.2531.0 (Intel X86)
    Mar 29 2009 10:27:29
    Copyright (c) 1988-2008 Microsoft Corporation
    Enterprise Evaluation Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 2)

--------------------------------- */
-- > 生成测试数据表:tb

IF NOT OBJECT_ID ( ' [tb] ' ) IS NULL
    DROP TABLE [ tb ]
GO
CREATE TABLE [ tb ] ( [ id ] INT , [ d1 ] INT , [ d2 ] INT , [ d3 ] INT , [ d4 ] INT , [ d5 ] INT , [ d6 ] INT , [ d7 ] INT , [ d8 ] INT , [ d9 ] INT , [ d10 ] INT )
INSERT [ tb ]
SELECT 1 , 4 , 6 , 9 , 5 , 2 , 3 , 7 , 9 , 22 , 15 UNION ALL
SELECT 2 , 1 , 2 , 4 , 6 , 7 , 8 , 9 , 10 , 3 , 5
GO
-- SELECT * FROM [tb]

-- >SQL查询如下:
DECLARE @s VARCHAR ( 2000 )
SELECT @s = ISNULL ( @s + ' , ' , '' ) + QUOTENAME (NAME)
FROM syscolumns
WHERE id = OBJECT_ID ( ' tb ' ) AND name NOT IN ( ' id ' )
EXEC ( '
;WITH t AS
(
    SELECT rn=ROW_NUMBER()OVER(PARTITION BY id ORDER BY val DESC),*
    FROM tb
        UNPIVOT(val FOR col IN( ' + @s + ' ))b
)
SELECT id,col,val
FROM t
WHERE rn<4
' )
/*
id    col    val
1    d9    22
1    d10    15
1    d3    9
2    d8    10
2    d7    9
2    d6    8
*/

--SQL2005XML解法:

-- Test Data: ta
IF OBJECT_ID ( ' [ta] ' ) IS NOT NULL
    DROP TABLE [ ta ]
Go
CREATE TABLE ta( [ id ] INT , [ d1 ] INT , [ d2 ] INT , [ d3 ] INT , [ d4 ] INT , [ d5 ] INT )
Go
INSERT INTO ta
   SELECT 1 , 4 , 6 , 9 , 5 , 2
INSERT INTO ta
   SELECT 2 , 4 , 1 , 9 , 5 , 2
GO

select
c.id,
a.x.query( ' /r/d[1] ' ).value( ' . ' , ' int ' ) as max1, a.x.query( ' /r/d[2] ' ).value( ' . ' , ' int ' ) as max2,
a.x.query( ' /r/d[3] ' ).value( ' . ' , ' int ' ) as max3
from ta as c
cross apply
    ( select x = cast (( select * from ta  where c.id = id for xml path( ' r ' )) as xml).query( '
        for $i in /r
         return
            <r>{$i/id}
             {
                for $j in $i/*[local-name()!="id"]
                  order by number($j) descending
                    return <d>{number($j)}</d>
             }
            </r>
          
    ' )
) a


/*
id          max1        max2        max3
----------- ----------- ----------- -----------
1           9           6           5
2           9           5           4

(2 行受影响)

*/