如何在 MIN 或 MAX 中包含空值？

我有一个存储时间跨度数据的表。这个表的模式类似于:

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

我正在计算每个记录 ID 的开始和结束日期，所以最小 StartDate 和最大 EndDate。StartDate 不能为空，所以我不需要担心这个，但是我需要 MAX (EndDate)来表明这是当前运行的时间跨度。

维护 EndDate 的 NULL 值并将其视为最大值，这一点很重要。

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

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

我已经创建了一个 SQL 小提琴的基本设置完成。

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

如何使 SQLServer2008按照我的意愿从 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
152338 次浏览
小开

Use IsNull

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

I've modified MIN in the second instruction to MAX

小开
最佳答案

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

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).

小开

Assuming you have only one record with null in EndDate column for a given RecordID, something like this should give you desired output :

WITH cte1 AS
(
SELECT recordid, MIN(startdate) as min_start , MAX(enddate) as max_end
FROM tmp
GROUP BY recordid
)


SELECT a.recordid, a.min_start ,
CASE
WHEN b.recordid IS  NULL THEN a.max_end
END as max_end
FROM cte1 a
LEFT JOIN tmp b ON (b.recordid = a.recordid AND b.enddate IS NULL)
小开

In my expression, count(enddate) counts how many rows where the enddate column is not null. The count(*) expression counts total rows. By comparing, you can easily tell if any value in the enddate column contains null. If they are identical, then max(enddate) is the result. Otherwise the case will default to returning null which is also the answer. This is a very popular way to do this exact check.

SELECT recordid,
MIN(startdate),
case when count(enddate) = count(*) then max(enddate) end
FROM tmp
GROUP BY recordid
小开

The effect you want is to treat the NULL as the largest possible date then replace it with NULL again upon completion:

SELECT RecordId, MIN(StartDate), NULLIF(MAX(COALESCE(EndDate,'9999-12-31')),'9999-12-31')
FROM tmp GROUP BY RecordId

Per your fiddle this will return the exact results you specify under all conditions.

小开

Use the analytic function :

select case when
max(field) keep (dense_rank first order by datfin desc nulls first) is null then 1
else 0 end as flag
from MYTABLE;
小开

I try to use a union to combine two queries to format the returns you want:

SELECT recordid, startdate, enddate FROM tmp Where enddate is null UNION SELECT recordid, MIN(startdate), MAX(enddate) FROM tmp GROUP BY recordid

But I have no idea if the Union would have great impact on the performance


提交答案