我需要过滤满足以下条件的特定记录集任何记录的帐户为“N/A”和金额为0，则应过滤该记录。这里的问题是，有一些记录的帐户为“N/A”，但金额不为0，这些记录不应该被过滤掉。输入
Id account amount1 ABC 51 N/A 02 PQR 62 N/A 03 N/A 84 N/A 9
Id account amount
1 ABC 5
1 N/A 0
2 PQR 6
2 N/A 0
3 N/A 8
4 N/A 9
输出
Id account amount1 ABC 52 PQR 63 N/A 84 N/A 9
有没有人可以帮助这个正确的过滤条件？
7y4bm7vi1#
account = 'N/A' AND amount = 0将匹配您要筛选出的行。假设这两列都不包含NULL值（您的示例数据不包含），那么您可以使用NOT (account = 'N/A' AND amount = 0)反转该过滤器以查找您想要保留的所有行：
account = 'N/A' AND amount = 0
NULL
NOT (account = 'N/A' AND amount = 0)
SELECT *FROM table_nameWHERE NOT (account = 'N/A' AND amount = 0);
SELECT *
FROM table_name
WHERE NOT (account = 'N/A' AND amount = 0);
如果你的列可以有NULL值，并且你想保留它们，那么你可以用途：
SELECT *FROM table_nameWHERE NOT (account = 'N/A' AND amount = 0)OR account IS NULLOR amount IS NULL;
WHERE NOT (account = 'N/A' AND amount = 0)
OR account IS NULL
OR amount IS NULL;
其中，对于样本数据：
CREATE TABLE table_name (id, account, amount) AS SELECT 1, 'ABC', 5 FROM DUAL UNION ALL SELECT 1, 'N/A', 0 FROM DUAL UNION ALL SELECT 2, 'PQR', 6 FROM DUAL UNION ALL SELECT 2, 'N/A', 0 FROM DUAL UNION ALL SELECT 3, 'N/A', 8 FROM DUAL UNION ALL SELECT 4, 'N/A', 9 FROM DUAL
CREATE TABLE table_name (id, account, amount) AS
SELECT 1, 'ABC', 5 FROM DUAL UNION ALL
SELECT 1, 'N/A', 0 FROM DUAL UNION ALL
SELECT 2, 'PQR', 6 FROM DUAL UNION ALL
SELECT 2, 'N/A', 0 FROM DUAL UNION ALL
SELECT 3, 'N/A', 8 FROM DUAL UNION ALL
SELECT 4, 'N/A', 9 FROM DUAL
两个输出：| ID|账户|量|| --|--|--|| 1 |ABC| 5 || 2 |PQR| 6 || 3 |N/A| 8 || 4 |N/A| 9 |fiddle
slhcrj9b2#
一种选择是-对于您发布的示例数据-
SQL> with test (id, account, amount) as 2 (select 1, 'ABC', 5 from dual union all 3 select 1, 'N/A', 0 from dual union all 4 select 2, 'PQR', 6 from dual union all 5 select 2, 'N/A', 0 from dual union all 6 select 3, 'N/A', 8 from dual union all 7 select 4, 'N/A', 9 from dual 8 )
SQL> with test (id, account, amount) as
2 (select 1, 'ABC', 5 from dual union all
3 select 1, 'N/A', 0 from dual union all
4 select 2, 'PQR', 6 from dual union all
5 select 2, 'N/A', 0 from dual union all
6 select 3, 'N/A', 8 from dual union all
7 select 4, 'N/A', 9 from dual
8 )
查询方式：
9 select * 10 from test 11 where account <> 'N/A' 12 or amount <> 0; ID ACCOUNT AMOUNT---------- -------- ---------- 1 ABC 5 2 PQR 6 3 N/A 8 4 N/A 9SQL>
9 select *
10 from test
11 where account <> 'N/A'
12 or amount <> 0;
ID ACCOUNT AMOUNT
---------- -------- ----------
SQL>
2条答案
按热度按时间7y4bm7vi1#
account = 'N/A' AND amount = 0将匹配您要筛选出的行。假设这两列都不包含NULL值（您的示例数据不包含），那么您可以使用NOT (account = 'N/A' AND amount = 0)反转该过滤器以查找您想要保留的所有行：如果你的列可以有
NULL值，并且你想保留它们，那么你可以用途：其中，对于样本数据：
两个输出：
| ID|账户|量|
| --|--|--|
| 1 |ABC| 5 |
| 2 |PQR| 6 |
| 3 |N/A| 8 |
| 4 |N/A| 9 |
fiddle
slhcrj9b2#
一种选择是-对于您发布的示例数据-
查询方式：