列表

详情


1613. 找到遗失的ID

表: Customers

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

 

编写一个解决方案, 找到所有遗失的顾客 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 从表中遗失.

原站题解

去查看

上次编辑到这里,代码来自缓存 点击恢复默认模板
# Write your MySQL query statement below

mysql 解法, 执行用时: 252 ms, 内存消耗: 0 B, 提交时间: 2023-10-15 19:52:19

# Write your MySQL query statement below
with recursive tmp as ( 
    select 1 as n union all 
    select n + 1 from tmp where n <= 100 )
select n as ids from tmp
where n not in ( select customer_id from Customers )
and n <=( select max(customer_id) from Customers );

上一题