SQL从行数据中选择MIN值,而不是列数据 [英] SQL Selecting MIN value from row data, not column data
本文介绍了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 34
Then, 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 M
Or 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值,而不是列数据的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文