SQL Server 中是否有一个 Max 函数，它采用两个值，如 .NET 中的 Math.Max？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/124417/
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
Is there a Max function in SQL Server that takes two values like Math.Max in .NET?
提问by skb
I want to write a query like this:
我想写一个这样的查询：
SELECT o.OrderId, MAX(o.NegotiatedPrice, o.SuggestedPrice)
FROM Order o
But this isn't how the MAXfunction works, right? It is an aggregate function so it expects a single parameter and then returns the MAX of all rows.
但这不是MAX函数的工作原理，对吧？它是一个聚合函数，因此它需要一个参数，然后返回所有行的 MAX。
Does anyone know how to do it my way?
有谁知道如何按照我的方式做？
采纳答案by Kevin Crumley
You'd need to make a User-Defined Functionif you wanted to have syntax similar to your example, but could you do what you want to do, inline, fairly easily with a CASEstatement, as the others have said.
User-Defined Function如果您想使用类似于您的示例的语法，您需要创建一个，但是您可以CASE像其他人所说的那样用语句轻松地内联做您想做的事情。
The UDFcould be something like this:
该UDF会是这样的：
create function dbo.InlineMax(@val1 int, @val2 int)
returns int
as
begin
if @val1 > @val2
return @val1
return isnull(@val2,@val1)
end
... and you would call it like so ...
......你会这样称呼它......
SELECT o.OrderId, dbo.InlineMax(o.NegotiatedPrice, o.SuggestedPrice)
FROM Order o
回答by MikeTeeVee
If you're using SQL Server 2008 (or above), then this is the better solution:
如果您使用的是 SQL Server 2008（或更高版本），那么这是更好的解决方案：
SELECT o.OrderId,
(SELECT MAX(Price)
FROM (VALUES (o.NegotiatedPrice),(o.SuggestedPrice)) AS AllPrices(Price))
FROM Order o
All credit and votes should go to Sven's answer to a related question, "SQL MAX of multiple columns?"
I say it's the "best answer" because:
所有的功劳和投票都应该归于 Sven 对相关问题“多列的 SQL MAX？”的回答。
我说这是“最佳答案”，因为：
- It doesn't require complicating your code with UNION's, PIVOT's, UNPIVOT's, UDF's, and crazy-long CASE statments.
- It isn't plagued with the problem of handling nulls, it handles them just fine.
- It's easy to swap out the "MAX" with "MIN", "AVG", or "SUM". You can use any aggregate function to find the aggregate over many different columns.
- You're not limited to the names I used (i.e. "AllPrices" and "Price"). You can pick your own names to make it easier to read and understand for the next guy.
- You can find multiple aggregates using SQL Server 2008's derived_tableslike so:
SELECT MAX(a), MAX(b) FROM (VALUES (1, 2), (3, 4), (5, 6), (7, 8), (9, 10) ) AS MyTable(a, b)
- 它不需要使用 UNION、PIVOT、UNPIVOT、UDF 和疯狂长的 CASE 语句使您的代码复杂化。
- 它没有被处理空值的问题所困扰，它可以很好地处理它们。
- 用“MIN”、“AVG”或“SUM”替换“MAX”很容易。您可以使用任何聚合函数来查找许多不同列的聚合。
- 您不限于我使用的名称（即“AllPrices”和“Price”）。您可以选择自己的名字，以便下一个人更容易阅读和理解。
- 您可以使用 SQL Server 2008 的衍生表找到多个聚合，如下所示：
SELECT MAX(a), MAX(b) FROM (VALUES (1, 2), (3, 4), (5, 6), (7, 8), (9, 10) ) AS MyTable(a, b)
回答by splattne
Can be done in one line:
可以在一行中完成：
-- the following expression calculates ==> max(@val1, @val2)
SELECT 0.5 * ((@val1 + @val2) + ABS(@val1 - @val2))
Edit:If you're dealing with very large numbers you'll have to convert the value variables into bigint in order to avoid an integer overflow.
编辑：如果您正在处理非常大的数字，则必须将值变量转换为 bigint 以避免整数溢出。
回答by Scott Langham
I don't think so. I wanted this the other day. The closest I got was:
我不这么认为。前几天我想要这个。我得到的最接近的是：
SELECT
o.OrderId,
CASE WHEN o.NegotiatedPrice > o.SuggestedPrice THEN o.NegotiatedPrice
ELSE o.SuggestedPrice
END
FROM Order o
回答by Xin
Why not try IIFfunction (requires SQL Server 2012 and later)
为什么不试试IIF函数（需要 SQL Server 2012 及更高版本）
IIF(a>b, a, b)
That's it.
就是这样。
(Hint: be careful about either would be null, since the result of a>bwill be false whenever either is null. So bwill be the result in this case)
（提示：要小心任何一个 would be null，因为a>b只要任何一个为空，结果就会为假。b在这种情况下，结果也是如此）
回答by jbeanky
DECLARE @MAX INT
@MAX = (SELECT MAX(VALUE)
FROM (SELECT 1 AS VALUE UNION
SELECT 2 AS VALUE) AS T1)
回答by jbeanky
The other answers are good, but if you have to worry about having NULL values, you may want this variant:
其他答案很好，但如果您不得不担心有 NULL 值，您可能需要这个变体：
SELECT o.OrderId,
CASE WHEN ISNULL(o.NegotiatedPrice, o.SuggestedPrice) > ISNULL(o.SuggestedPrice, o.NegotiatedPrice)
THEN ISNULL(o.NegotiatedPrice, o.SuggestedPrice)
ELSE ISNULL(o.SuggestedPrice, o.NegotiatedPrice)
END
FROM Order o
回答by LukStorms
In SQL Server 2012 or higher, you can use a combination of IIFand ISNULL(or COALESCE) to get the maximum of 2 values.
Even when 1 of them is NULL.
在 SQL Server 2012 或更高版本中，您可以使用IIF和ISNULL（或COALESCE）的组合来获取最多 2 个值。
即使其中 1 个为 NULL。
IIF(col1 >= col2, col1, ISNULL(col2, col1))
Or if you want it to return 0 when both are NULL
或者，如果您希望它在两者都为 NULL 时返回 0
IIF(col1 >= col2, col1, COALESCE(col2, col1, 0))
Example snippet:
示例片段：
-- use table variable for testing purposes
declare @Order table
(
OrderId int primary key identity(1,1),
NegotiatedPrice decimal(10,2),
SuggestedPrice decimal(10,2)
);
-- Sample data
insert into @Order (NegotiatedPrice, SuggestedPrice) values
(0, 1),
(2, 1),
(3, null),
(null, 4);
-- Query
SELECT
o.OrderId, o.NegotiatedPrice, o.SuggestedPrice,
IIF(o.NegotiatedPrice >= o.SuggestedPrice, o.NegotiatedPrice, ISNULL(o.SuggestedPrice, o.NegotiatedPrice)) AS MaxPrice
FROM @Order o
Result:
结果：
OrderId NegotiatedPrice SuggestedPrice MaxPrice
1 0,00 1,00 1,00
2 2,00 1,00 2,00
3 3,00 NULL 3,00
4 NULL 4,00 4,00
But if one needs to SUM multiple values?
Then I suggest to CROSS APPLY to an aggregation of the VALUES.
This also has the benefit that this can calculate other things at the same time.
但是如果需要对多个值求和呢？
然后我建议将 CROSS APPLY 应用于 VALUES 的聚合。
这还有一个好处就是可以同时计算其他东西。
Example:
例子：
SELECT t.*
, ca.[Total]
, ca.[Maximum]
, ca.[Minimum]
, ca.[Average]
FROM SomeTable t
CROSS APPLY (
SELECT
SUM(v.col) AS [Total],
MIN(v.col) AS [Minimum],
MAX(v.col) AS [Maximum],
AVG(v.col) AS [Average]
FROM (VALUES (t.Col1), (t.Col2), (t.Col3), (t.Col4)) v(col)
) ca
回答by Martin Smith
Sub Queries can access the columns from the Outer query so you can use this approachto use aggregates such as MAXacross columns. (Probably more useful when there is a greater number of columns involved though)
子查询可以访问外部查询中的列，因此您可以使用这种方法来使用聚合，例如MAX跨列。（虽然当涉及更多列时可能更有用）
;WITH [Order] AS
(
SELECT 1 AS OrderId, 100 AS NegotiatedPrice, 110 AS SuggestedPrice UNION ALL
SELECT 2 AS OrderId, 1000 AS NegotiatedPrice, 50 AS SuggestedPrice
)
SELECT
o.OrderId,
(SELECT MAX(price)FROM
(SELECT o.NegotiatedPrice AS price
UNION ALL SELECT o.SuggestedPrice) d)
AS MaxPrice
FROM [Order] o
回答by SetFreeByTruth
SQL Server 2012 introduced IIF:
SQL Server 2012 介绍IIF：
SELECT
o.OrderId,
IIF( ISNULL( o.NegotiatedPrice, 0 ) > ISNULL( o.SuggestedPrice, 0 ),
o.NegotiatedPrice,
o.SuggestedPrice
)
FROM
Order o
Handling NULLs is recommended when using IIF, because a NULLon either side of your boolean_expressionwill cause IIFto return the false_value(as opposed to NULL).
建议在使用时处理 NULL IIF，因为NULL在您的任何一侧boolean_expression都会导致IIF返回false_value（而不是NULL）。
