从几个列中选择最小值的最佳方法是什么?

Given the following table in SQL Server 2005:

给出SQL Server 2005中的下表:

ID   Col1   Col2   Col3
--   ----   ----   ----
1       3     34     76  
2      32    976     24
3       7    235      3
4     245      1    792

What is the best way to write the query that yields the following result (i.e. one that yields the final column - a column containing the minium values out of Col1, Col2, and Col 3 for each row)?

编写产生以下结果的查询的最佳方式是什么(即生成最后一列的查询—每一行包含Col1、Col2和Col 3中的minium值)?

ID   Col1   Col2   Col3  TheMin
--   ----   ----   ----  ------
1       3     34     76       3
2      32    976     24      24
3       7    235      3       3
4     245      1    792       1

UPDATE:

更新:

For clarification (as I have said in the coments) in the real scenario the database is properly normalized. These "array" columns are not in an actual table but are in a result set that is required in a report. And the new requirement is that the report also needs this MinValue column. I can't change the underlying result set and therefore I was looking to T-SQL for a handy "get out of jail card".

为了澄清(正如我在coments中说过的)，在真实场景中，数据库是正常的。这些“数组”列不在实际的表中，而在报表中需要的结果集中。新的要求是报告也需要这个MinValue列。我无法更改底层结果集，因此我希望T-SQL能够提供一个方便的“出狱卡”。

I tried the CASE approach mentioned below and it works, although it is a bit cumbersome. It is also more complicated than stated in the answers because you need to cater for the fact that there are two min values in the same row.

我尝试了下面提到的CASE方法，它很有效，尽管有点麻烦。它也比在答案中陈述的要复杂，因为你需要满足同一行里有两个最小值的事实。

Anyway, I thought I'd post my current solution which, given my constraints, works pretty well. It uses the UNPIVOT operator:

不管怎样，我想我应该把我现在的解决方案，考虑到我的约束条件，工作得很好。它使用UNPIVOT算子:

with cte (ID, Col1, Col2, Col3)
as
(
    select ID, Col1, Col2, Col3
    from TestTable
)
select cte.ID, Col1, Col2, Col3, TheMin from cte
join
(
    select
        ID, min(Amount) as TheMin
    from 
        cte 
        UNPIVOT (Amount for AmountCol in (Col1, Col2, Col3)) as unpvt
    group by ID
) as minValues
on cte.ID = minValues.ID

I'll say upfront that I don't expect this to offer the best performance, but given the circumstances (I can't redesign all the queries just for the new MinValue column requirement), it is a pretty elegant "get out of jail card".

我将坦率地说，我并不期望这能提供最好的性能，但是考虑到环境(我不能重新设计所有的查询，只是为了新的MinValue列要求)，它是一个相当优雅的“从*中获得”。

18 个解决方案

#1

There are likely to be many ways to accomplish this. My suggestion is to use Case/When to do it. With 3 columns, it's not too bad.

实现这一目标的方法可能有很多。我的建议是用Case/什么时候做。有3列，不算太糟。

Select Id,
       Case When Col1 < Col2 And Col1 < Col3 Then Col1
            When Col2 < Col1 And Col2 < Col3 Then Col2 
            Else Col3
            End As TheMin
From   YourTableNameHere

#2

Using CROSS APPLY:

使用交叉应用:

SELECT ID, Col1, Col2, Col3, MinValue
FROM YourTable
CROSS APPLY (SELECT MIN(d) MinValue FROM (VALUES (Col1), (Col2), (Col3)) AS a(d)) A

SQL Fiddle

SQL小提琴

#3

The best way is actually not to do it - it's strange that people insist on storing their data in a way that requires SQL "gymnastics" to extract meaningful information when there are far easier ways to achieve the desired result if you just normalise your schema correctly.

最好的方法实际上是不这样做——奇怪的是，人们坚持以需要SQL“体操”的方式存储数据，以提取有意义的信息。

The right way to do this, in my opinion, is to have the following table:

我认为，正确的做法是有以下表格:

ID    Col    Val
--    ---    ---
 1      1      3
 1      2     34
 1      3     76

 2      1     32
 2      2    976
 2      3     24

 3      1      7
 3      2    235
 3      3      3

 4      1    245
 4      2      1
 4      3    792

with ID/Col as the primary key and possibly Col as an extra key, depending on your needs. Then your query becomes a simple

根据您的需要，使用ID/Col作为主键，也可能使用Col作为额外的键。然后您的查询变得很简单

select min(val) from tbl

and you can still treat the individual 'old columns' separately by using

您仍然可以通过使用来分别处理单个的“旧列”

where col = 2

in your other queries. This also allows for easy expansion should the number of 'old columns' grow.

在你的其他查询。这也使得“旧栏”的数量增加。

This makes your queries so much easier. The general guideline I tend to use is, if you ever have something that looks like an array in a database row, you're probably doing something wrong and should think about restructuring the data.

这使得查询变得非常简单。我倾向于使用的一般指导原则是，如果您有类似于数据库行中的数组的东西，您可能做错了什么，应该考虑重构数据。

---

However, if for some reason you can't change those columns, I'd suggest using insert and update triggers and add another column which these triggers set to the minimum on Col1/2/3. This will move the 'cost' of the operation away from the select to the update/insert where it belongs - most database tables in my experience are read far more often than written so incurring the cost on write tends to be more efficient over time.

但是，如果由于某些原因不能更改这些列，我建议使用insert和update触发器，并添加另一个列，这些触发器将这些触发器设置为Col1/2/3的最小值。这将使操作的“成本”从select /insert到它所属的位置——根据我的经验，大多数数据库表的读取频率要比写入的频率高得多，因此写入时的成本随着时间的推移往往会更有效。

In other words, the minimum for a row only changes when one of the other columns change, so that's when you should be calculating it, not every time you select (which is wasted if the data isn't changing). You would then end up with a table like:

换句话说，一行的最小值只在其他列之一发生变化时发生变化，因此应该计算它，而不是每次都进行选择(如果数据没有变化，这就浪费了时间)。然后你会得到这样一张桌子:

ID   Col1   Col2   Col3   MinVal
--   ----   ----   ----   ------
 1      3     34     76        3
 2     32    976     24       24
 3      7    235      3        3
 4    245      1    792        1

Any other option that has to make decisions at select time is usually a bad idea performance-wise, since the data only changes on insert/update - the addition of another column takes up more space in the DB and will be slightly slower for the inserts and updates but can be much faster for selects - the preferred approach should depend on your priorities there but, as stated, most tables are read far more often than they're written.

任何其他选项,必须做出的决定通常是一个坏主意进行属性选择时候,由于数据只在插入/更新变化,添加另一个列在DB占用更多的空间,会稍慢的插入和更新,但可以选择更快——首选方法应该取决于你的优先级,但如上所述,大多数表读取已写入往往远远超过他们。

#4

You can use the "brute force" approach with a twist:

你可以使用“蛮力”方法来扭转:

SELECT CASE
    WHEN Col1 <= Col2 AND Col1 <= Col3 THEN Col1
    WHEN                  Col2 <= Col3 THEN Col2
    ELSE                                    Col3
END AS [Min Value] FROM [Your Table]

When the first when condition fails it guarantees that Col1 is not the smallest value therefore you can eliminate it from rest of the conditions. Likewise for subsequent conditions. For five columns your query becomes:

当第一个条件失败时，它保证Col1不是最小值，因此您可以从其他条件中删除它。同样,随后的条件。对于5列，查询变为:

SELECT CASE
    WHEN Col1 <= Col2 AND Col1 <= Col3 AND Col1 <= Col4 AND Col1 <= Col5 THEN Col1
    WHEN                  Col2 <= Col3 AND Col2 <= Col4 AND Col2 <= Col5 THEN Col2
    WHEN                                   Col3 <= Col4 AND Col3 <= Col5 THEN Col3
    WHEN                                                    Col4 <= Col5 THEN Col4
    ELSE                                                                      Col5
END AS [Min Value] FROM [Your Table]

Note that if there is a tie between two or more columns then <= ensures that we exit the CASE statement as early as possible.

注意，如果两个或多个列之间有一个连接，那么<=将确保尽早退出CASE语句。

#5

If the columns were integers as in your example I would create a function:

如果列是整数，如你的例子，我会创建一个函数:

create function f_min_int(@a as int, @b as int) 
returns int
as
begin
    return case when @a < @b then @a else coalesce(@b,@a) end
end

then when I need to use it I would do :

当我需要用它时，我会做:

select col1, col2, col3, dbo.f_min_int(dbo.f_min_int(col1,col2),col3)

if you have 5 colums then the above becomes

如果你有5列，那么上面的就变成了

select col1, col2, col3, col4, col5,
dbo.f_min_int(dbo.f_min_int(dbo.f_min_int(dbo.f_min_int(col1,col2),col3),col4),col5)

#6

You could also do this with a union query. As the number of columns increase, you would need to modify the query, but at least it would be a straight forward modification.

您还可以使用union查询来执行此操作。随着列数的增加，您将需要修改查询，但至少这是一个直接向前的修改。

Select T.Id, T.Col1, T.Col2, T.Col3, A.TheMin
From   YourTable T
       Inner Join (
         Select A.Id, Min(A.Col1) As TheMin
         From   (
                Select Id, Col1
                From   YourTable

                Union All

                Select Id, Col2
                From   YourTable

                Union All

                Select Id, Col3
                From   YourTable
                ) As A
         Group By A.Id
       ) As A
       On T.Id = A.Id

#7

SELECT ID, Col1, Col2, Col3, 
    (SELECT MIN(Col) FROM (VALUES (Col1), (Col2), (Col3)) AS X(Col)) AS TheMin
FROM Table

#8

This is brute force but works

这是蛮力，但有效

 select case when col1 <= col2 and col1 <= col3 then col1
           case when col2 <= col1 and col2 <= col3 then col2
           case when col3 <= col1 and col3 <= col2 then col3
    as 'TheMin'
           end

from Table T

... because min() works only on one column and not across columns.

…因为min()只作用于一列，而不是跨列。

#9

Both this question And this question try to answer this.

这个问题和这个问题都试图回答这个问题。

The recap is that Oracle has a built in function for this, with Sql Server you are stuck either defining a user-defined-function or using case statements.

recap是Oracle的一个内置函数，Sql Server可以定义一个用户定义函数或用例语句。

#10

If you're able to make a stored procedure, it could take an array of values, and you could just call that.

如果你能做一个存储过程，它可以取一个值数组，你可以直接调用它。

#11

select *,
case when column1 < columnl2 And column1 < column3 then column1
when columnl2 < column1 And columnl2 < column3 then columnl2
else column3
end As minValue
from   tbl_example

#12

A little twist on the union query:

联合查询的一个小转折:

DECLARE @Foo TABLE (ID INT, Col1 INT, Col2 INT, Col3 INT)

INSERT @Foo (ID, Col1, Col2, Col3)
VALUES
(1, 3, 34, 76),
(2, 32, 976, 24),
(3, 7, 235, 3),
(4, 245, 1, 792)

SELECT
    ID,
    Col1,
    Col2,
    Col3,
    (
        SELECT MIN(T.Col)
        FROM
        (
            SELECT Foo.Col1 AS Col UNION ALL
            SELECT Foo.Col2 AS Col UNION ALL
            SELECT Foo.Col3 AS Col 
        ) AS T
    ) AS TheMin
FROM
    @Foo AS Foo

#13

If you use SQL 2005 you can do something neat like this:

如果您使用SQL 2005，您可以做如下事情:

;WITH    res
          AS ( SELECT   t.YourID ,
                        CAST(( SELECT   Col1 AS c01 ,
                                        Col2 AS c02 ,
                                        Col3 AS c03 ,
                                        Col4 AS c04 ,
                                        Col5 AS c05
                               FROM     YourTable AS cols
                               WHERE    YourID = t.YourID
                             FOR
                               XML AUTO ,
                                   ELEMENTS
                             ) AS XML) AS colslist
               FROM     YourTable AS t
             )
    SELECT  YourID ,
            colslist.query('for $c in //cols return min(data($c/*))').value('.',
                                            'real') AS YourMin ,
            colslist.query('for $c in //cols return avg(data($c/*))').value('.',
                                            'real') AS YourAvg ,
            colslist.query('for $c in //cols return max(data($c/*))').value('.',
                                            'real') AS YourMax
    FROM    res

This way you don't get lost in so many operators :)

这样你就不会迷失在这么多的运营者中了。

However, this could be slower than the other choice.

然而，这可能比其他选择要慢。

It's your choice...

这是你自己的选择……

#14

Below I use a temp table to get the minimum of several dates. The first temp table queries several joined tables to get various dates (as well as other values for the query), the second temp table then gets the various columns and the minimum date using as many passes as there are date columns.

下面我使用临时表来获取最少的几个日期。第一个临时表查询几个已连接的表，以获得不同的日期(以及查询的其他值)，第二个临时表然后获取不同的列和最少的日期，使用的次数和日期列一样多。

This is essentially like the union query, the same number of passes are required, but may be more efficient (based on experience, but would need testing). Efficiency wasn't an issue in this case (8,000 records). One could index etc.

这基本上与union查询类似，需要相同数量的传递，但可能更有效(基于经验，但需要测试)。在这种情况下，效率不是问题(8000条记录)。人能指数等。

--==================== this gets minimums and global min
if object_id('tempdb..#temp1') is not null
    drop table #temp1
if object_id('tempdb..#temp2') is not null
    drop table #temp2

select r.recordid ,  r.ReferenceNumber, i.InventionTitle, RecordDate, i.ReceivedDate
, min(fi.uploaddate) [Min File Upload], min(fi.CorrespondenceDate) [Min File Correspondence]
into #temp1
from record r 
join Invention i on i.inventionid = r.recordid
left join LnkRecordFile lrf on lrf.recordid = r.recordid
left join fileinformation fi on fi.fileid = lrf.fileid
where r.recorddate > '2015-05-26'
 group by  r.recordid, recorddate, i.ReceivedDate,
 r.ReferenceNumber, i.InventionTitle



select recordid, recorddate [min date]
into #temp2
from #temp1

update #temp2
set [min date] = ReceivedDate 
from #temp1 t1 join #temp2 t2 on t1.recordid = t2.recordid
where t1.ReceivedDate < [min date] and  t1.ReceivedDate > '2001-01-01'

update #temp2 
set [min date] = t1.[Min File Upload]
from #temp1 t1 join #temp2 t2 on t1.recordid = t2.recordid
where t1.[Min File Upload] < [min date] and  t1.[Min File Upload] > '2001-01-01'

update #temp2
set [min date] = t1.[Min File Correspondence]
from #temp1 t1 join #temp2 t2 on t1.recordid = t2.recordid
where t1.[Min File Correspondence] < [min date] and t1.[Min File Correspondence] > '2001-01-01'


select t1.*, t2.[min date] [LOWEST DATE]
from #temp1 t1 join #temp2 t2 on t1.recordid = t2.recordid
order by t1.recordid

#15

SELECT [ID],
            (
                SELECT MIN([value].[MinValue])
                FROM
                (
                    VALUES
                        ([Col1]),
                        ([Col1]),
                        ([Col2]),
                        ([Col3])
                ) AS [value] ([MinValue])
           ) AS [MinValue]
FROM Table;

#16

If you know what values you are looking for, usually a status code, the following can be helpful:

如果你知道你在寻找什么值，通常是一个状态码，下面的内容可能会有帮助:

select case when 0 in (PAGE1STATUS ,PAGE2STATUS ,PAGE3STATUS,
PAGE4STATUS,PAGE5STATUS ,PAGE6STATUS) then 0 else 1 end
FROM CUSTOMERS_FORMS

#17

Use this:

用这个:

select least(col1, col2, col3) FROM yourtable

#18

For multiple columns its best to use a CASE statement, however for two numeric columns i and j you can use simple math:

对于多个列，最好使用CASE语句，但是对于两个数字列i和j，可以使用简单的数学:

min(i,j) = (i+j)/2 - abs(i-j)/2

min(i,j) = (i+j)/2 - abs(i-j)/2

This formula can be used to get the minimum value of multiple columns but its really messy past 2, min(i,j,k) would be min(i,min(j,k))

这个公式可以用来求多列的最小值但是它在2点之后非常混乱，min(i,j,k)应该是min(i,min(j,k))