这里有一个很好的解决方案:

CREATE function [dbo].[inLineMax] (@v1 float,@v2 float,@v3 float,@v4 float)
returns float
as
begin
declare @val float
set @val = 0 
declare @TableVal table
(value float )
insert into @TableVal select @v1
insert into @TableVal select @v2
insert into @TableVal select @v3
insert into @TableVal select @v4

select @val= max(value) from @TableVal

return @val
end 

我更喜欢基于case-when的解决方案，我的假设是，与其他可能的解决方案(如交叉应用、values()、自定义函数等)相比，它对可能的性能下降的影响应该最小。

下面是case-when版本，它在大多数可能的测试用例中处理空值:

SELECT
    CASE 
        WHEN Date1 > coalesce(Date2,'0001-01-01') AND Date1 > coalesce(Date3,'0001-01-01') THEN Date1 
        WHEN Date2 > coalesce(Date3,'0001-01-01') THEN Date2 
        ELSE Date3
    END AS MostRecentDate
    , *
from 
(values
     (  1, cast('2001-01-01' as Date), cast('2002-01-01' as Date), cast('2003-01-01' as Date))
    ,(  2, cast('2001-01-01' as Date), cast('2003-01-01' as Date), cast('2002-01-01' as Date))
    ,(  3, cast('2002-01-01' as Date), cast('2001-01-01' as Date), cast('2003-01-01' as Date))
    ,(  4, cast('2002-01-01' as Date), cast('2003-01-01' as Date), cast('2001-01-01' as Date))
    ,(  5, cast('2003-01-01' as Date), cast('2001-01-01' as Date), cast('2002-01-01' as Date))
    ,(  6, cast('2003-01-01' as Date), cast('2002-01-01' as Date), cast('2001-01-01' as Date))
    ,( 11, cast(NULL         as Date), cast('2002-01-01' as Date), cast('2003-01-01' as Date))
    ,( 12, cast(NULL         as Date), cast('2003-01-01' as Date), cast('2002-01-01' as Date))
    ,( 13, cast('2003-01-01' as Date), cast(NULL         as Date), cast('2002-01-01' as Date))
    ,( 14, cast('2002-01-01' as Date), cast(NULL         as Date), cast('2003-01-01' as Date))
    ,( 15, cast('2003-01-01' as Date), cast('2002-01-01' as Date), cast(NULL         as Date))
    ,( 16, cast('2002-01-01' as Date), cast('2003-01-01' as Date), cast(NULL         as Date))
    ,( 21, cast('2003-01-01' as Date), cast(NULL         as Date), cast(NULL         as Date))
    ,( 22, cast(NULL         as Date), cast('2003-01-01' as Date), cast(NULL         as Date))
    ,( 23, cast(NULL         as Date), cast(NULL         as Date), cast('2003-01-01' as Date))
    ,( 31, cast(NULL         as Date), cast(NULL         as Date), cast(NULL         as Date))

) as demoValues(id, Date1,Date2,Date3)
order by id
;

结果是:

MostRecent    id   Date1      Date2      Date3
2003-01-01    1    2001-01-01 2002-01-01 2003-01-01
2003-01-01    2    2001-01-01 2003-01-01 2002-01-01
2003-01-01    3    2002-01-01 2001-01-01 2002-01-01
2003-01-01    4    2002-01-01 2003-01-01 2001-01-01
2003-01-01    5    2003-01-01 2001-01-01 2002-01-01
2003-01-01    6    2003-01-01 2002-01-01 2001-01-01
2003-01-01    11   NULL       2002-01-01 2003-01-01
2003-01-01    12   NULL       2003-01-01 2002-01-01
2003-01-01    13   2003-01-01 NULL       2002-01-01
2003-01-01    14   2002-01-01 NULL       2003-01-01
2003-01-01    15   2003-01-01 2002-01-01 NULL
2003-01-01    16   2002-01-01 2003-01-01 NULL
2003-01-01    21   2003-01-01 NULL       NULL
2003-01-01    22   NULL       2003-01-01 NULL
2003-01-01    23   NULL       NULL       2003-01-01
NULL          31   NULL       NULL       NULL

您可以创建一个函数，其中传递日期，然后将该函数添加到select语句，如下所示。 select Number, dbo.fxMost_Recent_Date(Date1,Date2,Date3)，成本

create FUNCTION  fxMost_Recent_Date 

（ @Date1 smalldatetime, @Date2 smalldatetime, @Date3 smalldatetime ） 返回smalldatetime 作为 开始 DECLARE @Result smalldatetime

declare @MostRecent smalldatetime

set @MostRecent='1/1/1900'

if @Date1>@MostRecent begin set @MostRecent=@Date1 end
if @Date2>@MostRecent begin set @MostRecent=@Date2 end
if @Date3>@MostRecent begin set @MostRecent=@Date3 end
RETURN @MostRecent

END

我的解决方案也可以处理空值比较。它可以被简化为一个单一的查询，但为了解释，我使用CTE。这个想法是在第一步中减少从3个数字到2个数字的比较，然后在第二步中从2个数字到1个数字的比较。

with x1 as
(
  select 1 as N1, null as N2, 3 as N3
  union
  select 1 as N1, null as N2, null as N3
  union
  select null as N1, null as N2, null as N3
)
,x2 as
(
select 
N1,N2,N3,
IIF(Isnull(N1,0)>=Isnull(N2,0),N1,N2) as max1,
IIF(Isnull(N2,0)>=Isnull(N3,0),N2,N3) as max2
from x1
)
,x3 as
(
 select N1,N2,N3,max1,max2,
 IIF(IsNull(max1,0)>=IsNull(max2,0),max1,max2) as MaxNo
 from x2
)
select * from x3

输出:

如果您正在使用SQL Server 2005，您可以使用UNPIVOT特性。下面是一个完整的例子:

create table dates 
(
  number int,
  date1 datetime,
  date2 datetime,
  date3 datetime 
)

insert into dates values (1, '1/1/2008', '2/4/2008', '3/1/2008')
insert into dates values (1, '1/2/2008', '2/3/2008', '3/3/2008')
insert into dates values (1, '1/3/2008', '2/2/2008', '3/2/2008')
insert into dates values (1, '1/4/2008', '2/1/2008', '3/4/2008')

select max(dateMaxes)
from (
  select 
    (select max(date1) from dates) date1max, 
    (select max(date2) from dates) date2max,
    (select max(date3) from dates) date3max
) myTable
unpivot (dateMaxes For fieldName In (date1max, date2max, date3max)) as tblPivot

drop table dates

下面是使用T-SQL和SQL Server实现Max功能的另一个很好的解决方案

SELECT [Other Fields],
  (SELECT Max(v) 
   FROM (VALUES (date1), (date2), (date3),...) AS value(v)) as [MaxDate]
FROM [YourTableName]

Values是表值构造函数。

"指定要构造到表中的一组行值表达式。Transact-SQL表值构造函数允许在单个DML语句中指定多行数据。表值构造函数可以指定为INSERT…VALUES语句，或者作为MERGE语句的USING子句或FROM子句中的派生表。