SQL - 如何从 where 子句列表中返回不在表中的 ID? [英] SQL - How can I return the IDs from a where clause list that are not in the table?

问题描述

给定这样的查询:

select customerId
  from customer
 where customerId in (
          1, 2, 3
       )

我在 where 子句的列表中有数百个 ID；如何从 where 子句中的列表中返回不在表中的 ID?

I have hundreds of IDs in the where clause's list; how would I return the IDs from the list in the where clause that are not in the table?

这是一个生产表，我只能对其运行 select 查询.我只能运行 select 查询；我没有创建任何表的权限.

This is a production table that I can only run select queries against. I can only run select queries; I don't have permissions to create any tables.

推荐答案

真正残酷的方法，对于大多数 DBMS，这将起作用(Oracle 除外，您需要 select..from双重).即使您无权创建/更新表并且只能SELECT

The really, really brutal approach, for most DBMS, this will work (except Oracle where you need to select..from dual). This should work on DB2 even if you have no access to create/update tables and can only SELECT

select N1.N * 1000 + N2.N * 100 + N3.N * 10 + N4.N as NonExistentCustomerID
from (
    select 1 as N union all select 2 union all select 3 union all
    select 4 union all select 5 union all select 6 union all
    select 7 union all select 8 union all select 9 union all
    select 0) N1
cross join (
    select 1 as N union all select 2 union all select 3 union all
    select 4 union all select 5 union all select 6 union all
    select 7 union all select 8 union all select 9 union all
    select 0) N2
cross join (
    select 1 as N union all select 2 union all select 3 union all
    select 4 union all select 5 union all select 6 union all
    select 7 union all select 8 union all select 9 union all
    select 0) N3
cross join (
    select 1 as N union all select 2 union all select 3 union all
    select 4 union all select 5 union all select 6 union all
    select 7 union all select 8 union all select 9 union all
    select 0) N4
where N1.N * 1000 + N2.N * 100 + N3.N * 10 + N4.N in (1,2,3)
    and not exists (
        select * from customer c where c.customerid = v.number + v2.number*1000)

根据需要扩展子查询以覆盖整个数字范围.

Expand the subqueries as required to cover your full number range.

如果您可以创建表格(或有一个方便的)，请创建一个数字"表格，而不是使用数字 0 到 9(10 条记录)，并继续加入自身，即

If you could create tables (or had one handy), create a "numbers" table instead with the digits 0 to 9 (10 records), and keep joining to itself, i.e.

create table Numbers (N int)
insert into Numbers
select 1 union all select 2 union all select 3 union all
select 4 union all select 5 union all select 6 union all
select 7 union all select 8 union all select 9 union all
select 0

select N1.N * 1000 + N2.N * 100 + N3.N * 10 + N4.N as NonExistentCustomerID
from numbers N1
cross join numbers N2
cross join numbers N3
cross join numbers N4
where N1.N * 1000 + N2.N * 100 + N3.N * 10 + N4.N in (1,2,3)
    and not exists (
        select * from customer c where c.customerid = v.number + v2.number*1000)

numbers 表对于各种查询总是有用的.

A numbers table is always useful for various queries.

供 SQL Server 参考，假设数字在 0-2047 范围内，您可以使用

For reference for SQL Server, assuming the numbers are within the range 0-2047, you can use

select v.number
from master..spt_values v
left join customer c on c.customerid = v.number
where v.type='P' and v.number in (1,2,3)
  and v.customerid is null

如果您需要更大的范围，请继续加入 master..spt_values 以获得更大的范围

If you need a larger range, keep joining to master..spt_values again to get a larger range

select v.number + v2.number*1000 as NonExistentCustomerID
from master..spt_values v
inner join master..spt_values v2 on v2.type='P'
where v.type='P' and v.number + v2.number*1000 in (1,2,3)
  and v.number between 0 and 999
  and not exists (
    select * from customer c where c.customerid = v.number + v2.number*1000)
order by 1

这篇关于SQL - 如何从 where 子句列表中返回不在表中的 ID?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！