原帖:http://blog.csdn.net/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 行受影响)
*/