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 子句的列表；我如何从不在表中的 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

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)

A 数字表对于各种查询总是有用的

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屋！