在日期值上使用空值

Question

>我必须按Max（StartDate）选择一组记录，并且有多个记录具有相同的开始日期但不同的结束日期，我想选择具有NULL EndDate的记录而不是具有实际日期的值。

SELECT UPC, DocumentNumber, MAX(StartDate) AS 'StartDate'
                FROM #tbDupRecs
                --WHERE EndDate = CASE EndDate WHEN NULL THEN NULL ELSE EndDate END
            GROUP BY UPC, DocumentNumber
            Order By UPC, DocumentNumber, StartDate

当我尝试包含类似 EndDate 的内容时，我收到错误，因为我无法将其包含在 Select 语句或分组依据等中......我尝试过的所有内容（如上所述）都选择了带有日期的记录......

Answer 1

我认为这应该有效...它为您分组的每组记录提供一个 RN，并获取顶部的记录。

with cte as(
SELECT 
    UPC, 
    DocumentNumber, 
    StartDate,
    row_number over (partition by UPC, DocumentNumber order by case when StartDate is null then '12/31/2999' else StartDate end desc) as rn
FROM #tbDupRecs)
select * from cte where rn = 1

或

SELECT 
    UPC, 
    DocumentNumber, 
    StartDate
FROM #tbDupRecs
WHERE EXISTS (SELECT UPC, DocumentNumber, max(isnull(StartDate,'12/31/2999')) FROM #tbDupRecs group by UPC, DocumentNumber)

或

SELECT 
    r.UPC, 
    r.DocumentNumber, 
    r.StartDate
FROM #tbDupRecs r
INNER JOIN 
    (SELECT UPC, DocumentNumber, max(isnull(StartDate,'12/31/2999')) 
    FROM #tbDupRecs 
    group by UPC, DocumentNumber) r2 on r.UPC = r2.UPC and r.DocumentNumber = r2.DocumentNumber and isnull(r.StartDate,'12/31/2999') = isnull(r2.StartDate,'12/31/2999')