Using SQL 2005, is there a way to select the minimum value between 5 columns within one single row of data?
使用SQL 2005,是否有一种方法可以在一行数据中选择5列之间的最小值?
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?
那么,如何使用SQL获得最小值呢?
6 个解决方案
#1
1
You can create a UDF.
您可以创建一个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.
或者您可以直接跳过UDF并直接使用查询。
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
#2
2
probably something like
可能类似
select id
, least (num1, num2, num3, num4, num5)
from your_table
/
Most flavours of RDBMS offer LEAST().
大多数RDBMS提供的最少()。
#3
2
Fix your data structure to be normalized so that you don't have to do this complex, performance killing stuff to get the information you need.
修改数据结构,使其规范化,这样您就不需要做这种复杂的、破坏性能的工作来获得所需的信息。
#4
0
You can calculate min using the formula : MIN(a,b) = (a+b)/2 - abs(a-b)/2
你可以用公式来计算最小值:min (a,b) = (a+b)/2 - abs(a-b)/2
#5
0
TSQL can do it, but it takes a little prep...
TSQL可以做到这一点,但它需要一点准备……
First, you need a function:
(it takes a comma-delimeted string of integers, and returns the greatest integer)
首先,您需要一个函数:(它接受一个逗号分隔的整数字符串,并返回最大的整数)
CREATE Function [dbo].[GreatestInt]
( @Array varchar(max) )
Returns int As
BEGIN
DECLARE @end Int
DECLARE @start Int
DECLARE @tbl_int Table (myInt Int)
DECLARE @return Int
SET @Array = @Array + ','
SET @start=1
SET @end=1
WHILE @end<Len(@Array)
BEGIN
SET @end = CharIndex(',', @Array, @end)
INSERT INTO @tbl_int
SELECT
Cast(Substring(@Array, @start, @end-@start) As Int)
SET @start=@end+1
SET @end = @end+1
END
SET @return = (SELECT MAX(myInt) FROM @tbl_int)
RETURN @return
END
And then to create your string of integers (this is the part TSQL isn't very good at):
(in the SELECT)
然后创建整数字符串(这是TSQL不太擅长的部分):(在SELECT中)
stuff(
stuff([num5], 1, 0,',')
,1,0,
stuff(
stuff([num4], 1, 0,',')
,1,0,
stuff(
stuff([num3], 1, 0,',')
,1,0,
stuff(
stuff([num2], 1, 0,',')
,1,0,
[num1]
)
)
)
)
So to use the function:
使用这个函数
SELECT
id,
dbo.GreatestInt( stuff(
stuff([num5], 1, 0,',')
,1,0,
stuff(
stuff([num4], 1, 0,',')
,1,0,
stuff(
stuff([num3], 1, 0,',')
,1,0,
stuff(
stuff([num2], 1, 0,',')
,1,0,
[num1]
)
)
)
)
)
FROM
myTable
The reason I did it like this, instead of the way @mikael did in his answer (wich I +1'd, because it does answer your question), is that this approach will work on any number of fields, not just 5. But honestly, TSQL does have some room to improve here -- they really need a tsql version of plsql's greatest/least functions. Oh well...
我这样做的原因,不是@mikael在他的回答中所做的(因为它确实回答了你的问题),而是这个方法适用于任何数量的字段,而不仅仅是5个。但老实说,TSQL确实有一些改进的空间——它们确实需要一个TSQL版本的plsql的最大/最小功能。哦……
#6
0
SELECT id
, CASE WHEN num1 < num2 AND num1 < num3 AND num1 < num4 AND num1 < num5
THEN num1
WHEN num2 < num3 AND num2 < num4 AND num2 < num5
THEN num2
WHEN num3 < num4 AND num3 < num5
THEN num3
WHEN num4 < num5
THEN num4
ELSE num5
END AS LeastNum
FROM MyTable
#1
1
You can create a UDF.
您可以创建一个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.
或者您可以直接跳过UDF并直接使用查询。
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
#2
2
probably something like
可能类似
select id
, least (num1, num2, num3, num4, num5)
from your_table
/
Most flavours of RDBMS offer LEAST().
大多数RDBMS提供的最少()。
#3
2
Fix your data structure to be normalized so that you don't have to do this complex, performance killing stuff to get the information you need.
修改数据结构,使其规范化,这样您就不需要做这种复杂的、破坏性能的工作来获得所需的信息。
#4
0
You can calculate min using the formula : MIN(a,b) = (a+b)/2 - abs(a-b)/2
你可以用公式来计算最小值:min (a,b) = (a+b)/2 - abs(a-b)/2
#5
0
TSQL can do it, but it takes a little prep...
TSQL可以做到这一点,但它需要一点准备……
First, you need a function:
(it takes a comma-delimeted string of integers, and returns the greatest integer)
首先,您需要一个函数:(它接受一个逗号分隔的整数字符串,并返回最大的整数)
CREATE Function [dbo].[GreatestInt]
( @Array varchar(max) )
Returns int As
BEGIN
DECLARE @end Int
DECLARE @start Int
DECLARE @tbl_int Table (myInt Int)
DECLARE @return Int
SET @Array = @Array + ','
SET @start=1
SET @end=1
WHILE @end<Len(@Array)
BEGIN
SET @end = CharIndex(',', @Array, @end)
INSERT INTO @tbl_int
SELECT
Cast(Substring(@Array, @start, @end-@start) As Int)
SET @start=@end+1
SET @end = @end+1
END
SET @return = (SELECT MAX(myInt) FROM @tbl_int)
RETURN @return
END
And then to create your string of integers (this is the part TSQL isn't very good at):
(in the SELECT)
然后创建整数字符串(这是TSQL不太擅长的部分):(在SELECT中)
stuff(
stuff([num5], 1, 0,',')
,1,0,
stuff(
stuff([num4], 1, 0,',')
,1,0,
stuff(
stuff([num3], 1, 0,',')
,1,0,
stuff(
stuff([num2], 1, 0,',')
,1,0,
[num1]
)
)
)
)
So to use the function:
使用这个函数
SELECT
id,
dbo.GreatestInt( stuff(
stuff([num5], 1, 0,',')
,1,0,
stuff(
stuff([num4], 1, 0,',')
,1,0,
stuff(
stuff([num3], 1, 0,',')
,1,0,
stuff(
stuff([num2], 1, 0,',')
,1,0,
[num1]
)
)
)
)
)
FROM
myTable
The reason I did it like this, instead of the way @mikael did in his answer (wich I +1'd, because it does answer your question), is that this approach will work on any number of fields, not just 5. But honestly, TSQL does have some room to improve here -- they really need a tsql version of plsql's greatest/least functions. Oh well...
我这样做的原因,不是@mikael在他的回答中所做的(因为它确实回答了你的问题),而是这个方法适用于任何数量的字段,而不仅仅是5个。但老实说,TSQL确实有一些改进的空间——它们确实需要一个TSQL版本的plsql的最大/最小功能。哦……
#6
0
SELECT id
, CASE WHEN num1 < num2 AND num1 < num3 AND num1 < num4 AND num1 < num5
THEN num1
WHEN num2 < num3 AND num2 < num4 AND num2 < num5
THEN num2
WHEN num3 < num4 AND num3 < num5
THEN num3
WHEN num4 < num5
THEN num4
ELSE num5
END AS LeastNum
FROM MyTable