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

-->Title:生成?y????BR>-->Author:wufeng4552
-->Date:2009-09-2115:08:41

declare@Ttable([Col1]int,[Col2]int,[Col3]int,[Col4]int,[Col5]int,[Col6]int,[Col7]int)
Insert@T
select1,10,20,30,40,50,60unionall
select2,60,30,45,20,52,85unionall
select3,87,56,65,41,14,21
--方法1
select[col1],
      max([col2])maxcol
from
 (select[col1],[col2]from@t
 unionall
 select[col1],[col3]from@t
 unionall
 select[col1],[col4]from@t
 unionall
 select[col1],[col5]from@t
 unionall
 select[col1],[col6]from@t
 unionall
 select[col1],[col7]from@t
 )T
where[col2]between20and60 --?l件限制
groupby[col1]
/*
col1       maxcol
----------------------
1          60
2          60
3          56

(3???料列受到影?)

*/
--方法2
select[col1],
      (selectmax([col2])from
      (
       select[col2]
       unionallselect[col3]
       unionallselect[col4]
       unionallselect[col5]
       unionallselect[col6]
       unionallselect[col7]
      )T
      where[col2]between20and60)asmaxcol--指定查????BR>from@t
/*
(3???料列受到影?)
col1       maxcol
----------------------
1          60
2          60
3          56
*/