SQL查询每行中最大值的技巧

--------------------------------------------------------------------------

--  Author : htl258(Tony)

--  Date   : 2010-04-23 08:08:36

--  Version:Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86)

--          Jul  9 2008 14:43:34

--          Copyright (c) 1988-2008 Microsoft Corporation

--          Developer Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 3)

--  Blog   : http://blog.csdn.net/htl258

--  Subject: SQL查询每行中最大值的技巧

--------------------------------------------------------------------------

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

 

IF NOT OBJECT_ID('[tb]') IS NULL

    DROP TABLE [tb]

GO

CREATE TABLE [tb](

a SMALLINT,

b SMALLINT,

c SMALLINT,

d SMALLINT,

e SMALLINT,

f SMALLINT,

g SMALLINT

)

INSERT [tb]

SELECT 1,2,3,4,5,2,3 UNION ALL

SELECT 4,5,6,7,7,2,0 UNION ALL

SELECT 4,9,6,7,7,9,6

GO

--SELECT * FROM [tb]

 

-->SQL查询如下:

 

SELECT *,

    (SELECT MAX(a)

     FROM(

       SELECT a UNION ALL

       SELECT b UNION ALL

       SELECT c UNION ALL

       SELECT d UNION ALL

       SELECT e UNION ALL

       SELECT f UNION ALL

       SELECT g

       ) AS t  

    ) AS maxvalue

FROM tb

 

 

/*

a      b      c      d      e      f      g      maxvalue

------ ------ ------ ------ ------ ------ ------ --------

1      2      3      4      5      2      3      5

4      5      6      7      7      2      0      7

4      9      6      7      7      9      6      9

 

(3 行受影响)

*/