在表格中查找不存在的值

Question

假设我有一个表格：

ID   ACCNT   DESC
1    123     aaa
2    234     bbb
3    345     ccc

我有一份帐号列表，其中包括123、345、555和777。
因此，我可以通过以下方式获取已存在的列表：

SELECT * FROM MY_TABLE WHERE ACCNT IN (...my list...)

现在我想达到相反的结果。
从我的列表中，我想要一个查询来显示那些在表中不存在的账号编号。

因此，在我的例子中，我想要报告555和77在表中不存在。

如何在Oracle SQL中编写此查询？

- John Doe

---

这个列表在表中存在吗？ - Tim Biegeleisen

我猜你有另一个账号表或列表。分享一下，否则无法显示。 - AT-2017

你的数字列表是从哪里来的？ - Nick Krasnov

我收到了一个ID列表。它们在表中不存在。我需要报告该列表中哪些ID在表中不存在。 - John Doe

@NicholasKrasnov 是的，或者创建一个包含这些值的表，然后可以左连接。 - Tim Biegeleisen

Answer 1

select column_value as missing_num
from   table (sys.odcinumberlist (123,345,555,777))
where  column_value not in (select accnt from my_table);

- user5683823

---

要对字符串列表执行相同的操作，请使用 sys.odcivarchar2list 替换 sys.odcinumberlist 如果 "my_table" 是一个大表，则以下查询速度更快： select column_value as missing_num from table(sys.odcivarchar2list('abc','cde','def')) t where not exists (select 1 from my_table where my_table.accnt = t.column_value); - Mariano Paniga

Answer 2

以下方法可能适用于Oracle。您可以使用UNION创建一个子查询，其中包含您的帐户ID列表的单个列。然后，您可以将其与您的表进行LEFT JOIN，并隔离与MY_TABLE中任何内容都不匹配的ID。

SELECT t1.ACCNT
FROM
(
    SELECT 123 AS ACCNT FROM DUAL
    UNION ALL
    SELECT 345 FROM DUAL
    UNION ALL
    SELECT 555 FROM DUAL
    UNION ALL
    SELECT 777 FROM DUAL
) t1
LEFT JOIN MY_TABLE t2
    ON t1.ACCNT = t2.ACCNT
WHERE t2.ACCNT IS NULL

这种方法的一个优点是它实际上不会创建一个表格，这可能是你在查询之后不想要的。

- Tim Biegeleisen

Answer 3

这取决于您的帐号号码列表来源。例如，您可以使用集合类型和TABLE运算符：

-- Test case setup
CREATE TABLE ACCNT(ID,ACCNT,DESCR) AS
SELECT 1,123, 'aaa' FROM DUAL UNION ALL
SELECT 2,234, 'bbb' FROM DUAL UNION ALL
SELECT 3,345, 'ccc' FROM DUAL;

-- Create a collection type to hold account numbers
CREATE TYPE tab_numbers AS TABLE OF NUMBER;
/

-- Select account numbers that does not occur on the table
SELECT *
  FROM TABLE(TAB_NUMBERS(123,345,555,777))
 WHERE COLUMN_VALUE NOT IN (SELECT ACCNT FROM ACCNT);

- Daniela Petruzalek

Answer 4

这是一种方法。您可以简单地创建一个模式级的集合（嵌套表），并使用右/左外连接：

-- our collection type
create type t_list as table of number;
/

-- sample of data from your question
with t1(id1, accnt, desc1) as(
  select 1, 123, 'aaa' from dual union all
  select 2, 234, 'bbb' from dual union all
  select 3, 345, 'ccc' from dual
)
-- query itself
select t2.column_value as missing_num
  from t1                               <-- here goes your table
  right join table(t_list(123,345,555,777)) t2
     on (t2.column_value = t1.accnt)
  where t1.accnt is null 

结果：

MISSING_NUM
-----------
        555
        777

2 rows selected.

- Nick Krasnov

Answer 5

您可以使用WHERE NOT EXISTS，以下是示例;

SELECT *
FROM MY_TABLE MT 
WHERE NOT EXISTS (SELECT * FROM AccountNumbers AN WHERE MT.accountID = AN.id)

- Ryan Gavin

Answer 6

一种实现方法是使用 unpivot 函数：

select Number as ACCNT from(
select * from (
select 123,345,555,777 from dual ) 
unpivot
(
  "Values" FOR "Number" IN ("123","235","555","777")
 ) 
 ) 
minus

select ACCNT from my_table ;

输出应该是你列表中剩下的账号号码。

- user3315556