时间戳

首页 > 解决方案 > 如果满足 COUNT 条件，我可以应用 WHERE 子句吗？

oracle - 如果满足 COUNT 条件，我可以应用 WHERE 子句吗？

问题描述

仅当有其他行返回同一列的数据时，我很难尝试添加一个从列WHERE中过滤值的子句。null

如果所有行都有null该列的值，请保留所有行。

如果任何行具有该列的数据，请删除具有空值的行并仅保留具有数据的行。

我正在开发一个 Oracle 数据库。

在我的 SELECT 语句中，我目前正在使用 aLEFT JOIN从表 B中提取数据，即使B.info列的值为空。

实际查询如下：

SELECT A.id as A_ID, A.name as A_NAME, 
B.id as B_ID, B.name as B_NAME, B.info as B_INFO
FROM A
LEFT JOIN B ON B.id = A.id_B
WHERE A.filename = 'file1.txt'

我正在从事的业务中有两种可能的情况：

  1. 对于给定的“文件名”，查询返回一些行，其中的B.info列具有空值，而其他一些行的B.info列中填充了数据。我希望查询只返回带有B.info != null.

场景 1 - 实际输出：

+-------+--------+------+--------+-----------+
| A_ID  | A_NAME | B_ID | B_NAME | B_INFO    |
+-------+--------+------+--------+-----------+
| 1     | John   | null | null   | null      |
+-------+--------+------+--------+-----------+
| 2     | John   | 3    | Julia  | Age is 35 |
+-------+--------+------+--------+-----------+
| 3     | John   | null | null   | null      |
+-------+--------+------+--------+-----------+

场景 1 - 所需的输出：

+-------+--------+------+--------+-----------+
| A_ID  | A_NAME | B_ID | B_NAME | B_INFO    |
+-------+--------+------+--------+-----------+
| 2     | John   | 3    | Julia  | Age is 35 |
+-------+--------+------+--------+-----------+
  1. 对于给定的“文件名”，查询将返回所有行以及B.info列的null值。我希望查询继续返回这些行。

场景 2 - 实际输出 = 期望输出：

+-------+--------+------+--------+--------+
| A_ID  | A_NAME | B_ID | B_NAME | B_INFO |
+-------+--------+------+--------+--------+
| 1     | Mark   | null | null   | null   |
+-------+--------+------+--------+--------+
| 2     | Mark   | null | null   | null   |
+-------+--------+------+--------+--------+
| 3     | Mark   | null | null   | null   |
+-------+--------+------+--------+--------+

我尝试B.info is not null在 where 子句中添加条件，但是，尽管它返回了场景 1 的所需输出，但场景 2 的输出不返回任何行：

SELECT A.id as A_ID, A.name as A_NAME, 
B.id as B_ID, B.name as B_NAME, B.info as B_INFO
FROM A
LEFT JOIN B ON B.id = A.id_B
WHERE A.filename = 'file1.txt' 
AND B.info is not null

场景 1 - 输出

+-------+--------+------+--------+-----------+
| A_ID  | A_NAME | B_ID | B_NAME | B_INFO    |
+-------+--------+------+--------+-----------+
| 2     | John   | 3    | Julia  | Age is 35 |
+-------+--------+------+--------+-----------+

场景 2 - 输出

+-------+--------+------+--------+--------+
| A_ID  | A_NAME | B_ID | B_NAME | B_INFO |
+-------+--------+------+--------+--------+
+-------+--------+------+--------+--------+

我还尝试在 WHERE 子句中添加 CASE，但它会引发错误 ( ORA-00934: group function is not allowed here)

SELECT A.id as A_ID, A.name as A_NAME, 
B.id as B_ID, B.name as B_NAME, B.info as B_INFO
FROM A
LEFT JOIN B ON B.id = A.id_B
WHERE A.filename = 'file1.txt' 
AND B.info = CASE WHEN count(B.info) > 0 THEN null
    ELSE B.info
    END

对不起，我不能用真实的例子来解决保密问题。我希望我的例子足够清楚。我将不胜感激任何帮助！

标签： oracleplsqlcaseoracle-sqldeveloperwhere-clause

解决方案

您可以将此问题视为排名问题：您只想显示最佳行，非空行被认为比空行“更好”。

排名可以通过适当的 ORDER BY 子句来实现。从 Oracle 12c 开始：

select
  a.id as a_id, a.name as a_name, 
  b.id as b_id, b.name as b_name, b.info as b_info
from a
left join b on b.id = a.id_b
where a.filename = 'file1.txt'
order by case when b.id is null then 2 else 1 end
fetch first rows with ties;

在旧版本中：

select a_id, a_name, b_id, b_name, b_info
from
(
  select
    a.id as a_id, a.name as a_name, 
    b.id as b_id, b.name as b_name, b.info as b_info,
    rank() over (order by case when b.id is null then 2 else 1 end) as rnk
  from a
  left join b on b.id = a.id_b
  where a.filename = 'file1.txt'
)
where rnk = 1;

推荐阅读