如果你使用的是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)

其实很简单:

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;

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

以下是一个带有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))

在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

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

哎呀，我刚刚发布了一个关于这个问题的恶搞帖…

答案是，没有像Oracle's Greatest这样的内置函数，但是您可以通过UDF为两个列实现类似的结果，注意，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