SQL-如何从不在表中的where子句列表返回ID？

*对于大多数DBMS来说，这 *确实是一种非常残酷的方法 （除了需要使用Oracle之外，这是可行的

select..fromdual

SELECt

select N1.N * 1000 + N2.N * 100 + N3.N * 10 + N4.N as NonExistentCustomerIDfrom (    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) N1cross 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) N2cross 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) N3cross 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) N4where 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)

根据需要展开子查询以覆盖您的整个号码范围。

如果您可以创建表 （或有一个方便的 表 ），请创建一个“数字”表，而不要使用数字0到9（10条记录），并保持自身连接

create table Numbers (N int)insert into Numbersselect 1 union all select 2 union all select 3 union allselect 4 union all select 5 union all select 6 union allselect 7 union all select 8 union all select 9 union allselect 0select N1.N * 1000 + N2.N * 100 + N3.N * 10 + N4.N as NonExistentCustomerIDfrom numbers N1cross join numbers N2cross join numbers N3cross join numbers N4where 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

作为SQL Server的参考 ，假设数字在0-2047范围内，则可以使用

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

如果需要更大的范围，请继续连接到master..spt_values以获取更大的范围

select v.number + v2.number*1000 as NonExistentCustomerIDfrom master..spt_values vinner 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