MSSQL 多字段依照范围求最大值实现方法
发布时间：2023-08-17 14:35:52 所属栏目：MsSql教程 来源：
导读：-->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 @T
select 1
-->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 @T
select 1
|
-->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 @T select 1,10,20,30,40,50,60 union all select 2,60,45,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 */ （编辑：聊城站长网） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |
推荐文章
站长推荐
