SQL 如何在 MIN 或 MAX 中包含空值？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/21286215/
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
How can I include null values in a MIN or MAX?
提问by Ant Swift
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
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.
我正在尝试计算每个记录 id 的开始和结束日期，因此最小 StartDate 和最大 EndDate。StartDate 不可为空，所以我不需要担心这一点，但我需要 MAX(EndDate) 来表示这是当前的运行时间跨度。
It is important that I maintain the NULL value of the EndDate and treat this as the maximum value.
重要的是我保持 EndDate 的 NULL 值并将其视为最大值。
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).
最简单的尝试（如下）不起作用，突出显示 MIN 和 MAX 将忽略 NULLS 的问题（来源：http: //technet.microsoft.com/en-us/library/ms179916.aspx）。
SELECT recordid, MIN(startdate), MAX(enddate) FROM tmp GROUP BY recordid
I have created an SQL Fiddle with the basic setup done.
我已经完成了基本设置，创建了一个 SQL Fiddle。
http://sqlfiddle.com/#!3/b0a75
http://sqlfiddle.com/#!3/b0a75
How can I bend SQL Server 2008 to my will to produce the following result from the data given in the SQLFiddle?
如何根据我的意愿使 SQL Server 2008 从 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
回答by Damien_The_Unbeliever
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:
这有点难看，但因为NULLs 对你有特殊的意义，这是我能想到的最干净的方法：
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 NULLshould we return the MIN(or MAX).
也就是说，如果任何行有一个NULL，我们想强制它成为答案。只有当没有行包含 a 时NULL，我们才应该返回MIN(或MAX)。
回答by Matthew Erwin
The effect you want is to treat the NULL as the largest possible date then replace it with NULL again upon completion:
您想要的效果是将 NULL 视为可能的最大日期，然后在完成后再次将其替换为 NULL：
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.
根据您的小提琴，这将返回您在所有条件下指定的确切结果。
回答by t-clausen.dk
In my expression, count(enddate)counts how many rows where the enddatecolumn is not null.
The count(*)expression counts total rows.
By comparing, you can easily tell if any value in the enddatecolumn contains null. If they are identical, then max(enddate)is the result. Otherwise the casewill default to returning nullwhich is also the answer. This is a very popular way to do this exact check.
在我的表达式中，count(enddate)计算enddate列不为空的行数。该count(*)表达式计算总行数。通过比较，您可以轻松判断enddate列中是否有任何值包含null。如果它们相同，那么max(enddate)就是结果。否则case将默认返回null，这也是答案。这是进行这种精确检查的一种非常流行的方法。
SELECT recordid,
MIN(startdate),
case when count(enddate) = count(*) then max(enddate) end
FROM tmp
GROUP BY recordid
回答by Marko Juvan?i?
Use IsNull
使用 IsNull
SELECT recordid, MIN(startdate), MAX(IsNull(enddate, Getdate()))
FROM tmp
GROUP BY recordid
I've modified MIN in the second instruction to MAX
我已将第二条指令中的 MIN 修改为 MAX
回答by a1ex07
Assuming you have only one record with null in EndDate column for a given RecordID, something like this should give you desired output :
假设在给定的 RecordID 的 EndDate 列中只有一个为 null 的记录，这样的事情应该会给你想要的输出：
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)
回答by TheBakker
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;
回答by SHO
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
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
但我不知道联盟是否会对业绩产生很大影响
