*对于大多数DBMS来说，这 *确实是一种非常残酷的方法 （除了需要使用Oracle之外，这是可行的
select..fromdual）。即使您无权访问创建/更新表， 并且只能 访问DB2，这也应该适用于DB2
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
