选出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函数解法：

--> 生成测试数据表: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

')

--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