SQL 从几列中选择最小值的最佳方法是什么？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/368351/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
What's the best way to select the minimum value from several columns?
提问by stucampbell
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 中的最小值的列）？
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".
为了澄清（正如我在评论中所说），在真实场景中，数据库已正确规范化。这些“数组”列不在实际表中，而是在报告所需的结果集中。并且新的要求是报表也需要这个 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 列要求重新设计所有查询），这是一个非常优雅的“越狱”卡片”。
采纳答案by George Mastros
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/When 来做。有 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
回答by Nizam
Using CROSS APPLY:
使用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
回答by dsz
SELECT ID, Col1, Col2, Col3,
(SELECT MIN(Col) FROM (VALUES (Col1), (Col2), (Col3)) AS X(Col)) AS TheMin
FROM Table
回答by user3493139
On MySQL, use this:
在 MySQL 上，使用这个：
select least(col1, col2, col3) FROM yourtable
回答by Salman A
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:
当第一个 when 条件失败时，它保证 Col1 不是最小值，因此您可以从其余条件中消除它。后续条件也一样。对于五列，您的查询变为：
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 CASEstatement as early as possible.
请注意，如果两列或更多列之间存在联系，请<=确保我们CASE尽早退出该语句。
回答by paxdiablo
The best way to do that is probably notto 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 structure your schema a little better :-)
做到这一点的最好方法可能是不这样做 - 人们坚持以需要 SQL“体操”来提取有意义信息的方式存储他们的数据，这很奇怪，而有更简单的方法来实现所需的结果，如果你只是更好地构建您的架构:-)
The rightway 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/Colas the primary key (and possibly Colas an extra key, depending on your needs). Then your query becomes a simple select min(val) from tbland you can still treat the individual 'old columns' separately by using where col = 2in your other queries. This also allows for easy expansion should the number of 'old columns' grow.
withID/Col作为主键（也可能Col作为额外的键，取决于您的需要）。然后您的查询变得简单select min(val) from tbl，您仍然可以通过where col = 2在其他查询中使用来单独处理各个“旧列” 。如果“旧列”的数量增加，这也允许轻松扩展。
This makes your queries somuch easier. The general guideline I tend to use is, if you everhave 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'tchange those columns, I'd suggest using insert and update triggers and add anothercolumn 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.
但是，如果由于某种原因您无法更改这些列，我建议使用插入和更新触发器并添加另一列，这些触发器将Col1/2/3. 这会将操作的“成本”从选择转移到它所属的更新/插入 - 根据我的经验，大多数数据库表的读取次数远多于写入，因此随着时间的推移，写入成本往往会更有效率。
In other words, the minimum for a row only changes when one of the other columns change, so that'swhen 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 selecttime 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 muchfaster for selects - the preferred approach should depend on your priorities there but, as stated, most tables are read farmore often than they're written.
任何其他必须及时做出决定的选项在select性能方面通常是一个坏主意，因为数据仅在插入/更新时发生变化 - 另一列的添加在数据库中占用更多空间，并且插入和更新，但对于选择来说可能要快得多 - 首选方法应该取决于您在那里的优先级，但如上所述，大多数表的读取频率远高于写入频率。
回答by Georgios
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)
回答by George Mastros
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.
您也可以使用联合查询来做到这一点。随着列数的增加，您需要修改查询，但至少这将是一个直接的修改。
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
回答by Learning
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() 仅适用于一列而不适用于跨列。
回答by Sam Saffron
Both this questionAnd this questiontry 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.
回顾一下，Oracle 有一个内置函数，使用 Sql Server，您要么定义用户定义函数，要么使用 case 语句。
