我想写一个这样的查询:
SELECT o.OrderId, MAX(o.NegotiatedPrice, o.SuggestedPrice)
FROM Order o
但这不是MAX函数的工作原理,对吧?它是一个聚合函数,因此它需要一个参数,然后返回所有行的MAX。
有人知道我的方法吗?
我想写一个这样的查询:
SELECT o.OrderId, MAX(o.NegotiatedPrice, o.SuggestedPrice)
FROM Order o
但这不是MAX函数的工作原理,对吧?它是一个聚合函数,因此它需要一个参数,然后返回所有行的MAX。
有人知道我的方法吗?
当前回答
SELECT o.OrderId,
--MAX(o.NegotiatedPrice, o.SuggestedPrice)
(SELECT MAX(v) FROM (VALUES (o.NegotiatedPrice), (o.SuggestedPrice)) AS value(v)) as ChoosenPrice
FROM Order o
其他回答
你可以这样做:
select case when o.NegotiatedPrice > o.SuggestedPrice
then o.NegotiatedPrice
else o.SuggestedPrice
end
如果你使用的是SQL Server 2008(或更高版本),那么这是更好的解决方案:
SELECT o.OrderId,
(SELECT MAX(Price)
FROM (VALUES (o.NegotiatedPrice),(o.SuggestedPrice)) AS AllPrices(Price))
FROM Order o
所有的信用和投票都应该去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_tables like so: SELECT MAX(a), MAX(b) FROM (VALUES (1, 2), (3, 4), (5, 6), (7, 8), (9, 10) ) AS MyTable(a, b)
以下是一个带有NULL处理的IIF版本(基于Xin的回答):
IIF(a IS NULL OR b IS NULL, ISNULL(a,b), IIF(a > b, a, b))
逻辑如下,如果其中一个值为NULL,则返回非NULL的值(如果两个值都为NULL,则返回NULL)。否则返回较大的值。
MIN也是一样。
IIF(a IS NULL OR b IS NULL, ISNULL(a,b), IIF(a < b, a, b))
DECLARE @MAX INT
@MAX = (SELECT MAX(VALUE)
FROM (SELECT 1 AS VALUE UNION
SELECT 2 AS VALUE) AS T1)
其实很简单:
CREATE FUNCTION InlineMax
(
@p1 sql_variant,
@p2 sql_variant
) RETURNS sql_variant
AS
BEGIN
RETURN CASE
WHEN @p1 IS NULL AND @p2 IS NOT NULL THEN @p2
WHEN @p2 IS NULL AND @p1 IS NOT NULL THEN @p1
WHEN @p1 > @p2 THEN @p1
ELSE @p2 END
END;