查询以过滤具有相同非主键值的行，然后从 Oracle 中的结果中排除具有另一个空列的记录

debugcn 发表于 Dev

阿卡纳

表 Mytable 的示例数据

+----------+--------+----------+
|   BPT    |   BC   |    ST    |
+----------+--------+----------+
| NH       | AB360  | PTOTST   |
| MEMODMHF | AAAAA  | PTOTST   |
| NH       |        | PTOTST   |
| NH       |  ABH6G | PTOTSTCH |
| NH       |        |  PT01    |
| NH       | ABH6G  | PT04     |
| NH       |        | PT04     |
+----------+--------+----------+

过滤具有值 NH 的 BPT 列

+------+--------+----------+
| BPT  |   BC   |    ST    |
+------+--------+----------+
| NH   | AB360  | PTOTST   |
| NH   |        | PTOTST   |
| NH   |  ABH6G | PTOTSTCH |
| NH   |        |  PT01    |
| NH   | ABH6G  | PT04     |
| NH   |        | PT04     |
+------+--------+----------+

过滤值为空或 ABH6G 的 BC 列

+------+--------+----------+
| BPT  |   BC   |    ST    |
+------+--------+----------+
| NH   |        | PTOTST   |
| NH   |  ABH6G | PTOTSTCH |
| NH   |        |  PT01    |
| NH   | ABH6G  | PT04     |
| NH   |        | PT04     |
+------+--------+----------+

对于重复的 ST，排除 BC 为空的记录

+------+--------+----------+
| BPT  |   BC   |    ST    |
+------+--------+----------+
| NH   |        | PTOTST   |
| NH   |  ABH6G | PTOTSTCH |
| NH   |        |  PT01    |
| NH   | ABH6G  | PT04     |
+------+--------+----------+

下面提到的我的示例查询不起作用。如何实现这一点。

SELECT
  T1.BPT,
  T1.BC,
  T1.ST
FROM Mytable T1,
     Mytable T2
WHERE T1.BEN_PROD_TYP_CD IN ('NH')
AND ((T1.ST = T2.ST
AND T1.BC = 'ABH6G')
OR (T1.ST <> T2.ST
AND (T1.BC = 'ABH6G'
OR T1.BC IS NULL)));

扎鲁克

实现结果的一种方法是使用row_number()：

select t.BPT, t.BC, t.ST from
(
select t1.*, row_number() over(partition by st order by BC asc) as rn 
from MyTable t1
where t1.BPT = 'NH'
and (t1.BC = 'ABH6G' or t1.BC is null)
) t
where t.rn = 1

结果：

+-----+-------+----------+
| BPT |  BC   |    ST    |
+-----+-------+----------+
| NH  | NULL  | PTOTST   |
| NH  | ABH6G | PTOTSTCH |
| NH  | NULL  | PT01     |
| NH  | ABH6G | PT04     |
+-----+-------+----------+

演示

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。