我想写一个这样的查询:
SELECT o.OrderId, MAX(o.NegotiatedPrice, o.SuggestedPrice)
FROM Order o
但这不是 MAX
函数的工作方式,对吧?它是一个聚合函数,因此它需要一个参数,然后返回所有行的 MAX。
有谁知道如何按照我的方式做?
GREATEST
函数实现; SQLite 通过允许 MAX
聚合中的多个列来模拟支持。
GREATER
,而不是 GREATEST
。因此,请检查您的 DBMS 的帮助,如果您没有找到一个,请尝试另一个,或类似的东西。
如果您使用的是 SQL Server 2008(或更高版本),那么这是更好的解决方案:
SELECT o.OrderId,
(SELECT MAX(Price)
FROM (VALUES (o.NegotiatedPrice),(o.SuggestedPrice)) AS AllPrices(Price))
FROM Order o
所有功劳和投票都应归于 Sven's answer to a related question, "SQL MAX of multiple columns?"
我说这是“最佳答案”,因为:
它不需要使用 UNION、PIVOT、UNPIVOT、UDF 和疯狂的 CASE 语句使您的代码复杂化。它不受处理空值问题的困扰,它处理得很好。用“MIN”、“AVG”或“SUM”替换“MAX”很容易。您可以使用任何聚合函数来查找许多不同列的聚合。您不限于我使用的名称(即“AllPrices”和“Price”)。您可以选择自己的名字,以便下一个人更容易阅读和理解。您可以使用 SQL Server 2008 的 derived_tables 找到多个聚合,如下所示:SELECT MAX(a), MAX(b) FROM (VALUES (1, 2), (3, 4), (5, 6), (7, 8), (9, 10) ) AS MyTable(a, b)
可以在一行中完成:
-- the following expression calculates ==> max(@val1, @val2)
SELECT 0.5 * ((@val1 + @val2) + ABS(@val1 - @val2))
编辑:如果您要处理非常大的数字,则必须将值变量转换为 bigint 以避免整数溢出。
如果您想使用与示例类似的语法,则需要创建一个 User-Defined Function
,但是您能否像其他人所说的那样,使用 CASE
语句相当容易地做您想做的事情,内联。
UDF
可能是这样的:
create function dbo.InlineMax(@val1 int, @val2 int)
returns int
as
begin
if @val1 > @val2
return @val1
return isnull(@val2,@val1)
end
......你会这样称呼它......
SELECT o.OrderId, dbo.InlineMax(o.NegotiatedPrice, o.SuggestedPrice)
FROM Order o
我不这么认为。前几天我想要这个。我得到的最接近的是:
SELECT
o.OrderId,
CASE WHEN o.NegotiatedPrice > o.SuggestedPrice THEN o.NegotiatedPrice
ELSE o.SuggestedPrice
END
FROM Order o
为什么不试试 IIF 功能(需要 SQL Server 2012 及更高版本)
IIF(a>b, a, b)
而已。
(提示:请注意任何一个都是 null
,因为只要其中一个为空,a>b
的结果就会为假。所以在这种情况下,b
将是结果)
NULL
,则结果将始终是第二个。
NULL > 1234
声明是错误的
IIF(a>b, a, COALESCE(b,a))
在只存在一个时给出值
DECLARE @MAX INT
@MAX = (SELECT MAX(VALUE)
FROM (SELECT 1 AS VALUE UNION
SELECT 2 AS VALUE) AS T1)
在 SQL Server 2012 或更高版本中,您可以使用 IIF
和 ISNULL
(或 COALESCE
)的组合来获得最多 2 个值。
即使其中 1 个为 NULL。
IIF(col1 >= col2, col1, ISNULL(col2, col1))
或者,如果您希望它在两者都为 NULL 时返回 0
IIF(col1 >= col2, col1, COALESCE(col2, col1, 0))
示例片段:
-- 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
结果:
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
但是如果一个人需要多列的最大值呢?然后我建议对 VALUES 的聚合进行 CROSS APPLY。
例子:
SELECT t.*
, ca.[Maximum]
, ca.[Minimum], ca.[Total], ca.[Average]
FROM SomeTable t
CROSS APPLY (
SELECT
MAX(v.col) AS [Maximum],
MIN(v.col) AS [Minimum],
SUM(v.col) AS [Total],
AVG(v.col) AS [Average]
FROM (VALUES (t.Col1), (t.Col2), (t.Col3), (t.Col4)) v(col)
) ca
这具有额外的好处,即可以同时计算其他事物。
其他答案很好,但如果你不得不担心有 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
子查询可以访问外部查询中的列,因此您可以使用 this approach 跨列使用聚合,例如 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
VALUES
语法更好。
尝试这个。它可以处理超过 2 个值
SELECT Max(v) FROM (VALUES (1), (2), (3)) AS value(v)
GREATEST
编写了一个解决方案,但这个解决方案也在我的桌面 SQL Server Express 上运行
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
我会使用 kcrumley 提供的解决方案,只需稍微修改它以处理 NULL
create function dbo.HigherArgumentOrNull(@val1 int, @val2 int)
returns int
as
begin
if @val1 >= @val2
return @val1
if @val1 < @val2
return @val2
return NULL
end
EDIT 在 Mark 的评论后修改。正如他在 3 值逻辑 x > 中正确指出的那样。 NULL 或 x < NULL 应始终返回 NULL。换句话说,未知的结果。
SQL Server 2012 引入了 IIF
:
SELECT
o.OrderId,
IIF( ISNULL( o.NegotiatedPrice, 0 ) > ISNULL( o.SuggestedPrice, 0 ),
o.NegotiatedPrice,
o.SuggestedPrice
)
FROM
Order o
建议在使用 IIF
时处理 NULL,因为 boolean_expression
两侧的 NULL
将导致 IIF
返回 false_value
(而不是 NULL
)。
我可能不会这样做,因为它比已经提到的 CASE 构造效率低 - 除非,也许,你有两个查询的覆盖索引。无论哪种方式,它都是解决类似问题的有用技术:
SELECT OrderId, MAX(Price) as Price FROM (
SELECT o.OrderId, o.NegotiatedPrice as Price FROM Order o
UNION ALL
SELECT o.OrderId, o.SuggestedPrice as Price FROM Order o
) as A
GROUP BY OrderId
糟糕,我刚刚发布了 dupe of this question...
答案是,没有像 Oracle's Greatest 这样的内置函数,但是您可以使用 UDF 为 2 列实现类似的结果,注意,这里 sql_variant 的使用非常重要。
create table #t (a int, b int)
insert #t
select 1,2 union all
select 3,4 union all
select 5,2
-- option 1 - A case statement
select case when a > b then a else b end
from #t
-- option 2 - A union statement
select a from #t where a >= b
union all
select b from #t where b > a
-- option 3 - A udf
create function dbo.GREATEST
(
@a as sql_variant,
@b as sql_variant
)
returns sql_variant
begin
declare @max sql_variant
if @a is null or @b is null return null
if @b > @a return @b
return @a
end
select dbo.GREATEST(a,b)
from #t
发布了这个答案:
create table #t (id int IDENTITY(1,1), a int, b int)
insert #t
select 1,2 union all
select 3,4 union all
select 5,2
select id, max(val)
from #t
unpivot (val for col in (a, b)) as unpvt
group by id
它就像这样简单:
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;
就在这里。
T-SQL (SQL Server 2022 (16.x)) 现在支持 GREATEST/LEAST 函数:
MAX/MIN 作为非聚合函数 现在适用于 Azure SQL 数据库和 SQL 托管实例。它将滚动到 SQL Server 的下一个版本。
Logical Functions - GREATEST (Transact-SQL)
此函数返回一个或多个表达式列表中的最大值。最大(表达式 1 [ ,...表达式 N ] )
所以在这种情况下:
SELECT o.OrderId, GREATEST(o.NegotiatedPrice, o.SuggestedPrice)
FROM Order o
GREATEST
目前仅在 SQL Server Azure 上可用
你可以这样做:
select case when o.NegotiatedPrice > o.SuggestedPrice
then o.NegotiatedPrice
else o.SuggestedPrice
end
SELECT o.OrderID
CASE WHEN o.NegotiatedPrice > o.SuggestedPrice THEN
o.NegotiatedPrice
ELSE
o.SuggestedPrice
END AS Price
对于上面关于大数的答案,您可以在加法/减法之前进行乘法运算。它有点笨重,但不需要演员表。 (我不能说速度,但我认为它仍然很快)
选择 0.5 * ((@val1 + @val2) + ABS(@val1 - @val2))
更改为
选择@val1*0.5+@val2*0.5 + ABS(@val1*0.5 - @val2*0.5)
如果您想避免强制转换,至少是另一种选择。
这是一个应该处理空值并将与旧版本的 MSSQL 一起使用的案例示例。这是基于其中一个流行示例中的内联函数:
case
when a >= b then a
else isnull(b,a)
end
-- Simple way without "functions" or "IF" or "CASE"
-- Query to select maximum value
SELECT o.OrderId
,(SELECT MAX(v)
FROM (VALUES (o.NegotiatedPrice), (o.SuggestedPrice)) AS value(v)) AS MaxValue
FROM Order o;
VALUES
内联,但我不确定这是否比 CASE
或 IFF
更简单。我很想看看这个解决方案的性能如何与其他选项相提并论
VALUES
的有趣之处在于,它提供了一种简单的方法来检查多个列的 MAX
值。至于性能,我不知道:)
CREATE FUNCTION [dbo].[fnMax] (@p1 INT, @p2 INT)
RETURNS INT
AS BEGIN
DECLARE @Result INT
SET @p2 = COALESCE(@p2, @p1)
SELECT
@Result = (
SELECT
CASE WHEN @p1 > @p2 THEN @p1
ELSE @p2
END
)
RETURN @Result
END
这是@Scott Langham 的简单 NULL 处理答案:
SELECT
o.OrderId,
CASE WHEN (o.NegotiatedPrice > o.SuggestedPrice OR o.SuggestedPrice IS NULL)
THEN o.NegotiatedPrice
ELSE o.SuggestedPrice
END As MaxPrice
FROM Order o
这是一个带有 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))
select OrderId, (
select max([Price]) from (
select NegotiatedPrice [Price]
union all
select SuggestedPrice
) p
) from [Order]
以最简单的形式...
CREATE FUNCTION fnGreatestInt (@Int1 int, @Int2 int )
RETURNS int
AS
BEGIN
IF @Int1 >= ISNULL(@Int2,@Int1)
RETURN @Int1
ELSE
RETURN @Int2
RETURN NULL --Never Hit
END
对于 SQL Server 2012:
SELECT
o.OrderId,
IIF( o.NegotiatedPrice >= o.SuggestedPrice,
o.NegotiatedPrice,
ISNULL(o.SuggestedPrice, o.NegiatedPrice)
)
FROM
Order o
扩展 Xin 的答案并假设比较值类型是 INT,这种方法也适用:
SELECT IIF(ISNULL(@A, -2147483648) > ISNULL(@B, -2147483648), @A, @B)
这是带有示例值的完整测试:
DECLARE @A AS INT
DECLARE @B AS INT
SELECT @A = 2, @B = 1
SELECT IIF(ISNULL(@A, -2147483648) > ISNULL(@B, -2147483648), @A, @B)
-- 2
SELECT @A = 2, @B = 3
SELECT IIF(ISNULL(@A, -2147483648) > ISNULL(@B, -2147483648), @A, @B)
-- 3
SELECT @A = 2, @B = NULL
SELECT IIF(ISNULL(@A, -2147483648) > ISNULL(@B, -2147483648), @A, @B)
-- 2
SELECT @A = NULL, @B = 1
SELECT IIF(ISNULL(@A, -2147483648) > ISNULL(@B, -2147483648), @A, @B)
-- 1
在 MemSQL 中执行以下操作:
-- DROP FUNCTION IF EXISTS InlineMax;
DELIMITER //
CREATE FUNCTION InlineMax(val1 INT, val2 INT) RETURNS INT AS
DECLARE
val3 INT = 0;
BEGIN
IF val1 > val2 THEN
RETURN val1;
ELSE
RETURN val2;
END IF;
END //
DELIMITER ;
SELECT InlineMax(1,2) as test;