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,85 union all
select 3,87,56,65,41,14,21
--方法1
select [col1],
       max([col2])maxcol
from
 (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
 )t
where [col2] between 20 and 60  --條件限制
group by [col1]
/*
col1        maxcol
----------- -----------
1           60
2           60
3           56

(3 個資料列受到影響)

*/
--方法2
select [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           60
2           60
3           56
*/