我可能不会这样做，因为它比前面提到的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

 -- 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;

select OrderId, (
    select max([Price]) from (
        select NegotiatedPrice [Price]
        union all
        select SuggestedPrice
    ) p
) from [Order]

在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;

我不这么想。我那天想要这个。我最接近的说法是:

SELECT
  o.OrderId,
  CASE WHEN o.NegotiatedPrice > o.SuggestedPrice THEN o.NegotiatedPrice 
     ELSE o.SuggestedPrice
  END
FROM Order o

在SQL Server 2012或更高版本中，您可以使用IIF和ISNULL(或COALESCE)的组合来获得最多2个值。 即使其中一个是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

但如果需要多列中的最大值呢? 然后我建议在值的聚合上使用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

这有一个额外的好处，它可以同时计算其他的东西。