前言
废话不多说，直接由题学习各种函数及思想，直接开干！
一、1280. 学生们参加各科测试的次数
1.题目描述
学生表: Students
+---------------+---------+ | Column Name | Type | +---------------+---------+ | student_id | int | | student_name | varchar | +---------------+---------+ 主键为 student_id（学生ID），该表内的每一行都记录有学校一名学生的信息。
科目表: Subjects
+--------------+---------+ | Column Name | Type | +--------------+---------+ | subject_name | varchar | +--------------+---------+ 主键为 subject_name（科目名称），每一行记录学校的一门科目名称。
考试表: Examinations
+--------------+---------+ | Column Name | Type | +--------------+---------+ | student_id | int | | subject_name | varchar | +--------------+---------+ 这张表压根没有主键，可能会有重复行。 学生表里的一个学生修读科目表里的每一门科目，而这张考试表的每一行记录就表示学生表里的某个学生参加了一次科目表里某门科目的测试。
要求写一段 SQL 语句，查询出每个学生参加每一门科目测试的次数，结果按 student_id 和 subject_name 排序。
查询结构格式如下所示：
Students table: +------------+--------------+ | student_id | student_name | +------------+--------------+ | 1 | Alice | | 2 | Bob | | 13 | John | | 6 | Alex | +------------+--------------+ Subjects table: +--------------+ | subject_name | +--------------+ | Math | | Physics | | Programming | +--------------+ Examinations table: +------------+--------------+ | student_id | subject_name | +------------+--------------+ | 1 | Math | | 1 | Physics | | 1 | Programming | | 2 | Programming | | 1 | Physics | | 1 | Math | | 13 | Math | | 13 | Programming | | 13 | Physics | | 2 | Math | | 1 | Math | +------------+--------------+ Result table: +------------+--------------+--------------+----------------+ | student_id | student_name | subject_name | attended_exams | +------------+--------------+--------------+----------------+ | 1 | Alice | Math | 3 | | 1 | Alice | Physics | 2 | | 1 | Alice | Programming | 1 | | 2 | Bob | Math | 1 | | 2 | Bob | Physics | 0 | | 2 | Bob | Programming | 1 | | 6 | Alex | Math | 0 | | 6 | Alex | Physics | 0 | | 6 | Alex | Programming | 0 | | 13 | John | Math | 1 | | 13 | John | Physics | 1 | | 13 | John | Programming | 1 | +------------+--------------+--------------+----------------+ 结果表需包含所有学生和所有科目（即便测试次数为0）： Alice 参加了 3 次数学测试, 2 次物理测试，以及 1 次编程测试； Bob 参加了 1 次数学测试, 1 次编程测试，没有参加物理测试； Alex 啥测试都没参加； John 参加了数学、物理、编程测试各 1 次。
2.解题思路
1) 先将students表和subjects连接构成每名学生与每科目匹配的临时表
2）将临时表与考试表依据条件左连接
3）使用GROUP BY 分组并使用聚合函数COUNT() 来计算次数
4）注意这里我们要count的是e.subject_name 这样才会统计到没参加考试的0值
3.代码实现
SELECT
T.student_id,
T.student_name,
T.subject_name,
COUNT(E.subject_name) AS attended_exams
FROM (
SELECT *
FROM Students
INNER JOIN Subjects
) T
LEFT JOIN Examinations E
ON T.student_id = E.student_id AND T.subject_name = E.subject_name
GROUP BY T.subject_name, T.student_name
ORDER BY T.student_id, T.subject_name二、可以放心投资的国家
1.题目描述
表 Person:
+----------------+---------+ | Column Name | Type | +----------------+---------+ | id | int | | name | varchar | | phone_number | varchar | +----------------+---------+ id 是该表主键. 该表每一行包含一个人的名字和电话号码. 电话号码的格式是:'xxx-yyyyyyy', 其中xxx是国家码(3个字符), yyyyyyy是电话号码(7个字符), x和y都表示数字. 同时, 国家码和电话号码都可以包含前导0.
表 Country:
+----------------+---------+ | Column Name | Type | +----------------+---------+ | name | varchar | | country_code | varchar | +----------------+---------+ country_code是该表主键. 该表每一行包含国家名和国家码. country_code的格式是'xxx', x是数字.
表 Calls:
+-------------+------+ | Column Name | Type | +-------------+------+ | caller_id | int | | callee_id | int | | duration | int | +-------------+------+ 该表无主键, 可能包含重复行. 每一行包含呼叫方id, 被呼叫方id和以分钟为单位的通话时长. caller_id != callee_id
一家电信公司想要投资新的国家. 该公司想要投资的国家是: 该国的平均通话时长要严格地大于全球平均通话时长.
写一段 SQL, 找到所有该公司可以投资的国家.
返回的结果表没有顺序要求.
查询的结果格式如下例所示.
Person表: +----+----------+--------------+ | id | name | phone_number | +----+----------+--------------+ | 3 | Jonathan | 051-1234567 | | 12 | Elvis | 051-7654321 | | 1 | Moncef | 212-1234567 | | 2 | Maroua | 212-6523651 | | 7 | Meir | 972-1234567 | | 9 | Rachel | 972-0011100 | +----+----------+--------------+Country表: +----------+--------------+ | name | country_code | +----------+--------------+ | Peru | 051 | | Israel | 972 | | Morocco | 212 | | Germany | 049 | | Ethiopia | 251 | +----------+--------------+ Calls 表: +-----------+-----------+----------+ | caller_id | callee_id | duration | +-----------+-----------+----------+ | 1 | 9 | 33 | | 2 | 9 | 4 | | 1 | 2 | 59 | | 3 | 12 | 102 | | 3 | 12 | 330 | | 12 | 3 | 5 | | 7 | 9 | 13 | | 7 | 1 | 3 | | 9 | 7 | 1 | | 1 | 7 | 7 | +-----------+-----------+----------+ Result 表: +----------+ | country | +----------+ | Peru | +----------+ 国家Peru的平均通话时长是 (102 + 102 + 330 + 330 + 5 + 5) / 6 = 145.666667 国家Israel的平均通话时长是 (33 + 4 + 13 + 13 + 3 + 1 + 1 + 7) / 8 = 9.37500 国家Morocco的平均通话时长是 (33 + 4 + 59 + 59 + 3 + 7) / 6 = 27.5000 全球平均通话时长 = (2 * (33 + 4 + 59 + 102 + 330 + 5 + 13 + 3 + 1 + 7)) / 20 = 55.70000 所以, Peru是唯一的平均通话时长大于全球平均通话时长的国家, 也是唯一的推荐投资的国家.
2.解题思路
主要运用函数 WITH AS, UNION ALL, LEFT( , )
1) 本题主要思想其实是将caller_id和callee_id放到同一列，因此我们采用 WITH AS 函数来构建这样的临时表，同时使用UNION ALL 达到这一目的。
2) 将临时表左连接Person表和Country表，其中筛选电话号码前三位相同来找出相同国家需要LEFT(phone_number, 3)
3)最后以国家名字分组来找到平均值大于整体平均值的国家
3.代码实现
# Write your MySQL query statement below
WITH a AS (
SELECT caller_id AS call_id, duration FROM Calls
UNION ALL
SELECT callee_id AS call_id, duration FROM Calls
)
SELECT c.name AS country
FROM a
LEFT JOIN Person p
ON a.call_id = p.id
LEFT JOIN Country c
ON c.country_code = LEFT(phone_number, 3)
GROUP BY c.name
Having AVG(a.duration) > (select AVG(a.duration) from a)
三、580. 统计各专业学生人数
1.题目描述
表: Student
+--------------+---------+ | Column Name | Type | +--------------+---------+ | student_id | int | | student_name | varchar | | gender | varchar | | dept_id | int | +--------------+---------+ Student_id是该表的主键。 dept_id是Department表中dept_id的外键。 该表的每一行都表示学生的姓名、性别和所属系的id。
表: Department
+-------------+---------+ | Column Name | Type | +-------------+---------+ | dept_id | int | | dept_name | varchar | +-------------+---------+ Dept_id是该表的主键。 该表的每一行包含一个部门的id和名称。
编写一个SQL查询，为 Department 表中的所有部门(甚至是没有当前学生的部门)报告各自的部门名称和每个部门的学生人数。
按 student_number 降序 返回结果表。如果是平局，则按 dept_name 的 字母顺序 排序。
查询结果格式如下所示。
示例 1:
输入: Student 表: +------------+--------------+--------+---------+ | student_id | student_name | gender | dept_id | +------------+--------------+--------+---------+ | 1 | Jack | M | 1 | | 2 | Jane | F | 1 | | 3 | Mark | M | 2 | +------------+--------------+--------+---------+ Department 表: +---------+-------------+ | dept_id | dept_name | +---------+-------------+ | 1 | Engineering | | 2 | Science | | 3 | Law | +---------+-------------+ 输出: +-------------+----------------+ | dept_name | student_number | +-------------+----------------+ | Engineering | 2 | | Science | 1 | | Law | 0 | +-------------+----------------+
2.解题思路
很常规的sql题
1）先连接
2）再分组
3）再排序最后计数
3.代码实现
# Write your MySQL query statement below
SELECT D.dept_name, COUNT(S.student_id) AS student_number
FROM Department D
LEFT JOIN Student S
ON D.dept_id = S.dept_id
GROUP BY D.dept_name
ORDER BY student_number DESC, D.dept_name四、1294. 不同国家的天气类型
1.题目描述
国家表：Countries
+---------------+---------+ | Column Name | Type | +---------------+---------+ | country_id | int | | country_name | varchar | +---------------+---------+ country_id 是这张表的主键。 该表的每行有 country_id 和 country_name 两列。
天气表：Weather
+---------------+---------+ | Column Name | Type | +---------------+---------+ | country_id | int | | weather_state | varchar | | day | date | +---------------+---------+ (country_id, day) 是该表的复合主键。 该表的每一行记录了某个国家某一天的天气情况。
写一段 SQL 来找到表中每个国家在 2019 年 11 月的天气类型。
天气类型的定义如下：当 weather_state 的平均值小于或等于15返回 Cold，当 weather_state 的平均值大于或等于 25 返回 Hot，否则返回 Warm。
你可以以任意顺序返回你的查询结果。
查询结果格式如下所示：
Countries table: +------------+--------------+ | country_id | country_name | +------------+--------------+ | 2 | USA | | 3 | Australia | | 7 | Peru | | 5 | China | | 8 | Morocco | | 9 | Spain | +------------+--------------+ Weather table: +------------+---------------+------------+ | country_id | weather_state | day | +------------+---------------+------------+ | 2 | 15 | 2019-11-01 | | 2 | 12 | 2019-10-28 | | 2 | 12 | 2019-10-27 | | 3 | -2 | 2019-11-10 | | 3 | 0 | 2019-11-11 | | 3 | 3 | 2019-11-12 | | 5 | 16 | 2019-11-07 | | 5 | 18 | 2019-11-09 | | 5 | 21 | 2019-11-23 | | 7 | 25 | 2019-11-28 | | 7 | 22 | 2019-12-01 | | 7 | 20 | 2019-12-02 | | 8 | 25 | 2019-11-05 | | 8 | 27 | 2019-11-15 | | 8 | 31 | 2019-11-25 | | 9 | 7 | 2019-10-23 | | 9 | 3 | 2019-12-23 | +------------+---------------+------------+ Result table: +--------------+--------------+ | country_name | weather_type | +--------------+--------------+ | USA | Cold | | Austraila | Cold | | Peru | Hot | | China | Warm | | Morocco | Hot | +--------------+--------------+ USA 11 月的平均 weather_state 为 (15) / 1 = 15 所以天气类型为 Cold。 Australia 11 月的平均 weather_state 为 (-2 + 0 + 3) / 3 = 0.333 所以天气类型为 Cold。 Peru 11 月的平均 weather_state 为 (25) / 1 = 25 所以天气类型为 Hot。 China 11 月的平均 weather_state 为 (16 + 18 + 21) / 3 = 18.333 所以天气类型为 Warm。 Morocco 11 月的平均 weather_state 为 (25 + 27 + 31) / 3 = 27.667 所以天气类型为 Hot。 我们并不知道 Spain 在 11 月的 weather_state 情况所以无需将他包含在结果中。
2.解题思路
这题主要思路也是基本题思路，先建立连接再筛选出符合要求日期最后根据country_name进行分组查询，当然其中有需要注意的点：
1）YEAR(), MONTH(), DAY()函数对日期列提取的使用
2）CASE WHEN....THEN.....END AS 的使用
3.代码实现
# Write your MySQL query statement below
SELECT c.country_name,
CASE
WHEN AVG(w.weather_state) >= 25 THEN 'Hot'
WHEN AVG(w.weather_state) <= 15 THEN 'Cold'
ELSE 'Warm'
END AS weather_type
FROM Weather w
LEFT JOIN Countries c
ON w.country_id = c.country_id
where YEAR(w.day) = 2019 AND MONTH(w.day) = 11
GROUP BY c.country_name五、626. 换座位
1.题目描述
表: Seat
+-------------+---------+ | Column Name | Type | +-------------+---------+ | id | int | | name | varchar | +-------------+---------+ Id是该表的主键列。 该表的每一行都表示学生的姓名和ID。 Id是一个连续的增量。
编写SQL查询来交换每两个连续的学生的座位号。如果学生的数量是奇数，则最后一个学生的id不交换。
按 id 升序 返回结果表。
查询结果格式如下所示。
示例 1:
输入: Seat 表: +----+---------+ | id | student | +----+---------+ | 1 | Abbot | | 2 | Doris | | 3 | Emerson | | 4 | Green | | 5 | Jeames | +----+---------+ 输出: +----+---------+ | id | student | +----+---------+ | 1 | Doris | | 2 | Abbot | | 3 | Green | | 4 | Emerson | | 5 | Jeames | +----+---------+ 解释: 请注意，如果学生人数为奇数，则不需要更换最后一名学生的座位。
2.解题思路
常用思路一：
对于所有座位 id 是奇数的学生，修改其 id 为 id+1，如果最后一个座位 id 也是奇数，则最后一个座位 id 不修改。对于所有座位 id 是偶数的学生，修改其 id 为 id-1。
1）查询座位的数量做为临时表
2）然后使用 CASE 条件和 MOD 函数修改每个学生的座位 id。
常见思路二：
使用 (id+1)^1-1 计算交换后每个学生的座位 id，即：
| id | (id+1)^1-1 | student |
|----|------------|---------|
| 1 | 2 | Abbot |
| 2 | 1 | Doris |
| 3 | 4 | Emerson |
| 4 | 3 | Green |
| 5 | 6 | Jeames |
然后连接原来的座位表和更新 id 后的座位表。最后输出 s1.id 和 s2.student。但是 id=5 的学生，s1.student 正确，s2.student 为 NULL。因此使用 COALESCE() 函数为最后一行记录生成正确的输出。
其中COALESCE(A, B, C)函数会输出第一个不为NULL的变量
| id | student | id | student |
|----|---------|----|---------|
| 1 | Abbot | 2 | Doris |
| 2 | Doris | 1 | Abbot |
| 3 | Emerson | 4 | Green |
| 4 | Green | 3 | Emerson |
| 5 | Jeames | | |
3.代码实现
# Write your MySQL query statement below
#直接使用位运算
#SELECT s1.id, COALESCE(s2.student, s1.student) AS student
#FROM Seat s1
#LEFT JOIN Seat s2
#ON s1.id = (s2.id + 1) ^ 1 - 1
#ORDER BY s1.id
WITH Counts AS (
SELECT COUNT(id) AS counts
FROM Seat
)
SELECT
CASE
WHEN MOD(id, 2) != 0 AND id != counts THEN id + 1
WHEN MOD(id, 2) != 0 AND id = counts THEN id
WHEN MOD(id, 2) = 0 THEN id - 1
END AS id,
student
FROM Seat, Counts
ORDER BY id六、1783. 大满贯数量
1.题目描述
表：Players
+----------------+---------+ | Column Name | Type | +----------------+---------+ | player_id | int | | player_name | varchar | +----------------+---------+ player_id 是这个表的主键 这个表的每一行给出一个网球运动员的 ID 和 姓名
表：Championships
+---------------+---------+ | Column Name | Type | +---------------+---------+ | year | int | | Wimbledon | int | | Fr_open | int | | US_open | int | | Au_open | int | +---------------+---------+ year 是这个表的主键 该表的每一行都包含在每场大满贯网球比赛中赢得比赛的球员的 ID
请写出查询语句，查询出每一个球员赢得大满贯比赛的次数。结果不包含没有赢得比赛的球员的ID
结果集 无顺序要求 。
查询结果的格式，如下所示。
示例 1:
输入： Players 表： +-----------+-------------+ | player_id | player_name | +-----------+-------------+ | 1 | Nadal | | 2 | Federer | | 3 | Novak | +-----------+-------------+ Championships 表： +------+-----------+---------+---------+---------+ | year | Wimbledon | Fr_open | US_open | Au_open | +------+-----------+---------+---------+---------+ | 2018 | 1 | 1 | 1 | 1 | | 2019 | 1 | 1 | 2 | 2 | | 2020 | 2 | 1 | 2 | 2 | +------+-----------+---------+---------+---------+ 输出： +-----------+-------------+-------------------+ | player_id | player_name | grand_slams_count | +-----------+-------------+-------------------+ | 2 | Federer | 5 | | 1 | Nadal | 7 | +-----------+-------------+-------------------+ 解释： Player 1 (Nadal) 获得了 7 次大满贯：其中温网 2 次(2018, 2019), 法国公开赛 3 次 (2018, 2019, 2020), 美国公开赛 1 次 (2018)以及澳网公开赛 1 次 (2018) 。 Player 2 (Federer) 获得了 5 次大满贯：其中温网 1 次 (2020), 美国公开赛 2 次 (2019, 2020) 以及澳网公开赛 2 次 (2019, 2020) 。 Player 3 (Novak) 没有赢得，因此不包含在结果集中。
2.解题思路
本题关键在于使用UNION ALL 将三个比赛合成一列
做为临时表temp，将运动员表和temp表左连接进行筛选分组统计
3.代码实现
# Write your MySQL query statement below
WITH temp AS (
SELECT Wimbledon AS total FROM Championships
UNION ALL
SELECT Fr_open FROM Championships
UNION ALL
SELECT US_open FROM Championships
UNION ALL
SELECT Au_open FROM Championships
)
SELECT p.player_id, p.player_name, COUNT(player_id) AS grand_slams_count
FROM Players p
LEFT JOIN temp
ON p.player_id = temp.total
WHERE temp.total IS NOT NULL
GROUP BY p.player_id七、指定日期的产品价格
1.题目描述
产品数据表: Products
+---------------+---------+ | Column Name | Type | +---------------+---------+ | product_id | int | | new_price | int | | change_date | date | +---------------+---------+ 这张表的主键是 (product_id, change_date)。 这张表的每一行分别记录了 某产品 在某个日期 更改后 的新价格。
写一段 SQL来查找在 2019-08-16 时全部产品的价格，假设所有产品在修改前的价格都是 10 。
以 任意顺序 返回结果表。
查询结果格式如下例所示。
示例 1:
输入：
Products 表:
+------------+-----------+-------------+
| product_id | new_price | change_date |
+------------+-----------+-------------+
| 1 | 20 | 2019-08-14 |
| 2 | 50 | 2019-08-14 |
| 1 | 30 | 2019-08-15 |
| 1 | 35 | 2019-08-16 |
| 2 | 65 | 2019-08-17 |
| 3 | 20 | 2019-08-18 |
+------------+-----------+-------------+
输出：
+------------+-------+
| product_id | price |
+------------+-------+
| 2 | 50 |
| 1 | 35 |
| 3 | 10 |
+------------+-------+
2.解题思路
此题主要为建立子查询，第一个子查询是不同商品的id，第二个子查询是找出表中在2019-08-16时间范围内的分组最大日期下的id和价格，最后我们通过将两个子查询连接，得出结果，
要注意的是：使用COALESCE(a, b, c, d) 和 IFNULL(a, b)都可以
3.代码实现
# Write your MySQL query statement below
#COALESCE(a, b, c, d) 和 IFNULL(a, b)都可以
SELECT p1.product_id, COALESCE(p2.new_price, 10) AS price
FROM (
SELECT DISTINCT product_id
FROM Products
) AS p1
LEFT JOIN (
SELECT product_id, new_price
FROM Products
WHERE (product_id, change_date) IN (
SELECT product_id, MAX(change_date)
FROM Products
WHERE change_date <= '2019-08-16'
GROUP BY product_id
)
) AS p2
ON p1.product_id = p2.product_id
七、1731. 每位经理的下属员工数量
1.题目描述
Table: Employees
+-------------+----------+ | Column Name | Type | +-------------+----------+ | employee_id | int | | name | varchar | | reports_to | int | | age | int | +-------------+----------+ employee_id 是这个表的主键. 该表包含员工以及需要听取他们汇报的上级经理的ID的信息。 有些员工不需要向任何人汇报（reports_to 为空）。
对于此问题，我们将至少有一个其他员工需要向他汇报的员工，视为一个经理。
编写SQL查询需要听取汇报的所有经理的ID、名称、直接向该经理汇报的员工人数，以及这些员工的平均年龄，其中该平均年龄需要四舍五入到最接近的整数。
返回的结果集需要按照 employee_id 进行排序。
查询结果的格式如下：
Employees table: +-------------+---------+------------+-----+ | employee_id | name | reports_to | age | +-------------+---------+------------+-----+ | 9 | Hercy | null | 43 | | 6 | Alice | 9 | 41 | | 4 | Bob | 9 | 36 | | 2 | Winston | null | 37 | +-------------+---------+------------+-----+ Result table: +-------------+-------+---------------+-------------+ | employee_id | name | reports_count | average_age | +-------------+-------+---------------+-------------+ | 9 | Hercy | 2 | 39 | +-------------+-------+---------------+-------------+ Hercy 有两个需要向他汇报的员工, 他们是 Alice and Bob. 他们的平均年龄是 (41+36)/2 = 38.5, 四舍五入的结果是 39.
2.解题思路
同样是子查询问题，我们要注意的是要根据Employees表来找出经理id即直接根据reports_to来分组，并进行统计或求取平均值等操作。
最后通过子查询连接Employees表来获取结果
3.代码实现
# Write your MySQL query statement below
SELECT e.employee_id AS employee_id,
e.name AS name,
t.reports_count AS reports_count,
t.average_age AS average_age
FROM (SELECT reports_to AS manager_id,
COUNT(employee_id) AS reports_count,
ROUND(AVG(age),0) AS average_age
FROM Employees
WHERE reports_to IS NOT NULL
GROUP BY reports_to) t
LEFT JOIN Employees e
ON t.manager_id=e.employee_id
ORDER BY e.employee_id八、 找出每所学校的最低分数要求
1.题目描述
表： Schools
+-------------+------+ | Column Name | Type | +-------------+------+ | school_id | int | | capacity | int | +-------------+------+ school_id 是该表的主键。 此表包含了一些学校的容纳能力。容纳能力指的是学校能够接纳学生的最大数量。
表： Exam
+---------------+------+ | Column Name | Type | +---------------+------+ | score | int | | student_count | int | +---------------+------+ score 是该表的主键。 表中每一行表示有 student_count 名学生在考试中至少获得了 score 分。 表中的数据在逻辑上是正确的，即记录了高score的行相比记录了低score的行拥有相同或更少的student_count。 也就是说，对于表中的i行与j行，如果scorei > scorej，那么student_counti <= student_countj
每年，学校会公布学生申请所需的最低分数要求。学校根据所有学生的考试成绩来决定其最低分数要求。
- 学校希望确保即使每一个满足分数要求的学生都申请该学校，学校也有足够的能力接纳每一个学生。
- 学校也希望尽可能多的学生能申请该学校。
- 学校必须使用在
Exam表中的 score 来作为最低分数要求。
编写一个SQL查询来查询每所学校的最低分数要求。如果同时有多个score值满足上述要求，则选择其中最小的一个。如果数据不足以决定最低分数要求，那么输出 -1。
返回的结果表可以按任意顺序排序。
查询结果格式如下例所示：
示例：
输入:
Schools 表:
+-----------+----------+
| school_id | capacity |
+-----------+----------+
| 11 | 151 |
| 5 | 48 |
| 9 | 9 |
| 10 | 99 |
+-----------+----------+
Exam 表:
+-------+---------------+
| score | student_count |
+-------+---------------+
| 975 | 10 |
| 966 | 60 |
| 844 | 76 |
| 749 | 76 |
| 744 | 100 |
+-------+---------------+
输出:
+-----------+-------+
| school_id | score |
+-----------+-------+
| 5 | 975 |
| 9 | -1 |
| 10 | 749 |
| 11 | 744 |
+-----------+-------+
解释：
- School 5：学校的容纳能力为 48 。选择 975 作为最低分数要求，因为学校最多会收到 10 份申请，这在学校的容纳能力以内。
- School 10：学校的容纳能力为 99 。可以选择 844 或 749 作为最低分数要求，因为学校最多会收到 76 份申请，这在学校的容纳能力以内。又因为 749 是所有可选项中最小的，因此我们选择 749 。
- School 11：学校的容纳能力为 151 。选择 744 作为最低分数要求，因为学校最多会收到 100 份申请，这在学校的容纳能力以内。
- School 9：给出的数据不足以确定最低分数要求。如果选择 975 作为最低分数要求，学校可能会收到 10 份申请，然而学校的容纳能力只有 9 。我们没有关于更高分数的信息，因此我们返回 -1 。
2.解题思路
本题关键点在于使用IF NULL函数，注意IFNULL函数的使用
3.代码实现
# Write your MySQL query statement below
SELECT s.school_id,
IFNULL(MIN(e.score), -1) AS score
FROM Schools s
LEFT JOIN Exam e
ON s.capacity >= e.student_count
GROUP BY s.school_id
ORDER BY s.school_id
九、1045. 买下所有产品的客户
1.题目描述
Customer 表：
+-------------+---------+
| Column Name | Type |
+-------------+---------+
| customer_id | int |
| product_key | int |
+-------------+---------+
product_key 是 Customer 表的外键
Product 表：
+-------------+---------+ | Column Name | Type | +-------------+---------+ | product_key | int | +-------------+---------+ product_key 是这张表的主键。
写一条 SQL 查询语句，从 Customer 表中查询购买了 Product 表中所有产品的客户的 id。
示例：
Customer 表： +-------------+-------------+ | customer_id | product_key | +-------------+-------------+ | 1 | 5 | | 2 | 6 | | 3 | 5 | | 3 | 6 | | 1 | 6 | +-------------+-------------+ Product 表： +-------------+ | product_key | +-------------+ | 5 | | 6 | +-------------+ Result 表： +-------------+ | customer_id | +-------------+ | 1 | | 3 | +-------------+ 购买了所有产品（5 和 6）的客户的 id 是 1 和 3
2.解题思路
本题的重点是product_key 是Product表中的主键，同时也是Customer的外键，因此可以简化为简单的问题，Customer表中的product_key是与Product表中一一对应的，不可能超出范围：
1. 在使用关系表时，仅在关系列中插入合法的数据非常重要。
2. 如果在customer表中插入拥有非法商品ID (即没有在product表中出现)的商品，则这些
产品是不可访问的，因为它们没有关联到某个产品。
3. 为防止这种情况发生，可指示MySQL只允许在customer表的product_key列中必须出现合法值(即出现在product表中的商品)。
**这就是维护引用完整性，它是通过在表的定义中指定主键和外键来实现的.**
另外数据一致原则, 以及使用外键的好处:
1. 商品信息不重复，从而不浪费时间和空间;
2. 如果商品信息变动，可以只更新product表中的单个记录，相关表中的数据不用改动;
3. 由于数据无重复，显然数据是一致的，这使得处理数据更简单
3.代码实现
# Write your MySQL query statement below
/**1. **在使用关系表时，仅在关系列中插入合法的数据非常重要。**
2. 回到这里的例子，如果在customer表中插入拥有非法商品ID (即没有在product表中出现)的商品，则这些
产品是不可访问的，因为它们没有关联到某个产品。
3. **为防止这种情况发生，可指示MySQL只允许在customer表的product_key列中必须出现合法值(即出现在product表中的商品)。**
**这就是维护引用完整性，它是通过在表的定义中指定主键和外键来实现的.**
另外数据一致原则, 以及使用外键的好处:
1. 商品信息不重复，从而不浪费时间和空间;
2. 如果商品信息变动，可以只更新product表中的单个记录，相关表中的数据不用改动;
3. 由于数据无重复，显然数据是一致的，这使得处理数据更简单
**/
SELECT customer_id
FROM Customer
GROUP BY customer_id
HAVING COUNT(DISTINCT product_key) = (SELECT COUNT(product_key) FROM Product )十、1341. 电影评分
1.题目描述
表：Movies
+---------------+---------+ | Column Name | Type | +---------------+---------+ | movie_id | int | | title | varchar | +---------------+---------+ movie_id 是这个表的主键。 title 是电影的名字。
表：Users
+---------------+---------+ | Column Name | Type | +---------------+---------+ | user_id | int | | name | varchar | +---------------+---------+ user_id 是表的主键。
表：MovieRating
+---------------+---------+ | Column Name | Type | +---------------+---------+ | movie_id | int | | user_id | int | | rating | int | | created_at | date | +---------------+---------+ (movie_id, user_id) 是这个表的主键。 这个表包含用户在其评论中对电影的评分 rating 。 created_at 是用户的点评日期。
请你编写一组 SQL 查询：
- 查找评论电影数量最多的用户名。如果出现平局，返回字典序较小的用户名。
- 查找在
February 2020平均评分最高 的电影名称。如果出现平局，返回字典序较小的电影名称。
字典序 ，即按字母在字典中出现顺序对字符串排序，字典序较小则意味着排序靠前。
查询结果格式如下例所示。
示例：
输入： Movies 表： +-------------+--------------+ | movie_id | title | +-------------+--------------+ | 1 | Avengers | | 2 | Frozen 2 | | 3 | Joker | +-------------+--------------+ Users 表： +-------------+--------------+ | user_id | name | +-------------+--------------+ | 1 | Daniel | | 2 | Monica | | 3 | Maria | | 4 | James | +-------------+--------------+ MovieRating 表： +-------------+--------------+--------------+-------------+ | movie_id | user_id | rating | created_at | +-------------+--------------+--------------+-------------+ | 1 | 1 | 3 | 2020-01-12 | | 1 | 2 | 4 | 2020-02-11 | | 1 | 3 | 2 | 2020-02-12 | | 1 | 4 | 1 | 2020-01-01 | | 2 | 1 | 5 | 2020-02-17 | | 2 | 2 | 2 | 2020-02-01 | | 2 | 3 | 2 | 2020-03-01 | | 3 | 1 | 3 | 2020-02-22 | | 3 | 2 | 4 | 2020-02-25 | +-------------+--------------+--------------+-------------+ 输出： Result 表： +--------------+ | results | +--------------+ | Daniel | | Frozen 2 | +--------------+ 解释： Daniel 和 Monica 都点评了 3 部电影（"Avengers", "Frozen 2" 和 "Joker"） 但是 Daniel 字典序比较小。 Frozen 2 和 Joker 在 2 月的评分都是 3.5，但是 Frozen 2 的字典序比较小。
2.解题思路
使用主要函数是UNION ALL 将查完人的表和查完电影的表结合，即可得出结果
其中需要注意的是使用LIMIT 函数
3.代码实现
# Write your MySQL query statement below
SELECT T.results
FROM(
SELECT U.name AS results
FROM Users U, MovieRating MR
WHERE U.user_id = MR.user_id
GROUP BY U.user_id, U.name
ORDER BY COUNT(1) DESC, U.name ASC
LIMIT 1
) AS T
UNION ALL
SELECT Q.results
FROM(
SELECT M.title AS results
FROM Movies M, MovieRating MM
WHERE M.movie_id = MM.movie_id
AND YEAR(MM.created_at) = 2020
AND MONTH(MM.created_at) = 02
GROUP BY M.movie_id
ORDER BY AVG(MM.rating) DESC, M.title ASC
LIMIT 1
) AS Q
十一、550. 游戏玩法分析 IV
1.题目描述
Table: Activity
+--------------+---------+ | Column Name | Type | +--------------+---------+ | player_id | int | | device_id | int | | event_date | date | | games_played | int | +--------------+---------+ （player_id，event_date）是此表的主键。 这张表显示了某些游戏的玩家的活动情况。 每一行是一个玩家的记录，他在某一天使用某个设备注销之前登录并玩了很多游戏（可能是 0）。
编写一个 SQL 查询，报告在首次登录的第二天再次登录的玩家的比率，四舍五入到小数点后两位。换句话说，您需要计算从首次登录日期开始至少连续两天登录的玩家的数量，然后除以玩家总数。
查询结果格式如下所示：
Activity table: +-----------+-----------+------------+--------------+ | player_id | device_id | event_date | games_played | +-----------+-----------+------------+--------------+ | 1 | 2 | 2016-03-01 | 5 | | 1 | 2 | 2016-03-02 | 6 | | 2 | 3 | 2017-06-25 | 1 | | 3 | 1 | 2016-03-02 | 0 | | 3 | 4 | 2018-07-03 | 5 | +-----------+-----------+------------+--------------+ Result table: +-----------+ | fraction | +-----------+ | 0.33 | +-----------+ 只有 ID 为 1 的玩家在第一天登录后才重新登录，所以答案是 1/3 = 0.33
2.解题思路
1）只要求玩家第二天登录的比率，即只和player_id与event_date有关
2）建立子查询，以player_id为分组，得出其最早登录日期的后一天
3）通过WHERE (player_id, event_date) IN来筛选是否存在最早登录后一天也登录，如果是那么必为连续两天登录的用户
4）ROUND 函数来看保留即为小数字
3.代码实现
# Write your MySQL query statement below
SELECT
ROUND(COUNT(DISTINCT player_id) / (SELECT COUNT(DISTINCT player_id) FROM Activity), 2) AS fraction
FROM Activity
WHERE (player_id, event_date) IN (
SELECT player_id, date(MIN(event_date) + 1) AS next
FROM Activity
GROUP BY player_id
)十二、262. 行程和用户
1.题目描述
表：Trips
+-------------+----------+ | Column Name | Type | +-------------+----------+ | id | int | | client_id | int | | driver_id | int | | city_id | int | | status | enum | | request_at | date | +-------------+----------+ id 是这张表的主键。 这张表中存所有出租车的行程信息。每段行程有唯一 id ，其中 client_id 和 driver_id 是 Users 表中 users_id 的外键。 status 是一个表示行程状态的枚举类型，枚举成员为(‘completed’, ‘cancelled_by_driver’, ‘cancelled_by_client’) 。
表：Users
+-------------+----------+ | Column Name | Type | +-------------+----------+ | users_id | int | | banned | enum | | role | enum | +-------------+----------+ users_id 是这张表的主键。 这张表中存所有用户，每个用户都有一个唯一的 users_id ，role 是一个表示用户身份的枚举类型，枚举成员为 (‘client’, ‘driver’, ‘partner’) 。 banned 是一个表示用户是否被禁止的枚举类型，枚举成员为 (‘Yes’, ‘No’) 。
取消率 的计算方式如下：(被司机或乘客取消的非禁止用户生成的订单数量) / (非禁止用户生成的订单总数)。
写一段 SQL 语句查出 "2013-10-01" 至 "2013-10-03" 期间非禁止用户（乘客和司机都必须未被禁止）的取消率。非禁止用户即 banned 为 No 的用户，禁止用户即 banned 为 Yes 的用户。
返回结果表中的数据可以按任意顺序组织。其中取消率 Cancellation Rate 需要四舍五入保留 两位小数 。
查询结果格式如下例所示。
示例：
输入： Trips 表： +----+-----------+-----------+---------+---------------------+------------+ | id | client_id | driver_id | city_id | status | request_at | +----+-----------+-----------+---------+---------------------+------------+ | 1 | 1 | 10 | 1 | completed | 2013-10-01 | | 2 | 2 | 11 | 1 | cancelled_by_driver | 2013-10-01 | | 3 | 3 | 12 | 6 | completed | 2013-10-01 | | 4 | 4 | 13 | 6 | cancelled_by_client | 2013-10-01 | | 5 | 1 | 10 | 1 | completed | 2013-10-02 | | 6 | 2 | 11 | 6 | completed | 2013-10-02 | | 7 | 3 | 12 | 6 | completed | 2013-10-02 | | 8 | 2 | 12 | 12 | completed | 2013-10-03 | | 9 | 3 | 10 | 12 | completed | 2013-10-03 | | 10 | 4 | 13 | 12 | cancelled_by_driver | 2013-10-03 | +----+-----------+-----------+---------+---------------------+------------+ Users 表： +----------+--------+--------+ | users_id | banned | role | +----------+--------+--------+ | 1 | No | client | | 2 | Yes | client | | 3 | No | client | | 4 | No | client | | 10 | No | driver | | 11 | No | driver | | 12 | No | driver | | 13 | No | driver | +----------+--------+--------+ 输出： +------------+-------------------+ | Day | Cancellation Rate | +------------+-------------------+ | 2013-10-01 | 0.33 | | 2013-10-02 | 0.00 | | 2013-10-03 | 0.50 | +------------+-------------------+ 解释： 2013-10-01： - 共有 4 条请求，其中 2 条取消。 - 然而，id=2 的请求是由禁止用户（user_id=2）发出的，所以计算时应当忽略它。 - 因此，总共有 3 条非禁止请求参与计算，其中 1 条取消。 - 取消率为 (1 / 3) = 0.33 2013-10-02： - 共有 3 条请求，其中 0 条取消。 - 然而，id=6 的请求是由禁止用户发出的，所以计算时应当忽略它。 - 因此，总共有 2 条非禁止请求参与计算，其中 0 条取消。 - 取消率为 (0 / 2) = 0.00 2013-10-03： - 共有 3 条请求，其中 1 条取消。 - 然而，id=8 的请求是由禁止用户发出的，所以计算时应当忽略它。 - 因此，总共有 2 条非禁止请求参与计算，其中 1 条取消。 - 取消率为 (1 / 2) = 0.50
2.解题思路
1）我们首先要筛选出来没有banned的client_id，driver_id，即使用WITH AS 构建一个base表，使用WHERE IN。。。。AND。。进行筛选
2）构建临时表后其实问题以及解决大半了，只要根据日期分组并且通过WHERE 筛选日期符合要求的结果即可
3）最后还有一个关键点就是SUM(IF(status = 'completed', 0, 1))统计完成订单，这也算使用IF函数的一个小套路，求符合条件的和
3.代码实现
# Write your MySQL query statement below
WITH base AS (
SELECT *
FROM Trips
WHERE client_id IN (
SELECT users_id FROM Users WHERE banned = 'No'
) AND
driver_id IN (
SELECT users_id FROM Users WHERE banned = 'No'
)
)
SELECT
request_at AS Day,
ROUND(SUM(IF(status = 'completed', 0, 1))/COUNT(id), 2) AS 'Cancellation Rate'
FROM base
WHERE request_at BETWEEN '2013-10-01' AND '2013-10-03'
GROUP BY request_at
420
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
