SQL从行数据中选择最小值，而不是列数据

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

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

probably something like

可能类似

select id
       , least (num1, num2, num3, num4, num5)
from your_table
/

Most flavours of RDBMS offer LEAST().

大多数RDBMS提供的最少()。

#3

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

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

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

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