SQL Server 多字段根据范围求最大值
这篇文章主要为大家详细介绍了SQL Server 多字段根据范围求最大值,具有一定的参考价值,可以用来参考一下。
对此感兴趣的朋友,看看idc笔记做的技术笔记!
-->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*/
注:关于SQL Server 多字段根据范围求最大值的内容就先介绍到这里,更多相关文章的可以留意