-->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 */