本文介绍了SQL从行数据中选择MIN值,而不是列数据的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
使用SQL 2005,是否有一种方法可以选择单个数据行中5列之间的最小值?
因此,如果我有一行数据像这样:
id num1 num2 num3 num4 num5 1 22 51 4 99 34那么,如何使用SQL获得最低值?
解决方案您可以创建一个UDF。
create function GetMin(@ N1 int,@ N2 int ,@ N3 int,@ N4 int,@ N5 int)返回表为 return(select min(N)as Value from(select @ N1 union all select @ N2 union all select @ N3 union all select @ N4 union all select @ N5)as T(N) )并像这样使用。
declare @T table ( id int, num1 int, num2 int, num3 int, num4 int, num5 int ) 插入到@T值(1,22,51,4,99,34), (2,222,251,24,299,234) 选择id, M.Value 从@T cross apply dbo.GetMin(num1, num2,num3,num4,num5)as M也可以跳过UDF并直接使用查询。
选择id, M.Value 从@T 交叉应用min(N)as Value from(select num1 联合全部选择num2 联合全部选择num3 联合全部选择num4 union all select num5)as T(N))as M
Using SQL 2005, is there a way to select the minimum value between 5 columns within one single row of data?
So, if I have a row of data like this:
id num1 num2 num3 num4 num5 1 22 51 4 99 34Then, how can I get the lowest value using SQL?
解决方案You can create a UDF.
create function GetMin(@N1 int, @N2 int, @N3 int, @N4 int, @N5 int) returns table as return (select min(N) as Value from (select @N1 union all select @N2 union all select @N3 union all select @N4 union all select @N5) as T(N))And use it like this.
declare @T table ( id int, num1 int, num2 int, num3 int, num4 int, num5 int ) insert into @T values (1, 22, 51, 4, 99, 34), (2, 222, 251, 24, 299, 234) select id, M.Value from @T cross apply dbo.GetMin(num1, num2, num3, num4, num5) as MOr you can skip the UDF and use the query directly.
select id, M.Value from @T cross apply (select min(N) as Value from (select num1 union all select num2 union all select num3 union all select num4 union all select num5) as T(N)) as M
更多推荐
SQL从行数据中选择MIN值,而不是列数据
发布评论