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 (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 行受影响) */