如何在最小值或最大值中包含空值?

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

我有一个存储timespan数据的表。该表的模式类似于:

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是不可空的，所以我不需要担心这个问题，但是我需要MAX(EndDate)来表示这是一个正在运行的timespan。

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

保持EndDate的空值并将其视为最大值是很重要的。

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小提琴与基本设置完成。

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

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

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

7 个解决方案

#1

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

也就是说，如果任何一行都是空的，我们想让它成为答案。只有当没有行包含NULL时，我们才应该返回MIN(或MAX)。

#2

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.

根据您的小提琴，这将返回您在所有条件下指定的确切结果。

#3

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

在我的表达式中—count(enddate)计算有多少行enddates不是null count(*)，通过比较，您可以很容易地知道任何一个enddates是否包含null。如果它们是相同的，那么max(enddate)就是结果。否则，将默认返回null，这也是答案。这是一种非常流行的方法来进行精确的检查。

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

#4

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

我把第二指令中的最小值修改为MAX

#5

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

假设对于给定的记录，在EndDate列中只有一条记录为空，那么类似这样的内容应该会给您期望的输出:

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)

#6

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;

#7

-1

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

从tmp中选择recordid, startdate, enddate，其中enddate为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

但我不知道工会是否会对绩效产生巨大的影响

#1