select IIF(L1>L2,L1,L2) AS M1,IIF(M1>L3,M1,L3) AS M2 FROM tbl1这是Access的写法,测试通过,Sql server 2000 中好像没有IIF,有 Case 你可以参照来写。
SELECT [ID] , MAX(col1) AS MaxCol FROM ( SELECT [ID] , col1 FROM Table1 UNION ALL SELECT [ID] , col2 FROM Table1 UNION ALL SELECT [ID] , col3 FROM Table1 ) A GROUP BY [ID]
SELECT [ID] , MAX(col1) AS MaxCol FROM ( SELECT [ID] , col1 FROM Table1 UNION ALL SELECT [ID] , col2 FROM Table1 UNION ALL SELECT [ID] , col3 FROM Table1 ) A GROUP BY [ID] 内联!
另外提供一条SQL语句SELECT [id], CASE WHEN COL1 >COL2 and col1 > col3 THEN COL1 when col2>col1 and col2 > col3 then col2 else col3 end as MAXCOL from table1
SELECT [ID] , col1 FROM Table1
UNION ALL
SELECT [ID] , col2 FROM Table1
UNION ALL
SELECT [ID] , col3 FROM Table1
) A GROUP BY [ID]
SELECT [ID] , col1 FROM Table1
UNION ALL
SELECT [ID] , col2 FROM Table1
UNION ALL
SELECT [ID] , col3 FROM Table1
) A GROUP BY [ID]
内联!
when col2>col1 and col2 > col3 then col2
else
col3
end as MAXCOL
from table1