SQL 排除查询 [英] SQL Exclusion Query

问题描述

是否可以在单个 SQL 语句中执行以下操作:

Is it possible in a single SQL statement to do the following:

在提示中使用电话号码的子集，例如 8001231000-8001239999.然后查询我的数据库中有电话号码，并返回原始子集中哪些电话号码不在我的数据库中?我的数据库是 Oracle 10g.

Use a subset of telephone numbers in a prompt, for example 8001231000-8001239999. Then query my database that has phone numbers in it, and return which phone numbers in the original subset are NOT in my database? My db is Oracle 10g.

基本上不是带回8001231000-8001239999之间的电话号码，我想知道8001231000-8001239999之间的哪些电话号码不在我的数据库中.>

Basically instead of bringing back which phone numbers ARE between 8001231000-8001239999, I want to know which phone numbers between 8001231000-8001239999 are NOT in my database.

推荐答案

假设电话号码是NUMBER，可以生成特定范围内所有电话号码的列表

Assuming that the phone number is a NUMBER, you can generate the list of all phone numbers in a particular range

 SELECT level - 1 + 8001231000
   FROM dual
CONNECT BY level <= 8001239999-8001231000+1

然后，您可以将此范围内所有电话号码的列表加入到您的实际电话号码表中.类似的东西

You can then join this list of all the phone numbers in the range to your actual table of phone numbers. Something like

WITH all_numbers AS (
  SELECT level - 1 + 8001231000 phone_number
    FROM dual
 CONNECT BY level <= 8001239999-8001231000+1
)
SELECT *
  FROM all_numbers a
 WHERE NOT EXISTS(
    SELECT 1
      FROM phone_numbers p
     WHERE a.phone_number = p.phone_number)

这篇关于SQL 排除查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！