高频sql50题 —— step2:连接
连接训练
hard: 0 medium:2 esay:7
1378. 使用唯一标识码替换员工ID - 力扣（LeetCode）
1581. 进店却未进行过交易的顾客 - 力扣（LeetCode）
1661. 每台机器的进程平均运行时间 - 力扣（LeetCode）
1280. 学生们参加各科测试的次数 - 力扣（LeetCode）
570. 至少有5名直接下属的经理 - 力扣（LeetCode）
使用唯一标识替换员工ID - EASY
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 。
解答
威以员工为主表，确保所有员工都存在，依据id左连接特殊标识表，默认没法匹配上的会填空
#### sql
SELECT eu.unique_id as unique_id,
es.name as name
FROM Employees es
LEFT JOIN EmployeeUNI eu ON es.id = eu.id
产品销售分析 I - EASY
销售表 Sales：
+-------------+-------+
| Column Name | Type |
+-------------+-------+
| sale_id | int |
| product_id | int |
| year | int |
| quantity | int |
| price | int |
+-------------+-------+
(sale_id, year) 是销售表 Sales 的主键（具有唯一值的列的组合）。
product_id 是关联到产品表 Product 的外键（reference 列）。
该表的每一行显示 product_id 在某一年的销售情况。
注意: price 表示每单位价格。
产品表 Product：
+--------------+---------+
| Column Name | Type |
+--------------+---------+
| product_id | int |
| product_name | varchar |
+--------------+---------+
product_id 是表的主键（具有唯一值的列）。
该表的每一行表示每种产品的产品名称。
编写解决方案，以获取 Sales 表中所有 sale_id 对应的 product_name 以及该产品的所有 year 和 price 。
返回结果表 无顺序要求 。
解答
#### sql
SELECT p.product_name as product_name,
s.year as year,
s.price as price
FROM Sales s
LEFT JOIN Product p ON s.product_id = p.product_id
进店却未进行过交易的顾客 - EASY
表：Visits
+-------------+---------+
| Column Name | Type |
+-------------+---------+
| visit_id | int |
| customer_id | int |
+-------------+---------+
visit_id 是该表中具有唯一值的列。
该表包含有关光临过购物中心的顾客的信息。
表：Transactions
+----------------+---------+
| Column Name | Type |
+----------------+---------+
| transaction_id | int |
| visit_id | int |
| amount | int |
+----------------+---------+
transaction_id 是该表中具有唯一值的列。
此表包含 visit_id 期间进行的交易的信息。
有一些顾客可能光顾了购物中心但没有进行交易。请你编写一个解决方案，来查找这些顾客的 ID ，以及他们只光顾不交易的次数。
返回以 任何顺序 排序的结果表。
解答
对全体顾客筛选没有进行过交易的：where
去重获取光临次数：分组计数GROUP + count
#### sql
SELECT v.customer_id as customer_id,
count(*) as count_no_trans
FROM Visits v
LEFT JOIN Transactions t ON v.visit_id = t.visit_id
WHERE t.transaction_id is null
GROUP BY v.customer_id
上升的温度 - EASY
表： Weather
+---------------+---------+
| Column Name | Type |
+---------------+---------+
| id | int |
| recordDate | date |
| temperature | int |
+---------------+---------+
id 是该表具有唯一值的列。
没有具有相同 recordDate 的不同行。
该表包含特定日期的温度信息
编写解决方案，找出与之前（昨天的）日期相比温度更高的所有日期的 id 。
返回结果 无顺序要求 。
结果格式如下例子所示。
示例 1：
输入：
Weather 表：
+----+------------+-------------+
| id | recordDate | Temperature |
+----+------------+-------------+
| 1 | 2015-01-01 | 10 |
| 2 | 2015-01-02 | 25 |
| 3 | 2015-01-03 | 20 |
| 4 | 2015-01-04 | 30 |
+----+------------+-------------+
输出：
+----+
| id |
+----+
| 2 |
| 4 |
+----+
解释：
2015-01-02 的温度比前一天高（10 -> 25）
2015-01-04 的温度比前一天高（20 -> 30）
解答
datediff函数可以计算日期之间的差值，以1作为差值进行连表就可以获取”下一天“的数据
#### sql
SELECT w2.id as Id
FROM Weather w1, Weather w2
WHERE datediff(w2.recordDate, w1.recordDate) = 1
AND w2.Temperature > w1.Temperature
每台机器的进程平均运行时间 - EASY
表: Activity
+----------------+---------+
| Column Name | Type |
+----------------+---------+
| machine_id | int |
| process_id | int |
| activity_type | enum |
| timestamp | float |
+----------------+---------+
该表展示了一家工厂网站的用户活动。
(machine_id, process_id, activity_type) 是当前表的主键（具有唯一值的列的组合）。
machine_id 是一台机器的ID号。
process_id 是运行在各机器上的进程ID号。
activity_type 是枚举类型 ('start', 'end')。
timestamp 是浮点类型,代表当前时间(以秒为单位)。
'start' 代表该进程在这台机器上的开始运行时间戳 , 'end' 代表该进程在这台机器上的终止运行时间戳。
同一台机器，同一个进程都有一对开始时间戳和结束时间戳，而且开始时间戳永远在结束时间戳前面。
现在有一个工厂网站由几台机器运行，每台机器上运行着 相同数量的进程 。编写解决方案，计算每台机器各自完成一个进程任务的平均耗时。
完成一个进程任务的时间指进程的'end' 时间戳 减去 'start' 时间戳。平均耗时通过计算每台机器上所有进程任务的总耗费时间除以机器上的总进程数量获得。
结果表必须包含machine_id（机器ID） 和对应的 average time（平均耗时） 别名 processing_time，且四舍五入保留3位小数。
以 任意顺序 返回表。
解答
要考虑合并同一条信息的start和end，将一行数据获取开始时间（后续称为前表）和结束时间信息（后续成为后表）
依据相同的machine_id和machine_id做匹配，将activity_type=end也作为连接的条件，筛去后表中的start数据
使用where筛去前表的end数据，最后对一行数据的时间做差值取平均数后保留三位小数
#### sql
SELECT a1.machine_id as machine_id,
round(avg(a2.timestamp - a1.timestamp), 3) as processing_time
FROM Activity a1
LEFT JOIN Activity a2 ON a1.machine_id = a2.machine_id
AND a1.process_id = a2.process_id
AND a2.activity_type = 'end'
WHERE a1.activity_type = 'start'
GROUP BY a1.machine_id
员工奖金 - EASY
表：Employee
+-------------+---------+
| Column Name | Type |
+-------------+---------+
| empId | int |
| name | varchar |
| supervisor | int |
| salary | int |
+-------------+---------+
empId 是该表中具有唯一值的列。
该表的每一行都表示员工的姓名和 id，以及他们的工资和经理的 id。、
表：Bonus
+-------------+------+
| Column Name | Type |
+-------------+------+
| empId | int |
| bonus | int |
+-------------+------+
empId 是该表具有唯一值的列。
empId 是 Employee 表中 empId 的外键(reference 列)。
该表的每一行都包含一个员工的 id 和他们各自的奖金。
编写解决方案，报告每个奖金 少于 1000 的员工的姓名和奖金数额。
以 任意顺序 返回结果表。
解答
#### sql
SELECT e.name as name,
b.bonus as bonus
FROM Employee e
LEFT JOIN Bonus b ON e.empId = b.empId
WHERE b.bonus < 1000
OR b.bonus IS NULL
学生们参加各科测试的次数 - EASY
学生表: Students
+---------------+---------+
| Column Name | Type |
+---------------+---------+
| student_id | int |
| student_name | varchar |
+---------------+---------+
在 SQL 中，主键为 student_id（学生ID）。
该表内的每一行都记录有学校一名学生的信息。
科目表: Subjects
+--------------+---------+
| Column Name | Type |
+--------------+---------+
| subject_name | varchar |
+--------------+---------+
在 SQL 中，主键为 subject_name（科目名称）。
每一行记录学校的一门科目名称。
考试表: Examinations
+--------------+---------+
| Column Name | Type |
+--------------+---------+
| student_id | int |
| subject_name | varchar |
+--------------+---------+
这个表可能包含重复数据（换句话说，在 SQL 中，这个表没有主键）。
学生表里的一个学生修读科目表里的每一门科目。
这张考试表的每一行记录就表示学生表里的某个学生参加了一次科目表里某门科目的测试。
查询出每个学生参加每一门科目测试的次数，结果按 student_id 和 subject_name 排序。
解答
题目要求展出所有学生参加所有科目的参考次数，首先对学生表和科目表进行交叉连接
处理考试次数表（计算每个学生在已考科目的考试次数）后连接上述交叉表
对没有考试信息的行取0
#### sql
SELECT st.student_id as student_id,
st.student_name as student_name,
sb.subject_name as subject_name,
IFNULL(e.attended_exams, 0) as attended_exams
FROM Students st
CROSS JOIN Subjects sb
LEFT JOIN (
SELECT ex.student_id as student_id,
ex.subject_name as subject_name,
count(*) as attended_exams
FROM Examinations ex
GROUP BY ex.student_id, ex.subject_name
) e ON st.student_id = e.student_id
AND sb.subject_name = e.subject_name
ORDER BY st.student_id, sb.subject_name
至少有5名直接下属的经理 - MEDIUM
表: Employee
+-------------+---------+
| Column Name | Type |
+-------------+---------+
| id | int |
| name | varchar |
| department | varchar |
| managerId | int |
+-------------+---------+
id 是此表的主键（具有唯一值的列）。
该表的每一行表示雇员的名字、他们的部门和他们的经理的id。
如果managerId为空，则该员工没有经理。
没有员工会成为自己的管理者。
编写一个解决方案，找出至少有五个直接下属的经理。
以 任意顺序 返回结果表。
解答
自连接，聚合后计数大于五 having
#### sql
SELECT e1.name
FROM Employee e1
LEFT JOIN Employee e2 ON e2.managerId = e1.id
GROUP BY e1.id
HAVING COUNT(*) >= 5
确认率 - MEDIUM
表: Signups
+----------------+----------+
| Column Name | Type |
+----------------+----------+
| user_id | int |
| time_stamp | datetime |
+----------------+----------+
User_id是该表的主键。
每一行都包含ID为user_id的用户的注册时间信息。
表: Confirmations
+----------------+----------+
| Column Name | Type |
+----------------+----------+
| user_id | int |
| time_stamp | datetime |
| action | ENUM |
+----------------+----------+
(user_id, time_stamp)是该表的主键。
user_id是一个引用到注册表的外键。
action是类型为('confirmed'， 'timeout')的ENUM
该表的每一行都表示ID为user_id的用户在time_stamp请求了一条确认消息，该确认消息要么被确认('confirmed')，要么被过期('timeout')。
用户的 确认率 是 'confirmed' 消息的数量除以请求的确认消息的总数。没有请求任何确认消息的用户的确认率为 0 。确认率四舍五入到 小数点后两位 。
编写一个SQL查询来查找每个用户的 确认率 。
以 任意顺序 返回结果表。
解答
利用IF函数进行协助计数
SELECT s.user_id,
ROUND(COUNT(IF(c.action = 'confirmed', 1, null)) / COUNT(*), 2) as confirmation_rate
FROM Signups s
LEFT JOIN Confirmations c ON s.user_id = c.user_id
GROUP BY s.user_id
本博客所有文章除特别声明外，均采用 CC BY-NC-SA 4.0 许可协议。转载请注明来自 IT蛋的个人博客！