在存在具有值的其他行的情况下，省略具有null值的行

Question

给定此数据：

| x  | y    |
| -- | ---- |
| a  | 1    |
| a  | null |
| b  | 2    |
| b  | 3    |
| b  | 3    |
| b  | null |
| b  | null |
| c  | null |
| c  | null |

我想写一个查询，返回以下结果：

| x  | y    |
| -- | ---- |
| a  | 1    |
| b  | 2    |
| b  | 3    |
| c  | null |

也就是说，对于给定的x值，如果存在具有非null y值的行，则包括所有不同的(x值，y值(行，并省略任何(x值、null(行。但是，如果对于x值，所有行都有null y，则返回(x值，null(。

样本数据

create table t (x text, y int);
insert into t
values ('a' , 1)
, ('a' , null)
, ('b' , 2)
, ('b' , 3)
, ('b' , 3)
, ('b' , null)
, ('b' , null)
, ('c' , null)
, ('c' , null);

Answer 1

实现这一点的多种方法。使用ROW_NUMBER():的一种方法

WITH 
DistinctXYWithRowNumbers AS (
SELECT
*,
ROW_NUMBER() OVER (PARTITION BY x ORDER BY y DESC) AS RowNumber
FROM
(SELECT DISTINCT x, y FROM t) DistinctXY
)
SELECT
x,
y
FROM
DistinctXYWithRowNumbers
WHERE
NOT (y IS NULL AND RowNumber > 1)
ORDER BY
x,
y
;

使用计数表和JOIN:的另一种方法

WITH
Counts AS (
SELECT
x, 
COUNT(DISTINCT y) AS count
FROM
t
GROUP BY
x
)
SELECT
DistinctXY.*
FROM
(SELECT DISTINCT x, y FROM t) DistinctXY
LEFT JOIN Counts ON DistinctXY.x = Counts.x
WHERE
NOT (y IS NULL AND count > 0)
;

Answer 2

您可以使用计数窗口函数如下：

Select Distinct x, y 
From
(
Select x, y, 
COUNT(Case When y Is Not null Then 1 End) Over (Partition By x) cn
From t
) D
Where y Is Not null Or cn = 0
Order By x, y

或者，您可以将Not Exists与相关子查询一起使用，如下所示：

Select Distinct x, y From t As A
Where y Is Not null 
Or (y Is null And Not Exists(Select 1 From t As B Where B.x = A.x And B.y Is Not null))
Order By x, y

查看演示。