1613. 找到遗失的ID - 力扣（LeetCode）

1. 1613. 找到遗失的ID - 力扣（LeetCode）

2. 目标

   1. 输入

      表: Customers
      customer_id	customer_name
      1	Alice
      4	Bob
      5	Charlie

   2. 输出

      输出

      IDS

      2

      3

3. 分析

   编写一个解决方案, 找到所有遗失的顾客 id。遗失的顾客 id 是指那些不在 Customers 表中, 值却处于 1 和表中 最大 customer_id 之间的 id. 注意: 最大的 customer_id 值不会超过 100. 返回结果按 ids 升序 排列
   																			表: Customers			输出
   																			customer_id	customer_name		IDS
   																			1	Alice		2
   4	Bob		3
   5	Charlie
   递归1到最大id		IDS	筛选ids		IDS
   		1			2
   		2			3
   		3
   		4
   		5

4. 实现

   DROP TABLE IF EXISTS Customers;
   Create table If Not Exists Customers (customer_id int, customer_name varchar(20));
   Truncate table Customers;
   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');
   SELECT * FROM Customers;
   # 递归1到最大id
   WITH RECURSIVE T1(ids) AS (
   SELECT 1 ids
   FROM dual
   UNION ALL
   SELECT  ids + 1
   FROM T1
   WHERE ids<(SELECT MAX(customer_id)FROM Customers)
    )SELECT * FROM T1;
   #筛选ids
   WITH RECURSIVE T1(ids) AS (
   SELECT 1 ids
   FROM dual
   UNION ALL
   SELECT  ids + 1
   FROM T1
   WHERE ids<(SELECT MAX(customer_id)FROM Customers)
    )SELECT DISTINCT ids FROM T1
    WHERE ids NOT IN (SELECT (customer_id)FROM Customers)
    ORDER BY ids

5. 小结

        递归,筛选