> For the complete documentation index, see [llms.txt](https://zqt0.gitbook.io/leetcode/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://zqt0.gitbook.io/leetcode/sql/1613.find-the-missing-ids.md).

# 1613.(Medium)找到遗失的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 升序排列

查询结果格式如下例所示.

```
Customers 表:
+-------------+---------------+
| customer_id | customer_name |
+-------------+---------------+
| 1           | Alice         |
| 4           | Bob           |
| 5           | Charlie       |
+-------------+---------------+

Result 表:
+-----+
| ids |
+-----+
| 2   |
| 3   |
+-----+
表中最大的customer_id是5, 所以在范围[1,5]内, ID2和3从表中遗失.
```

来源：力扣（LeetCode）

链接：<https://leetcode-cn.com/problems/find-the-missing-ids>

著作权归领扣网络所有。商业转载请联系官方授权，非商业转载请注明出处。

## Solution

`with recursive` 递归建立 1 到 n=(max id) 的自然数列

```sql
with recursive t as (
    select 1 as ids
    union
    select ids+1 from t where ids<(select max(customer_id) from Customers)
)

select ids from t
where ids not in (select customer_id from Customers)
```