Leetcode力扣 MySQL数据库 1613 找到遗失的ID

1613 找到遗失的ID

SQL架构

Create table If Not Exists Customers_1613 (customer_id int, customer_name varchar(20));
Truncate table Customers_1613;
insert into Customers_1613 (customer_id, customer_name) values ('1', 'Alice');
insert into Customers_1613 (customer_id, customer_name) values ('4', 'Bob');
insert into Customers_1613 (customer_id, customer_name) values ('5', 'Charlie');

表: 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从表中遗失.
 

解题

with t1 as (
    select 1 as a
    union all select 2
    union all select 3
    union all select 4
    union all select 5
    union all select 6
    union all select 7
    union all select 8
    union all select 9
),
t2 as (
    select 0 as b
    union all select 1
    union all select 2
    union all select 3
    union all select 4
    union all select 5
    union all select 6
    union all select 7
    union all select 8
    union all select 9
),
t3 as (
    select 10*a + 1*b as numbers from t1, t2
    union all select 100
    union all select a from t1
        
        

)
select numbers as 'ids' from t3
where numbers < (select max(customer_id) from customers_1613)
and numbers not in (select customer_id from customers_1613)
order by numbers asc;