力扣上的SQL复健记录2

题1,

学生表: 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 排序。

查询结构格式如下所示。

示例 1：

输入：
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         |
+------------+--------------+
输出：
+------------+--------------+--------------+----------------+
| 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              |
+------------+--------------+--------------+----------------+

这题虽然是简单题，但是还是做了很久，首先第一点是对交叉连接使用不熟练，一开始在研究给每个学生赋予所有科目行，在未注意到Subjects这个表的作用下尝试了一堆外连接方法，浪费了很多时间，后来回忆到了自己昨天才做到的自称说没有怎么见过的交叉连接，会进行笛卡尔积的连接方式，所以第一步其实很简单：

select * from Students cross join Subjects

这样就能构造起示例列表前三列的样式。

第二步自然就是将第三张表的信息补充到该表，使用左连接：

(select * from Students cross join Subjects) a
left join
(select student_id,subject_name,count(student_id) as attended_exams from Examinations
group by student_id,subject_name) b
on a.student_id=b.student_id and a.subject_name=b.subject_name

这里需要注意，这张表是根据两列定位的，连接时不仅要保证id对应，也要保证学科对应；

最后，将其中的null值转化为0并排序：

select a.student_id,a.student_name,a.subject_name,
if(attended_exams is null,0,attended_exams) as  attended_exams
from
(select * from Students cross join Subjects) a
left join
(select student_id,subject_name,count(student_id) as attended_exams from Examinations
group by student_id,subject_name) b
on a.student_id=b.student_id and a.subject_name=b.subject_name
order by student_id,a.subject_name

这里再复习一下最基本的if语句用法，if（a,b,c）其中a为逻辑值，若a=1;则返回b，a=0，则返回c。

题2.

编写一个解决方案，找出至少有五个直接下属的经理。

以 任意顺序 返回结果表。

查询结果格式如下所示。

示例 1:

输入: 
Employee 表:
+-----+-------+------------+-----------+
| id  | name  | department | managerId |
+-----+-------+------------+-----------+
| 101 | John  | A          | Null      |
| 102 | Dan   | A          | 101       |
| 103 | James | A          | 101       |
| 104 | Amy   | A          | 101       |
| 105 | Anne  | A          | 101       |
| 106 | Ron   | B          | 101       |
+-----+-------+------------+-----------+
输出: 
+------+
| name |
+------+
| John |
+------+

这是最初的写法：

select name
from Employee a left join (select managerId as id from Employee) b on a.id=b.id
where count(a.id)>=5
group by a.id

其中group的用法报错，然后改成了having就对了

select name
from Employee a left join (select managerId as id from Employee) b on a.id=b.id
group by a.id
having count(a.id)>=5

原因是where后使用了需要聚合的函数count，违背了where的筛选顺序。

题3.

编写解决方案，找出所有影片描述为 非 boring (不无聊) 的并且 id 为奇数 的影片。

返回结果按 rating 降序排列。

select id,movie,description,rating
from cinema
where id mod 2 = 1 and description  not in ("boring")
order by rating desc

mysql的排序中默认为升序，其后附加asc同样为升序，附加desc为降序。