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

*/