问题:
写一个 SQL 语句, 找到所有遗失的顾客id. 遗失的顾客id是指那些不在 Customers 表中, 值却处于 1 和表中最大 customer_id 之间的id.
表: Customers
+---------------+---------+
| Column Name | Type |
+---------------+---------+
| customer_id | int |
| customer_name | varchar |
+---------------+---------+
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从表中遗失.
drop table if EXISTS Customers;
Create table If Not Exists Customers (customer_id int, customer_name varchar(255));
insert into Customers values (1,'Alice');
insert into Customers values (4,'Bob');
insert into Customers values (5,'Charlie');
select * from Customers;
with recursive t as
(select 1 as i
union all
select i+1 from t where i<100
)
-- select * from t;
select i as ids
from t
where i<=(select max(customer_id) from customers)
and i not in (select customer_id from customers)