单挑力扣（LeetCode）SQL题：1613. 找到遗失的ID（难度：中等）

表: Customers
+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| customer_id   | int     |
| customer_name | varchar |
+---------------+---------+
customer_id 是该表主键.
该表第一行包含了顾客的名字和id.


写一个 SQL 语句,找到所有遗失的顾客id.遗失的顾客id是指那些不在Customers表中,值却处于1和表中最大customer_id之间的id.
注意:最大的customer_id值不会超过100.
返回结果按ids 升序排列
查询结果格式如下例所示。


示例 1:
输入：
Customers 表:
+-------------+---------------+
| customer_id | customer_name |
+-------------+---------------+
| 1           | Alice         |
| 4           | Bob           |
| 5           | Charlie       |
+-------------+---------------+
输出：
+-----+
| ids |
+-----+
| 2   |
| 3   |
+-----+
解释：
表中最大的customer_id是5, 所以在范围[1,5]内, ID2和3从表中遗失.


来源：力扣（LeetCode）
链接：https://leetcode.cn/problems/find-the-missing-ids

#测试数据
Create table If Not Exists Customers (customer_id int, customer_name varchar(20));


insert into Customers (customer_id, customer_name) values ('1', 'Alice');
insert into Customers (customer_id, customer_name) values ('4', 'Bob');
insert into Customers (customer_id, customer_name) values ('5', 'Charlie');

#方法一
with recursive tmp as (
    select 1 as customer_id 
     union all 
    select customer_id+1 from tmp where customer_id < (select max(customer_id) from Customers)
)
select * from tmp;


#方法二
with recursive tmp(n) as (
    select 1 as customer_id
    union all
    select n+1 from tmp where n<(select max(customer_id) from Customers)
)
select * from tmp;

#方法一
with tmp(customer_id) as (
    select 1 as customer_id from dual
    union all
    select customer_id + 1 from tmp where customer_id < (select max(customer_id) from customers)
)
select * from tmp;


#方法二
select rownum as customer_id 
from dual 
connect by rownum <= (select max(customer_id) from customers);

with recursive tmp as (
    select 1 as customer_id 
     union all 
    select customer_id+1 from tmp where customer_id < (select max(customer_id) from Customers)
)
select 
    customer_id as ids
from tmp
where customer_id not in (select customer_id from Customers);