MSSQL 多字段根据范围求最大值实现方法

--Title:生成測試數據 --Author:wufeng4552 --Date :2009-09-21 15:08:41 declare @T table([Col1] int,[Col2] int,[Col3] int,[Col4] int,[Col5] int,[Col6] int,[Col7] int) Insert @T select 1,10,20,30,40,50,60 union all select 2,60,30,45,20,52

-->Title:生成測試數據-->Author:wufeng4552-->Date :2009-09-21 15:08:41declare @T table([Col1] int,[Col2] int,[Col3] int,[Col4] int,[Col5] int,[Col6] int,[Col7] int)Insert @Tselect 1,10,20,30,40,50,60 union allselect 2,60,30,45,20,52,85 union allselect 3,87,56,65,41,14,21--方法1select [col1],       max([col2])maxcolfrom (select [col1],[col2] from @t   union all  select [col1],[col3] from @t   union all  select [col1],[col4] from @t  union all  select [col1],[col5] from @t  union all  select [col1],[col6] from @t  union all  select [col1],[col7] from @t )Twhere [col2] between 20 and 60  --條件限制group by [col1]/*col1        maxcol----------- -----------1           602           603           56

(3 個資料列受到影響)

*/--方法2select [col1],       (select max([col2])from        (        select [col2]         union all select [col3]        union all select [col4]        union all select [col5]        union all select [col6]        union all select [col7]        )T       where [col2] between 20 and 60) as maxcol --指定查詢範圍from @t/*(3 個資料列受到影響)col1        maxcol----------- -----------1           602           603           56*/