SQL 在两列之间选择最近的日期
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/414222/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Selecting most recent date between two columns
提问by TheTXI
If I have a table that (among other columns) has two DATETIME columns, how would I select the most recentdate from those two columns.
如果我有一个表（在其他列中）有两个 DATETIME 列，我将如何从这两列中选择最近的日期。
Example:
例子：
ID Date1 Date2
1 1/1/2008 2/1/2008
2 2/1/2008 1/1/2008
3 1/10/2008 1/10/2008
If I wanted my results to look like
如果我希望我的结果看起来像
ID MostRecentDate
1 2/1/2008
2 2/1/2008
3 1/10/2008
Is there a simple way of doing this that I am obviously overlooking? I know I can do subqueries and case statements or even write a function in sql server to handle it, but I had it in my head that there was a max-compare type function already built in that I am just forgetting about.
是否有一种简单的方法可以做到这一点，但我显然忽略了这一点？我知道我可以做子查询和 case 语句，甚至可以在 sql server 中编写一个函数来处理它，但我脑子里有一个已经内置的 max-compare 类型函数，我只是忘记了。
回答by Rockcoder
回答by RedFilter
select ID,
case
when Date1 > Date2 then Date1
else Date2
end as MostRecentDate
from MyTable
回答by Michael Haren
You can throw this into a scalar function, which makes handling nulls a little easier. Obviously it isn't going to be any faster than the inline case statement.
您可以将其放入标量函数中，这样可以更轻松地处理空值。显然它不会比内联 case 语句快。
ALTER FUNCTION [fnGetMaxDateTime] (
@dtDate1 DATETIME,
@dtDate2 DATETIME
) RETURNS DATETIME AS
BEGIN
DECLARE @dtReturn DATETIME;
-- If either are NULL, then return NULL as cannot be determined.
IF (@dtDate1 IS NULL) OR (@dtDate2 IS NULL)
SET @dtReturn = NULL;
IF (@dtDate1 > @dtDate2)
SET @dtReturn = @dtDate1;
ELSE
SET @dtReturn = @dtDate2;
RETURN @dtReturn;
END
回答by JStevens
I think the accepted answer is the simplest. However, I would watch for null values in the dates...
我认为公认的答案是最简单的。但是，我会注意日期中的空值...
SELECT ID,
CASE WHEN ISNULL(Date1,'01-01-1753') > ISNULL(Date2,'01-01-1753') THEN Date1
ELSE Date2
END AS MostRecentDate
FROM Table
回答by Andre Figueiredo
回答by Charles Bretana
Whenever possible, use InLine functions as they suffer none of the performance issues generally associated with UDFs...
只要有可能，就使用 InLine 函数，因为它们不会遇到通常与 UDF 相关的性能问题......
Create FUNCTION MaximumDate
(
@DateTime1 DateTime,
@DateTime2 DateTime
)
RETURNS TABLE
AS
RETURN
(
Select Case When @DateTime1 > @DateTime2 Then @DateTime1
Else @DateTime2 End MaxDate
)
GO
For usage guidelines, see Here
有关使用指南，请参阅 此处
回答by Charles Bretana
Other than case statement, I don't believe so...
除了案件陈述，我不相信......
Select Case When DateColA > DateColB Then DateColA
Else DateColB End MostRecent
From Table ...
回答by Dhananjay
All other correct answers as already posted.
已经发布的所有其他正确答案。
But if you are still really looking for MAX keyword then here is a way :
但是，如果您仍然真的在寻找 MAX 关键字，那么这里有一个方法：
select ID , MAX(dt) from
( select Id , Date1 as dt from table1
union
select ID , Date2 from table2
) d
group by d.Id
回答by Recep
AFAIK, there is no built-in function to get the maximum of two values, but you can write your own easily as:
AFAIK，没有内置函数来获取两个值的最大值，但您可以轻松编写自己的函数：
CREATE FUNCTION dbo.GetMaximumDate(@date1 DATETIME, @date2 DATETIME)
RETURNS DATETIME
AS
BEGIN
IF (@date1 > @date2)
RETURN @date1
RETURN @date2
END
and call it as
并将其称为
SELECT Id, dbo.GetMaximumDate(Date1, Date2)
FROM tableName
回答by Michael - Where's Clay Shirky
This threadhas several solutions. If you had more than 2 dates to compare, "unpivot" might be preferable to writing a series of case statements. The following is blatantly stolen from Niikola:
这个线程有几个解决方案。如果要比较的日期超过 2 个，“unpivot”可能比编写一系列 case 语句更可取。以下是从Niikola公然窃取的：
select id, max(dDate) MostRecentDate
from YourTable
unpivot (dDate for nDate in (Date1, Date2, Date3)) as u
group by id
Then you can order by dDate, if that's helpful.
那么你可以order by dDate，如果这有帮助的话。
