LeetCode刷题

2.1 1378.使用唯一标识码替换员工ID

2.1.1 说明
Employees 表：
±--------------±--------+
| Column Name | Type |
±--------------±--------+
| id | int |
| name | varchar |
±--------------±--------+
在 SQL 中，id 是这张表的主键。
这张表的每一行分别代表了某公司其中一位员工的名字和 ID 。
EmployeeUNI 表：
±--------------±--------+
| Column Name | Type |
±--------------±--------+
| id | int |
| unique_id | int |
±--------------±--------+
在 SQL 中，(id, unique_id) 是这张表的主键。
这张表的每一行包含了该公司某位员工的 ID 和他的唯一标识码（unique ID）。

展示每位用户的 唯一标识码（unique ID ）；如果某位员工没有唯一标识码，使用 null 填充即可。
你可以以 任意 顺序返回结果表。
返回结果的格式如下例所示。
示例 1：
输入：
Employees 表:
±—±---------+
| id | name |
±—±---------+
| 1 | Alice |
| 7 | Bob |
| 11 | Meir |
| 90 | Winston |
| 3 | Jonathan |
±—±---------+
EmployeeUNI 表:
±—±----------+
| id | unique_id |
±—±----------+
| 3 | 1 |
| 11 | 2 |
| 90 | 3 |
±—±----------+
输出：
±----------±---------+
| unique_id | name |
±----------±---------+
| null | Alice |
| null | Bob |
| 2 | Meir |
| 3 | Winston |
| 1 | Jonathan |
±----------±---------+
解释：
Alice and Bob 没有唯一标识码, 因此我们使用 null 替代。
Meir 的唯一标识码是 2 。
Winston 的唯一标识码是 3 。
Jonathan 唯一标识码是 1 。
2.1.2 分析
暂时无法在飞书文档外展示此内容
2.1.3 实现
2.1.3.1 准备工作

Create table If Not Exists Employees (id int, name varchar(20))
Create table If Not Exists EmployeeUNI (id int, unique_id int)
Truncate table Employees
insert into Employees (id, name) values ('1', 'Alice')
insert into Employees (id, name) values ('7', 'Bob')
insert into Employees (id, name) values ('11', 'Meir')
insert into Employees (id, name) values ('90', 'Winston')
insert into Employees (id, name) values ('3', 'Jonathan')
Truncate table EmployeeUNI
insert into EmployeeUNI (id, unique_id) values ('3', '1')
insert into EmployeeUNI (id, unique_id) values ('11', '2')
insert into EmployeeUNI (id, unique_id) values ('90', '3')

2.1.3.2 代码实现

select unique_id,
       name 
from employees e1 left join employeeuni e2 on e1.id=e2.id ;

2.1.4 小结

• 左连接

2.2 1384.按年度列出销售总额

2.2.1 说明
Product 表：
±--------------±--------+
| Column Name | Type |
±--------------±--------+
| product_id | int |
| product_name | varchar |
±--------------±--------+
product_id 是这张表的主键(具有唯一值的列)。
product_name 是产品的名称。

Sales 表：
±--------------------±--------+
| Column Name | Type |
±--------------------±--------+
| product_id | int |
| period_start | date |
| period_end | date |
| average_daily_sales | int |
±--------------------±--------+
product_id 是这张表的主键(具有唯一值的列)。
period_start 和 period_end 是该产品销售期的起始日期和结束日期，且这两个日期包含在销售期内。
average_daily_sales 列存储销售期内该产品的日平均销售额。
销售日期范围为2018年到2020年。

编写解决方案，找出每个产品每年的总销售额，并包含 product_id , product_name , report_year 以及 total_amount 。
返回结果并按 product_id 和 report_year 排序。
返回结果格式如下例所示。
示例 1：
输入：
Product table:
±-----------±-------------+
| product_id | product_name |
±-----------±-------------+
| 1 | LC Phone |
| 2 | LC T-Shirt |
| 3 | LC Keychain |
±-----------±-------------+
Sales table:
±-----------±-------------±------------±--------------------+
| product_id | period_start | period_end | average_daily_sales |
±-----------±-------------±------------±--------------------+
| 1 | 2019-01-25 | 2019-02-28 | 100 |
| 2 | 2018-12-01 | 2020-01-01 | 10 |
| 3 | 2019-12-01 | 2020-01-31 | 1 |
±-----------±-------------±------------±--------------------+
输出：
±-----------±-------------±------------±-------------+
| product_id | product_name | report_year | total_amount |
±-----------±-------------±------------±-------------+
| 1 | LC Phone | 2019 | 3500 |
| 2 | LC T-Shirt | 2018 | 310 |
| 2 | LC T-Shirt | 2019 | 3650 |
| 2 | LC T-Shirt | 2020 | 10 |
| 3 | LC Keychain | 2019 | 31 |
| 3 | LC Keychain | 2020 | 31 |
±-----------±-------------±------------±-------------+
解释：
LC Phone 在 2019-01-25 至 2019-02-28 期间销售，该产品销售时间总计35天。销售总额 35100 = 3500。
LC T-shirt 在 2018-12-01 至 2020-01-01 期间销售，该产品在2018年、2019年、2020年的销售时间分别是31天、365天、1天，2018年、2019年、2020年的销售总额分别是31
10=310、365
10=3650、110=10。
LC Keychain 在 2019-12-01 至 2020-01-31 期间销售，该产品在2019年、2020年的销售时间分别是：31天、31天，2019年、2020年的销售总额分别是311=31、311=31。
2.2.2 分析一对应代码实现一：
暂时无法在飞书文档外展示此内容
2.2.3 分析二-最笨的方法
暂时无法在飞书文档外展示此内容
2.2.4 实现
2.2.4.1 准备工作

Create table If Not Exists Product (product_id int, product_name varchar(30))
Create table If Not Exists Sales (product_id int, period_start date, period_end date, average_daily_sales int)
Truncate table Product
insert into Product (product_id, product_name) values ('1', 'LC Phone ')
insert into Product (product_id, product_name) values ('2', 'LC T-Shirt')
insert into Product (product_id, product_name) values ('3', 'LC Keychain')
Truncate table Sales
insert into Sales (product_id, period_start, period_end, average_daily_sales) values ('1', '2019-01-25', '2019-02-28', '100')
insert into Sales (product_id, period_start, period_end, average_daily_sales) values ('2', '2018-12-01', '2020-01-01', '10')
insert into Sales (product_id, period_start, period_end, average_daily_sales) values ('3', '2019-12-01', '2020-01-31', '1')

2.2.4.2 代码实现一：
– 创建临时表 t1，处理销售时间段在同一年的情况

WITH t1 AS (
    SELECT product_id,
           YEAR(period_start) AS year,
           period_start AS start_time,
           period_end AS end_time,
           average_daily_sales
    FROM sales
    WHERE YEAR(period_start) = YEAR(period_end)
),
-- 创建临时表 t2，处理销售时间段跨越两个年份的情况
t2 AS (
    SELECT product_id,
           YEAR(period_start) AS year,
           period_start AS start_time,
           CONCAT(YEAR(period_start), '-12-31') AS end_time,
           average_daily_sales
    FROM sales
    WHERE YEAR(period_start) + 1 = YEAR(period_end)
    UNION ALL
    SELECT product_id,
           YEAR(period_start) + 1 AS year,
           CONCAT(YEAR(period_start) + 1, '-01-01') AS start_time,
           period_end AS end_time,
           average_daily_sales
    FROM sales
    WHERE YEAR(period_start) + 1 = YEAR(period_end)
),
-- 创建临时表 t3，处理销售时间段跨越三个年份的情况
t3 AS (
    SELECT product_id,
           YEAR(period_start) AS year,
           period_start AS start_time,
           CONCAT(YEAR(period_start), '-12-31') AS end_time,
           average_daily_sales
    FROM sales
    WHERE YEAR(period_start) + 2 = YEAR(period_end)
    UNION ALL
    SELECT product_id,
           YEAR(period_start) + 1 AS year,
           CONCAT(YEAR(period_start) + 1, '-01-01') AS start_time,
           CONCAT(YEAR(period_start) + 1, '-12-31') AS end_time,
           average_daily_sales
    FROM sales
    WHERE YEAR(period_start) + 2 = YEAR(period_end)
    UNION ALL
    SELECT product_id,
           YEAR(period_start) + 2 AS year,
           CONCAT(YEAR(period_start) + 2, '-01-01') AS start_time,
           period_end AS end_time,
           average_daily_sales
    FROM sales
    WHERE YEAR(period_start) + 2 = YEAR(period_end)
),
-- 将 t1、t2、t3 联合起来得到 t4
t4 AS (
    SELECT * FROM t1
    UNION ALL
    SELECT * FROM t2
    UNION ALL
    SELECT * FROM t3
)
-- 最终查询结果，连接产品表获取产品名称，计算总销售额并排序
SELECT t4.product_id,
       product.product_name,
       CONCAT('"', year, '"') AS report_year,
       (DATEDIFF(end_time, start_time) + 1) * average_daily_sales AS total_amount
FROM t4
         JOIN product ON t4.product_id = product.product_id
ORDER BY product_id, year;
-- 创建临时表 t1，处理销售时间段在同一年的情况
WITH t1 AS (
    SELECT product_id,
           YEAR(period_start) AS year,
           period_start AS start_time,
           period_end AS end_time,
           average_daily_sales
    FROM sales
    WHERE YEAR(period_start) = YEAR(period_end)
),
-- 创建临时表 t2，处理销售时间段跨越两个年份的情况
t2 AS (
    SELECT product_id,
           YEAR(period_start) AS year,
           period_start AS start_time,
           CONCAT(YEAR(period_start), '-12-31') AS end_time,
           average_daily_sales
    FROM sales
    WHERE YEAR(period_start) + 1 = YEAR(period_end)
    UNION ALL
    SELECT product_id,
           YEAR(period_start) + 1 AS year,
           CONCAT(YEAR(period_start) + 1, '-01-01') AS start_time,
           period_end AS end_time,
           average_daily_sales
    FROM sales
    WHERE YEAR(period_start) + 1 = YEAR(period_end)
),
-- 创建临时表 t3，处理销售时间段跨越三个年份的情况
t3 AS (
    SELECT product_id,
           YEAR(period_start) AS year,
           period_start AS start_time,
           CONCAT(YEAR(period_start), '-12-31') AS end_time,
           average_daily_sales
    FROM sales
    WHERE YEAR(period_start) + 2 = YEAR(period_end)
    UNION ALL
    SELECT product_id,
           YEAR(period_start) + 1 AS year,
           CONCAT(YEAR(period_start) + 1, '-01-01') AS start_time,
           CONCAT(YEAR(period_start) + 1, '-12-31') AS end_time,
           average_daily_sales
    FROM sales
    WHERE YEAR(period_start) + 2 = YEAR(period_end)
    UNION ALL
    SELECT product_id,
           YEAR(period_start) + 2 AS year,
           CONCAT(YEAR(period_start) + 2, '-01-01') AS start_time,
           period_end AS end_time,
           average_daily_sales
    FROM sales
    WHERE YEAR(period_start) + 2 = YEAR(period_end)
),
-- 将 t1、t2、t3 联合起来得到 t4
t4 AS (
    SELECT * FROM t1
    UNION ALL
    SELECT * FROM t2
    UNION ALL
    SELECT * FROM t3
)
-- 最终查询结果，连接产品表获取产品名称，计算总销售额并排序
SELECT t4.product_id,
       product.product_name,
       CONCAT('', year, '') AS report_year,
       (DATEDIFF(end_time, start_time) + 1) * average_daily_sales AS total_amount
FROM t4
         JOIN product ON t4.product_id = product.product_id
ORDER BY product_id, year;
;

2.2.4.3 代码实现二

with t1 as (
    select product_id, period_start, period_end,
    year(period_start) as year_start,
    year(period_end) as year_end,
    average_daily_sales
    from Sales
),
t2 as (
#--同年情况
    select product_id, year_start as report_year,
    (timestampdiff(day, period_start,period_end)+1)*average_daily_sales as total_amount
    from t1
    where year_start = year_end
),
t3 as (
--  2019-2020
    select product_id, '2019' as report_year, (timestampdiff(day, period_start,'2019-12-31')+1)*average_daily_sales as total_amount
    from t1
    where year_start = '2019' and year_end = '2020'
    union all
    select product_id, '2020' as report_year, (timestampdiff(day, '2020-01-01', period_end)+1)*average_daily_sales as total_amount
    from t1
    where year_start = '2019' and year_end = '2020'
),
t4 as (
 -- 2018-2020
    select product_id, '2018' as report_year, (timestampdiff(day, period_start,'2018-12-31')+1)*average_daily_sales as total_amount
    from t1
    where year_start = '2018' and year_end = '2020'
    union all
    select product_id, '2019' as report_year, 365*average_daily_sales as total_amount
    from t1
    where year_start = '2018' and year_end = '2020'
    union all
    select product_id, '2020' as report_year, (timestampdiff(day, '2020-01-01',period_end)+1)*average_daily_sales as total_amount
    from t1
    where year_start = '2018' and year_end = '2020'
),
t5 as (
#--2018-2019
    select product_id, '2018' as report_year, (timestampdiff(day, period_start, '2018-12-31')+1)*average_daily_sales as total_amount
    from t1
    where year_start = '2018' and year_end = '2019'
    union all
    select product_id, '2019' as report_year, (timestampdiff(day, '2019-01-01', period_end)+1)*average_daily_sales as total_amount
    from t1
    where year_start = '2018' and year_end = '2019'
),t6 as (
#--把所有的情况进行合并
    select *from t2
    union all
    select *from t3
    union all
    select *from t4
    union all
    select *from t5
)
select t6.product_id,
       product.product_name,
       report_year,
       total_amount
from t6 join product on t6.product_id=product.product_id;

2.2.5 小结

• Union all
• timestampdiff(参数可以是day month year，日期1，日期2)
• With 临时表

2.3 1393.股票的资本损益

2.3.1 说明
Stocks 表：
±--------------±--------+
| Column Name | Type |
±--------------±--------+
| stock_name | varchar |
| operation | enum |
| operation_day | int |
| price | int |
±--------------±--------+
(stock_name, operation_day) 是这张表的主键(具有唯一值的列的组合)
operation 列使用的是一种枚举类型，包括：(‘Sell’,‘Buy’)
此表的每一行代表了名为 stock_name 的某支股票在 operation_day 这一天的操作价格。
此表可以保证，股票的每个“卖出”操作在前一天都有相应的“买入”操作。并且，股票的每个“买入”操作在即将到来的一天都有相应的“卖出”操作。

编写解决方案报告每只股票的 资本损益。
股票的 资本利得/损失 是指一次或多次买卖该股票后的总收益或损失。
以 任意顺序 返回结果表。
结果格式如下所示。

示例 1：
输入：
Stocks 表:
±--------------±----------±--------------±-------+
| stock_name | operation | operation_day | price |
±--------------±----------±--------------±-------+
| Leetcode | Buy | 1 | 1000 |
| Corona Masks | Buy | 2 | 10 |
| Leetcode | Sell | 5 | 9000 |
| Handbags | Buy | 17 | 30000 |
| Corona Masks | Sell | 3 | 1010 |
| Corona Masks | Buy | 4 | 1000 |
| Corona Masks | Sell | 5 | 500 |
| Corona Masks | Buy | 6 | 1000 |
| Handbags | Sell | 29 | 7000 |
| Corona Masks | Sell | 10 | 10000 |
±--------------±----------±--------------±-------+
输出：
±--------------±------------------+
| stock_name | capital_gain_loss |
±--------------±------------------+
| Corona Masks | 9500 |
| Leetcode | 8000 |
| Handbags | -23000 |
±--------------±------------------+
解释：
Leetcode 股票在第一天以1000美元的价格买入，在第五天以9000美元的价格卖出。资本收益=9000-1000=8000美元。
Handbags 股票在第17天以30000美元的价格买入，在第29天以7000美元的价格卖出。资本损失=7000-30000=-23000美元。
Corona Masks 股票在第1天以10美元的价格买入，在第3天以1010美元的价格卖出。在第4天以1000美元的价格再次购买，在第5天以500美元的价格出售。最后，它在第6天以1000美元的价格被买走，在第10天以10000美元的价格被卖掉。资本损益是每次（’Buy’->‘Sell’）操作资本收益或损失的和=（1010-10）+（500-1000）+（10000-1000）=1000-500+9000=9500美元。
2.3.2 分析
暂时无法在飞书文档外展示此内容
2.3.3 实现
2.3.3.1 准备工作

Create Table If Not Exists Stocks (stock_name varchar(15), operation ENUM('Sell', 'Buy'), operation_day int, price int)
Truncate table Stocks
insert into Stocks (stock_name, operation, operation_day, price) values ('Leetcode', 'Buy', '1', '1000')
insert into Stocks (stock_name, operation, operation_day, price) values ('Corona Masks', 'Buy', '2', '10')
insert into Stocks (stock_name, operation, operation_day, price) values ('Leetcode', 'Sell', '5', '9000')
insert into Stocks (stock_name, operation, operation_day, price) values ('Handbags', 'Buy', '17', '30000')
insert into Stocks (stock_name, operation, operation_day, price) values ('Corona Masks', 'Sell', '3', '1010')
insert into Stocks (stock_name, operation, operation_day, price) values ('Corona Masks', 'Buy', '4', '1000')
insert into Stocks (stock_name, operation, operation_day, price) values ('Corona Masks', 'Sell', '5', '500')
insert into Stocks (stock_name, operation, operation_day, price) values ('Corona Masks', 'Buy', '6', '1000')
insert into Stocks (stock_name, operation, operation_day, price) values ('Handbags', 'Sell', '29', '7000')
insert into Stocks (stock_name, operation, operation_day, price) values ('Corona Masks', 'Sell', '10', '10000')

2.3.3.2 代码实现

with t1 as (
    select *,
       if(operation='Buy',-price,price) gain_loss
       FROM stocks
)select
     stock_name,
     sum(gain_loss) capital_gain_loss
     FROM t1
group by stock_name
;

2.3.4 小结

• if()判断
• With t1 as（）把判断的值放入临时表
• Group by 分组
• sum() 聚合函数
  2.4 1398.购买了产品 A 和产品 B 却没有购买产品 C 的顾客
  2.4.1 说明
  Customers 表：
  ±--------------------±--------+
  | Column Name | Type |
  ±--------------------±--------+
  | customer_id | int |
  | customer_name | varchar |
  ±--------------------±--------+
  customer_id 是这张表中具有唯一值的列。
  customer_name 是顾客的名称。

Orders 表：
±--------------±--------+
| Column Name | Type |
±--------------±--------+
| order_id | int |
| customer_id | int |
| product_name | varchar |
±--------------±--------+
order_id 是这张表中具有唯一值的列。
customer_id 是购买了名为 “product_name” 产品顾客的id。

请你编写解决方案，报告购买了产品 “A”，“B” 但没有购买产品 “C” 的客户的 customer_id 和 customer_name，因为我们想推荐他们购买这样的产品。
返回按 customer_id 排序 的结果表。
返回结果格式如下所示。
示例 1：
输入：
Customers table:
±------------±--------------+
| customer_id | customer_name |
±------------±--------------+
| 1 | Daniel |
| 2 | Diana |
| 3 | Elizabeth |
| 4 | Jhon |
±------------±--------------+
Orders table:
±-----------±-------------±--------------+
| order_id | customer_id | product_name |
±-----------±-------------±--------------+
| 10 | 1 | A |
| 20 | 1 | B |
| 30 | 1 | D |
| 40 | 1 | C |
| 50 | 2 | A |
| 60 | 3 | A |
| 70 | 3 | B |
| 80 | 3 | D |
| 90 | 4 | C |
±-----------±-------------±--------------+
输出：
±------------±--------------+
| customer_id | customer_name |
±------------±--------------+
| 3 | Elizabeth |
±------------±--------------+
解释：
只有 customer_id 为 3 的顾客购买了产品 A 和产品 B ，却没有购买产品 C 。
2.4.2 分析
暂时无法在飞书文档外展示此内容
2.4.3 实现
2.4.3.1 准备工作

Create table If Not Exists Customers (customer_id int, customer_name varchar(30))
Create table If Not Exists Orders (order_id int, customer_id int, product_name varchar(30))
Truncate table Customers
insert into Customers (customer_id, customer_name) values ('1', 'Daniel')
insert into Customers (customer_id, customer_name) values ('2', 'Diana')
insert into Customers (customer_id, customer_name) values ('3', 'Elizabeth')
insert into Customers (customer_id, customer_name) values ('4', 'Jhon')
Truncate table Orders
insert into Orders (order_id, customer_id, product_name) values ('10', '1', 'A')
insert into Orders (order_id, customer_id, product_name) values ('20', '1', 'B')
insert into Orders (order_id, customer_id, product_name) values ('30', '1', 'D')
insert into Orders (order_id, customer_id, product_name) values ('40', '1', 'C')
insert into Orders (order_id, customer_id, product_name) values ('50', '2', 'A')
insert into Orders (order_id, customer_id, product_name) values ('60', '3', 'A')
insert into Orders (order_id, customer_id, product_name) values ('70', '3', 'B')
insert into Orders (order_id, customer_id, product_name) values ('80', '3', 'D')
insert into Orders (order_id, customer_id, product_name) values ('90', '4', 'C')

2.4.3.2 代码实现

select *FROM customers where customer_id in (
    with t1 as (
    select distinct customer_id,product_name,
                case when product_name in('A','B') then 1
                    when product_name ='C' then 999
                    else 0
                end  as xxx
from orders
)
select customer_id
from t1
group by customer_id
having sum(xxx)=2
    );
    ```
2.4.4 小结
- 子查询
- distinct去重函数
- With 临时表
- Case when then else end条件判断
- Group by 分组函数
- Having 聚合函数判断
2.5 1407.排名靠前的旅行者
2.5.1 说明
表：Users
+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| id            | int     |
| name          | varchar |
+---------------+---------+
id 是该表中具有唯一值的列。
name 是用户名字。
 
表：Rides
+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| id            | int     |
| user_id       | int     |
| distance      | int     |
+---------------+---------+
id 是该表中具有唯一值的列。
user_id 是本次行程的用户的 id, 而该用户此次行程距离为 distance 。
 
编写解决方案，报告每个用户的旅行距离。
返回的结果表单，以 travelled_distance 降序排列 ，如果有两个或者更多的用户旅行了相同的距离, 那么再以 name 升序排列 。
返回结果格式如下例所示。
示例 1：
输入：
Users 表：
+------+-----------+
| id   | name      |
+------+-----------+
| 1    | Alice     |
| 2    | Bob       |
| 3    | Alex      |
| 4    | Donald    |
| 7    | Lee       |
| 13   | Jonathan  |
| 19   | Elvis     |
+------+-----------+
Rides 表：
+------+----------+----------+
| id   | user_id  | distance |
+------+----------+----------+
| 1    | 1        | 120      |
| 2    | 2        | 317      |
| 3    | 3        | 222      |
| 4    | 7        | 100      |
| 5    | 13       | 312      |
| 6    | 19       | 50       |
| 7    | 7        | 120      |
| 8    | 19       | 400      |
| 9    | 7        | 230      |
+------+----------+----------+
输出：
+----------+--------------------+
| name     | travelled_distance |
+----------+--------------------+
| Elvis    | 450                |
| Lee      | 450                |
| Bob      | 317                |
| Jonathan | 312                |
| Alex     | 222                |
| Alice    | 120                |
| Donald   | 0                  |
+----------+--------------------+
解释：
Elvis 和 Lee 旅行了 450 英里，Elvis 是排名靠前的旅行者，因为他的名字在字母表上的排序比 Lee 更小。
Bob, Jonathan, Alex 和 Alice 只有一次行程，我们只按此次行程的全部距离对他们排序。
Donald 没有任何行程, 他的旅行距离为 0。
2.5.2 分析
暂时无法在飞书文档外展示此内容
2.5.3 实现
2.5.3.1 准备工作
```sql
Create Table If Not Exists Users (id int, name varchar(30))
Create Table If Not Exists Rides (id int, user_id int, distance int)
Truncate table Users
insert into Users (id, name) values ('1', 'Alice')
insert into Users (id, name) values ('2', 'Bob')
insert into Users (id, name) values ('3', 'Alex')
insert into Users (id, name) values ('4', 'Donald')
insert into Users (id, name) values ('7', 'Lee')
insert into Users (id, name) values ('13', 'Jonathan')
insert into Users (id, name) values ('19', 'Elvis')
Truncate table Rides
insert into Rides (id, user_id, distance) values ('1', '1', '120')
insert into Rides (id, user_id, distance) values ('2', '2', '317')
insert into Rides (id, user_id, distance) values ('3', '3', '222')
insert into Rides (id, user_id, distance) values ('4', '7', '100')
insert into Rides (id, user_id, distance) values ('5', '13', '312')
insert into Rides (id, user_id, distance) values ('6', '19', '50')
insert into Rides (id, user_id, distance) values ('7', '7', '120')
insert into Rides (id, user_id, distance) values ('8', '19', '400')
insert into Rides (id, user_id, distance) values ('9', '7', '230')

2.5.3.2 代码实现

with t1 as (
    select user_id,
       sum(distance) as td
FROM rides
group by user_id
)
select name,
       if(td is not null ,td,0) as travelled_distance
from users left join t1 on users.id=t1.user_id
order by travelled_distance desc , name
;

2.5.4 小结

• 左连接 left join 左边的表全部保留，右边关联上的返回，没右关联上的置为null
• Group by
• Sum ()
• Order by --desc降序 asc为升序默认为升序
  2.6 1412.查询成绩处于中游的学生
  2.6.1 说明
  表: Student
  ±--------------------±--------+
  | Column Name | Type |
  ±--------------------±--------+
  | student_id | int |
  | student_name | varchar |
  ±--------------------±--------+
  student_id 是该表主键(具有唯一值的列)。
  student_name 学生名字。

表: Exam
±--------------±--------+
| Column Name | Type |
±--------------±--------+
| exam_id | int |
| student_id | int |
| score | int |
±--------------±--------+
(exam_id, student_id) 是该表主键(具有唯一值的列的组合)。
学生 student_id 在测验 exam_id 中得分为 score。

成绩处于中游的学生是指至少参加了一次测验, 且得分既不是最高分也不是最低分的学生。
编写解决方案，找出在 所有 测验中都处于中游的学生 (student_id, student_name)。不要返回从来没有参加过测验的学生。
返回结果表按照 student_id 排序。
返回结果格式如下。

示例 1：
输入：
Student 表：
±------------±--------------+
| student_id | student_name |
±------------±--------------+
| 1 | Daniel |
| 2 | Jade |
| 3 | Stella |
| 4 | Jonathan |
| 5 | Will |
±------------±--------------+
Exam 表：
±-----------±-------------±----------+
| exam_id | student_id | score |
±-----------±-------------±----------+
| 10 | 1 | 70 |
| 10 | 2 | 80 |
| 10 | 3 | 90 |
| 20 | 1 | 80 |
| 30 | 1 | 70 |
| 30 | 3 | 80 |
| 30 | 4 | 90 |
| 40 | 1 | 60 |
| 40 | 2 | 70 |
| 40 | 4 | 80 |
±-----------±-------------±----------+
输出：
±------------±--------------+
| student_id | student_name |
±------------±--------------+
| 2 | Jade |
±------------±--------------+
解释：
对于测验 1: 学生 1 和 3 分别获得了最低分和最高分。
对于测验 2: 学生 1 既获得了最高分, 也获得了最低分。
对于测验 3 和 4: 学生 1 和 4 分别获得了最低分和最高分。
学生 2 和 5 没有在任一场测验中获得了最高分或者最低分。
因为学生 5 从来没有参加过任何测验, 所以他被排除于结果表。
由此, 我们仅仅返回学生 2 的信息。
2.6.2 分析
暂时无法在飞书文档外展示此内容
2.6.3 实现
2.6.3.1 准备工作

Create table If Not Exists Student (student_id int, student_name varchar(30))
Create table If Not Exists Exam (exam_id int, student_id int, score int)
insert into Student (student_id, student_name) values ('1', 'Daniel')
insert into Student (student_id, student_name) values ('2', 'Jade')
insert into Student (student_id, student_name) values ('3', 'Stella')
insert into Student (student_id, student_name) values ('4', 'Jonathan')
insert into Student (student_id, student_name) values ('5', 'Will')
insert into Exam (exam_id, student_id, score) values ('10', '1', '70')
insert into Exam (exam_id, student_id, score) values ('10', '2', '80')
insert into Exam (exam_id, student_id, score) values ('10', '3', '90')
insert into Exam (exam_id, student_id, score) values ('20', '1', '80')
insert into Exam (exam_id, student_id, score) values ('30', '1', '70')
insert into Exam (exam_id, student_id, score) values ('30', '3', '80')
insert into Exam (exam_id, student_id, score) values ('30', '4', '90')
insert into Exam (exam_id, student_id, score) values ('40', '1', '60')
insert into Exam (exam_id, student_id, score) values ('40', '2', '70')
insert into Exam (exam_id, student_id, score) values ('40', '4', '80')

2.6.3.2 代码实现

with t1 as (
#t1--得到每次测试的最高分和最低分
    select *,
       min(score)over(partition by exam_id) as min_score,
       max(score)over(partition by exam_id) as max_score
FROM exam
),t2 as (
#t2--得到每次测试不等于最高分和最低分的学生即每次考试的中游学生，并去重
    select distinct student_id
    FROM t1
    where score !=min_score and score!=max_score
),t3 as (
#t3--得到每次测试最高分和最低分的学生，并进行去重。
    select distinct student_id
    FROM t1
    where score=min_score or score=max_score
)
#--通过where条件得到每次测试都处于中游的学生。最后进行关联学生表得到姓名
select t2.student_id,
       student.student_name
FROM t2 join student on t2.student_id =student.student_id
where t2.student_id not in ( select *from t3 )

2.6.4 小结

• 开窗函数 partition by
• With 临时表
• Join 内连接
• 子查询