如果存在其他非空值，则删除Null

How do I remove null value when there is other non-null value for a specific CaseNumber?

如果特定CaseNumber存在其他非空值，如何删除空值？

Below is the raw data (Table 1)

以下是原始数据（表1）

CaseNumber |  Date
-----------|-----------
A          | NULL
A          | 08/11/2017
B          | 07/11/2017
B          | 06/11/2017
C          | NULL
C          | NULL
D          | NULL
F          | 05/11/2017
F          | NULL
F          | 04/11/2017
G          | 03/11/2017
G          | NULL

Below is the result that I want.

以下是我想要的结果。

CaseNumber |  Date
-----------|-----------
A          | 08/11/2017
B          | 07/11/2017
B          | 06/11/2017
C          | NULL
D          | NULL
F          | 05/11/2017
F          | 04/11/2017
G          | 03/11/2017

I'm using SQL server 2012.

我正在使用SQL Server 2012。

2 个解决方案

#1

This was answered in a similar question here.

在这里回答了类似的问题。

Easiest way to eliminate NULLs in SELECT DISTINCT?

在SELECT DISTINCT中消除NULL的最简单方法是什么？

In your case:

在你的情况下：

SELECT DISTINCT * FROM Table1
WHERE Date IS NOT NULL OR CaseNumber IN (
  SELECT CaseNumber FROM Table1
  GROUP BY CaseNumber HAVING MAX(Date) IS NULL)

#2

You might select distinct CaseNumber left join all records where Date is not null:

您可以选择不同的CaseNumber左连接所有记录，其中Date不为null：

;with not_null as (
  select * from t
  where date is not null
), unique_case as (
  select distinct casenumber 
  from t
)
select unique_case.CaseNumber, not_null.Date from unique_case
left outer join not_null
on unique_case.CaseNumber=not_null.CaseNumber

Here is the fiddle with fake data.

这是假数据的小提琴。

#1