如何在 MIN 或 MAX 中包含空值? [英] How can I include null values in a MIN or MAX?

问题描述

我有一张表，用于存储时间跨度数据.该表的架构类似于:

I have a table where I am storing timespan data. the table has a schema similar to:

ID INT NOT NULL IDENTITY(1,1)   
RecordID INT NOT NULL  
StartDate DATE NOT NULL  
EndDate DATE NULL  

而且我正在尝试计算每个记录 ID 的开始和结束日期，因此最小 StartDate 和最大 EndDate.StartDate 不可为空，所以我不需要担心这一点，但我需要 MAX(EndDate) 来表示这是当前的运行时间跨度.

And I am trying to work out the start and end dates for each record id, so the minimum StartDate and maximum EndDate. StartDate is not nullable so I don't need to worry about this but I need the MAX(EndDate) to signify that this is currently a running timespan.

重要的是我保持 EndDate 的 NULL 值并将其视为最大值.

It is important that I maintain the NULL value of the EndDate and treat this as the maximum value.

最简单的尝试(如下)无法突出显示 MIN 和 MAX 将忽略 NULLS 的问题(来源:http://technet.microsoft.com/en-us/library/ms179916.aspx).

The most simple attempt (below) doesn't work highlighting the problem that MIN and MAX will ignore NULLS (source: http://technet.microsoft.com/en-us/library/ms179916.aspx).

SELECT recordid, MIN(startdate), MAX(enddate) FROM tmp GROUP BY recordid

我已经创建了一个基本设置完成的 SQL Fiddle.

I have created an SQL Fiddle with the basic setup done.

http://sqlfiddle.com/#!3/b0a75

如何让 SQL Server 2008 服从我的意愿，以根据 SQLFiddle 中给出的数据生成以下结果?

How can I bend SQL Server 2008 to my will to produce the following result from the data given in the SQLFiddle?

RecordId  Start       End  
1         2009-06-19  NULL
2         2012-05-06  NULL
3         2013-01-25  NULL
4         2004-05-06  2009-12-01

推荐答案

这有点难看，但因为 NULL 对你有特殊的意义，这是我能想到的最干净的方法它:

It's a bit ugly but because the NULLs have a special meaning to you, this is the cleanest way I can think to do it:

SELECT recordid, MIN(startdate),
   CASE WHEN MAX(CASE WHEN enddate IS NULL THEN 1 ELSE 0 END) = 0
        THEN MAX(enddate)
   END
FROM tmp GROUP BY recordid

也就是说，如果任何行具有 NULL，我们希望强制它成为答案.只有当没有行包含 NULL 时，我们才应该返回 MIN(或 MAX).

That is, if any row has a NULL, we want to force that to be the answer. Only if no rows contain a NULL should we return the MIN (or MAX).

这篇关于如何在 MIN 或 MAX 中包含空值?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！