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

http://topic.csdn.net/u/20090920/20/b340e89b-51ef-4999-9238-8461a352adf7.html

现在有表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 行受影响)

*/