sql - 单个查询以返回不存在的值 [英] sql - single query to return values that are not present

问题描述

例如，您有一个只有一列的简单表。
ie。

  CREATE TABLE movies（title VARCHAR2（255 BYTE））
 
  
 > INSERT INTO movies（title）VALUES（'Scream'）; 
 INSERT INTO电影（标题）VALUES（'Blair Witch'）; 
 INSERT INTO电影（标题）VALUES（'Friday the 13th'）; 
 INSERT INTO电影（标题）VALUES（'Scary Movie'）; 
 INSERT INTO电影（标题）VALUES（'Hide and Seek'）; 
 INSERT INTO电影（标题）VALUES（'Alien vs Predator'）; 
 
是否有单个查询或PL / SQL将动态执行以下动作（即无需手动
 
 
 
显然这个查询是错误的，但你得到的想法：
 从电影中选择* bb $ b在其中标题（
'Scream'，
'Scary Movie'，
'Exorcist'，
'Dracula'，
'Saw'，
'隐藏和寻找'
）
  
期望的结果是WHERE TITLE IN子句中的每个值的记录，其中记录不在表中。 
 ie。
 'Exorcist'
'Dracula'
'Saw'
  
 
 
解决方案
如果您使用的是10g或更高版本，将CSV字符串转换为动态表。请在此其他响应中查看字符串令牌器的代码。
 
 
 
您可以这样使用：
  select * from movies 
其中title不在（
 select * 
 from table（string_tokenizer 
（
'Scream，Scary Movie，Exorcist，Dracula，Saw，Hide和Seek'
）
 
）
）
 / 
  
 
 b $ b 
 
 
 
这里是一个稍微简单的实现，不需要任何额外的基础设施：
  SQL> select * from table（sys.dbms_debug_vc2coll（'Scream'，
'Scary Movie'，
'Exorcist'，
'Dracula'，
'Saw'，
 'hide and Seek'
））
 / 
 2 3 4 5 6 7 8 
 COLUMN_VALUE 
 -------------- -------------------------------------------------- ---------------- 
尖叫
恐怖电影
驱魔师
 Dracula 
 Saw 
隐藏和寻找
 
选择6行。 
 
 SQL> 
  
这类似于表值构造函数，但它只适用于单列表。
 
For example, you have a simple table with just one column.
ie.
CREATE TABLE movies   (title VARCHAR2(255 BYTE))
set up with the following data:
INSERT INTO movies   (title) VALUES ('Scream');
INSERT INTO movies   (title) VALUES ('Blair Witch');
INSERT INTO movies   (title) VALUES ('Friday the 13th');
INSERT INTO movies   (title) VALUES ('Scary Movie');
INSERT INTO movies   (title) VALUES ('Hide and Seek');
INSERT INTO movies   (title) VALUES ('Alien vs Predator');
Is there a single query or PL/SQL that will do the following dynamically (ie without having to manually do a "UNION select 'scream' from dual..." for every value)?

Obviously this query is wrong but you get the idea:
Select * from movies
where title in (
'Scream',
'Scary Movie',
'Exorcist',
'Dracula',
'Saw',
'Hide and Seek'
)
Desired result being a record for every value in the "WHERE TITLE IN" clause where the record is not present in the table.
ie.
'Exorcist'
'Dracula'
'Saw'

解决方案
If you're using 10g or higher, you can build a function which converts a CSV string into a  dynamic table.  Check out the code for a string tokenizer in this other response.

You would use it like this:
select * from movies
where title NOT in (
         select * 
          from table (string_tokenizer
                      (
                          'Scream, Scary Movie,Exorcist,Dracula,Saw,Hide and Seek'
                        )

                  )
     )
/




Here is a slightly simpler implementation which doesn't require any additional infrastructure: 
SQL> select * from table(sys.dbms_debug_vc2coll('Scream',
'Scary Movie',
'Exorcist',
'Dracula',
'Saw',
'Hide and Seek'
 ))
/
  2    3    4    5    6    7    8  
COLUMN_VALUE
--------------------------------------------------------------------------------
Scream
Scary Movie
Exorcist
Dracula
Saw
Hide and Seek

6 rows selected.

SQL> 
This is similar to the Table Value Constructor, but it does only work for single column "tables".

                        
这篇关于sql  - 单个查询以返回不存在的值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！