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