Oracle SQL -- 查找不在表中的值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9996797/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Oracle SQL -- find the values NOT in a table
提问by Jeremy
Take this table WORDS
拿这张表 WORDS
WORD
Hello
Aardvark
Potato
Dog
Cat
And this list:
还有这个清单：
('Hello', 'Goodbye', 'Greetings', 'Dog')
How do I return a list of words that AREN'T in the words table, but are in my list?
如何返回不在单词表中但在我的列表中的单词列表？
If I have a table that "contains all possible words", I can do:
如果我有一张“包含所有可能的单词”的表格，我可以这样做：
SELECT * from ALL_WORDS_TABLE
where word in ('Hello', 'Goodbye', 'Greetings', 'Dog')
and word not in
(SELECT word from WORDS
where word in ('Hello', 'Goodbye', 'Greetings', 'Dog')
);
However I do not have such a table. How else can this be done?
但是我没有这样的桌子。这还能怎么做？
Also, constructing a new table is not an option because I do not have that level of access.
此外，构建新表不是一种选择，因为我没有那个级别的访问权限。
回答by Wolf
Instead of hard coding the list values into rows, use DBMS_DEBUG_VC2COLLto dynamically convert your delimited list into rows, then use the MINUSoperator to eliminate rows in the second query that are not in the first query:
不是将列表值硬编码为行，而是DBMS_DEBUG_VC2COLL用于将分隔列表动态转换为行，然后使用MINUS运算符消除第二个查询中不在第一个查询中的行：
select column_value
from table(sys.dbms_debug_vc2coll('Hello', 'Goodbye', 'Greetings', 'Dog'))
minus
select word
from words;
回答by Tony Andrews
You can turn your list into a view like this:
您可以将列表变成这样的视图：
select 'Hello' as word from dual
union all
select 'Goodbye' from dual
union all
select 'Greetings' from dual
union all
select 'Dog' from dual
Then you can select from that:
然后你可以从中选择：
select * from
(
select 'Hello' as word from dual
union all
select 'Goodbye' from dual
union all
select 'Greetings' from dual
union all
select 'Dog' from dual
)
where word not in (select word from words);
Possibly not as neat a solution as you might have hoped for...
可能不像您希望的那样简洁的解决方案......
You say you don't have sufficient privileges to create tables, so presumably you can't create types either - but if you can find a suitable type "lying around" in your database you can do this:
你说你没有足够的权限来创建表，所以大概你也不能创建类型 - 但如果你能在你的数据库中找到一个合适的类型，你可以这样做：
select * from table (table_of_varchar2_type('Hello','Goodbye','Greetings','Dog'))
where column_value not in (select word from words);
Here table_of_varchar2_typeis imagined to be the name of a type that is defined like:
这里table_of_varchar2_type被想象成一个类型的名称，定义如下：
create type table_of_varchar2_type as table of varchar2(100);
One such type you are likely to be able to find is SYS.KU$_VCNTwhich is a TABLE OF VARCHAR2(4000).
您可能能够找到的一种SYS.KU$_VCNT类型是 TABLE OF VARCHAR2(4000)。
回答by denied
Try this solution :
试试这个解决方案：
SELECT
a.word
FROM
(
SELECT 'Hello' word FROM DUAL UNION
SELECT 'Goodbye' word FROM DUAL UNION
SELECT 'Greetings' word FROM DUAL UNION
SELECT 'Dog' word FROM DUAL
) a
LEFT JOIN ALL_WORDS_TABLE t ON t.word = a.word
WHERE
t.word IS NULL
