【力扣 | SQL题 | 每日四题】力扣1875， 2238， 612， 618

1. 力扣1875：将工资相同的雇员分组

1.1 题目：

表: Employees

+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| employee_id | int     |
| name        | varchar |
| salary      | int     |
+-------------+---------+
employee_id 是这张表具有唯一值的列.
这个表格的每一行包含雇员 ID, 姓名和工资信息.

这家公司想要将 工资相同 的雇员划分到同一个组中。每个组需要满足如下要求：

• 每个组需要由 至少两个 雇员组成。
• 同一个组中的所有雇员的 工资相同。
• 工资相同的所有雇员必须被分到同一个组中。
• 如果某位雇员的工资是独一无二的，那么它 不 被分配到任何一个组中。
• 组ID的设定基于这个组的工资相对于其他组的 工资的排名，即工资 最低 的组满足 team_id = 1 。注意，排名时 不需要考虑 没有组的雇员的工资。

编写一个解决方案来获取每一个被分配到组中的雇员的 team_id 。

返回的结果表按照 team_id 升序排列。如果相同，则按照 employee_id 升序排列。

返回结果格式如下示例所示。

示例 1：

输入：
Employees 表:
+-------------+---------+--------+
| employee_id | name    | salary |
+-------------+---------+--------+
| 2           | Meir    | 3000   |
| 3           | Michael | 3000   |
| 7           | Addilyn | 7400   |
| 8           | Juan    | 6100   |
| 9           | Kannon  | 7400   |
+-------------+---------+--------+
输出：
+-------------+---------+--------+---------+
| employee_id | name    | salary | team_id |
+-------------+---------+--------+---------+
| 2           | Meir    | 3000   | 1       |
| 3           | Michael | 3000   | 1       |
| 7           | Addilyn | 7400   | 2       |
| 9           | Kannon  | 7400   | 2       |
+-------------+---------+--------+---------+
解释：
Meir (employee_id=2) 和 Michael (employee_id=3) 在同一个组中，因为他们的工资都是3000。
Addilyn (employee_id=7) 和 Kannon (employee_id=9) 在同一个组中，因为他们的工资都是7400。
Juan (employee_id=8) 不在任何一个组中，因为他的工资为6100，是独一无二的（即：没有人和他的工资相同）。
组ID按照如下方式分配（基于工资排名，较低的排在前面）:
- team_id=1: Meir 和 Michael, 工资是3000
- team_id=2: Addilyn 和 Kannon, 工资是7400
Juan的工资(6100)没有被计算在排名中，因为他不属于任何一个组。

1.2 思路：

看注释。

1.3 题解：

-- 排名不需要考虑没有组的雇员
with tep as (
    select *
    from Employees e1
    where salary in (select salary from Employees e2 where e1.name <> e2.name)
), temp as (
    -- 然后给每个薪水分配一个id
    select rank() over (order by salary) team_id, salary 
    from tep 
    group by salary 
), tp as (
    -- 然后给每个employee_id分配一个id
    select employee_id, name, t1.salary, team_id
    from tep t1 join temp t2
    on t1.salary = t2.salary
)
-- 然后根据字段排序
select *
from tp 
order by team_id , employee_id 

2. 力扣2238：司机成为乘客的次数

2.1 题目：

表: Rides

+--------------+------+
| Column Name  | Type |
+--------------+------+
| ride_id      | int  |
| driver_id    | int  |
| passenger_id | int  |
+--------------+------+
ride_id 是该表的主键（具有唯一值的列）。
该表的每一行都包含驾驶员的 ID 和在 ride_id 中乘车的乘客的 ID。
注意 driver_id != passenger_id。

编写解决方案，获取每个司机的 ID 和他们作为乘客的次数。

以 任意顺序 返回结果表。

结果格式如下所示。

示例 1:

输入: 
Rides 表:
+---------+-----------+--------------+
| ride_id | driver_id | passenger_id |
+---------+-----------+--------------+
| 1       | 7         | 1            |
| 2       | 7         | 2            |
| 3       | 11        | 1            |
| 4       | 11        | 7            |
| 5       | 11        | 7            |
| 6       | 11        | 3            |
+---------+-----------+--------------+
输出: 
+-----------+-----+
| driver_id | cnt |
+-----------+-----+
| 7         | 2   |
| 11        | 0   |
+-----------+-----+
解释: 
在所有给定的行程中有两名司机: 7 和 11.
ID = 7 的司机曾两次成为乘客。
ID = 11 的司机从来不是乘客。

2.2 思路：

分为两种情况，找到所有司机，然后做过乘客，就计算次数，否则给个0.

2.3 题解：

-- 先找到司机有哪些人
with dirver as (
    select distinct driver_id
    from Rides 
), tep as (
    -- 找到哪些司机做过乘客
    select passenger_id 
    from Rides 
    where passenger_id in (select * from dirver)
)
-- 分为两种情况
-- 如果这个司机做过乘客，就计算他的次数
-- 如果没做过乘客的话，就直接给个0
select passenger_id driver_id, count(*) cnt 
from tep
group by passenger_id
union all
select driver_id, (select 0) cnt
from dirver 
where driver_id not in (select * from tep)

3. 力扣612：平面上的最近距离

3.1 题目：

Point2D 表：

+-------------+------+
| Column Name | Type |
+-------------+------+
| x           | int  |
| y           | int  |
+-------------+------+
(x, y) 是该表的主键列(具有唯一值的列的组合)。
这张表的每一行表示 X-Y 平面上一个点的位置

p1(x1, y1) 和 p2(x2, y2) 这两点之间的距离是 sqrt((x2 - x1)2 + (y2 - y1)2) 。

编写解决方案，报告 Point2D 表中任意两点之间的最短距离。保留 2 位小数 。

返回结果格式如下例所示。

示例 1：

输入：
Point2D table:
+----+----+
| x  | y  |
+----+----+
| -1 | -1 |
| 0  | 0  |
| -1 | -2 |
+----+----+
输出：
+----------+
| shortest |
+----------+
| 1.00     |
+----------+
解释：最短距离是 1.00 ，从点 (-1, -1) 到点 (-1, 2) 。

3.2 思路：

使用笛卡尔积，考虑所有的情况。

3.3 题解：

-- 笛卡尔积，考虑所有情况
select round(min(sqrt(power(p1.x-p2.x, 2)+power(p1.y-p2.y, 2))), 2) shortest
from Point2D p1, Point2D  p2 
where (p1.x, p1.y) <> (p2.x, p2.y)

4. 力扣618：学生地理信息报告

4.1 题目：

表： student 

+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| name        | varchar |
| continent   | varchar |
+-------------+---------+
该表可能包含重复的行。
该表的每一行表示学生的名字和他们来自的大陆。

一所学校有来自亚洲、欧洲和美洲的学生。

编写解决方案实现对大洲（continent）列的 透视表 操作，使得每个学生按照姓名的字母顺序依次排列在对应的大洲下面。输出的标题应依次为美洲（America）、亚洲（Asia）和欧洲（Europe）。

测试用例的生成保证来自美国的学生人数不少于亚洲或欧洲的学生人数。

返回结果格式如下所示。

示例 1:

输入: 
Student table:
+--------+-----------+
| name   | continent |
+--------+-----------+
| Jane   | America   |
| Pascal | Europe    |
| Xi     | Asia      |
| Jack   | America   |
+--------+-----------+
输出: 
+---------+------+--------+
| America | Asia | Europe |
+---------+------+--------+
| Jack    | Xi   | Pascal |
| Jane    | null | null   |
+---------+------+--------+

进阶：如果不能确定哪个大洲的学生数最多，你可以写出一个查询去生成上述学生报告吗？

4.2 思路：

窗口函数+左外连接。

4.3 题解：

-- 为每个表中的记录分配一个行号
with America as(
    select name, row_number() over (order by name) id
    from Student
    where continent = 'America'
    
), Asia as(
    select name, row_number() over (order by name) id
    from Student
    where continent = 'Asia'
    
), Europe as(
    select name, row_number() over (order by name) id
    from Student
    where continent = 'Europe'
    
)
-- 然后两个左外连接
-- 美国的学生人数不少于亚洲或欧洲的学生人数。
-- 所以America肯定是左表
select t1.name America , t2.name Asia , t3.name Europe 
from America t1
left join Asia t2
on t1.id = t2.id 
left join Europe t3 
on t1.id = t3.id