- sql语句：表【学生|科目|成绩】查询每个学生成绩最好的科目和成绩
CREATE TABLE `scores` (
`name` text,
`subject` text,
`score` int(255) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
select * from scores
where (name,score) in
(select name,Max(score) from scores group by name);
参考：https://www.zhihu.com/question/302413652
175. 组合两个表
SQL架构
表1: Person
+-------------+---------+
| 列名 | 类型 |
+-------------+---------+
| PersonId | int |
| FirstName | varchar |
| LastName | varchar |
+-------------+---------+
PersonId 是上表主键
表2: Address
+-------------+---------+
| 列名 | 类型 |
+-------------+---------+
| AddressId | int |
| PersonId | int |
| City | varchar |
| State | varchar |
+-------------+---------+
AddressId 是上表主键
编写一个 SQL 查询，满足条件：无论 person 是否有地址信息，都需要基于上述两表提供 person 的以下信息：
FirstName, LastName, City, State
*/
/*
方法：使用 outer join
算法
因为表 Address 中的 personId 是表 Person 的外关键字，所以我们可以连接这两个表来获取一个人的地址信息。
考虑到可能不是每个人都有地址信息，我们应该使用 outer join 而不是默认的 inner join。
*/
select FirstName, LastName, City, State
from Person left join Address
on Person.PersonId = Address.PersonId
;
/*
left join 和 inner join的区别？
假设你要join两个没有重复列的表，这是最常见的情况：
inner join A 和 B 获得的是A和B的交集(intersect),即韦恩图(venn diagram) 相交的部分.
outer join A和B获得的是A和B的并集(union), 即韦恩图(venn diagram)的所有部分.
示例
假定有两张表，每张表只有一列，列数据如下：
A B
- -
1 3
2 4
3 5
4 6
注意(1,2)是A表独有的，(3,4) 两张共有， (5,6)是B独有的。
Inner join
使用等号进行inner join以获得两表的交集，即共有的行。
select * from a INNER JOIN b on a.a = b.b;
select a.*,b.* from a,b where a.a = b.b;
a | b
--+--
3 | 3
4 | 4
Left outer join
left outer join 除了获得B表中符合条件的列外，还将获得A表所有的列。
select * from a LEFT OUTER JOIN b on a.a = b.b;
select a.*,b.* from a,b where a.a = b.b(+);
a | b
--+-----
1 | null
2 | null
3 | 3
4 | 4
Full outer join
full outer join 得到A和B的交集,即A和B中所有的行.。如果A中的行在B中没有对应的部分,B的部分将是 null, 反之亦然。
select * from a FULL OUTER JOIN b on a.a = b.b;
a | b
-----+-----
1 | null
2 | null
3 | 3
4 | 4
null | 6
null | 5
176. 第二高的薪水
SQL架构
编写一个 SQL 查询，获取 Employee 表中第二高的薪水（Salary） 。
+----+--------+
| Id | Salary |
+----+--------+
| 1 | 100 |
| 2 | 200 |
| 3 | 300 |
+----+--------+
例如上述 Employee 表，SQL查询应该返回 200 作为第二高的薪水。如果不存在第二高的薪水，那么查询应返回 null。
+---------------------+
| SecondHighestSalary |
+---------------------+
| 200 |
+---------------------+
select (select distinct Salary from `Employee` order by Salary desc limit 1 offset 1) as SecondHighestSalary
177. 第N高的薪水
编写一个 SQL 查询，获取 Employee 表中第 n 高的薪水（Salary）。
+----+--------+
| Id | Salary |
+----+--------+
| 1 | 100 |
| 2 | 200 |
| 3 | 300 |
+----+--------+
例如上述 Employee 表，n = 2 时，应返回第二高的薪水 200。如果不存在第 n 高的薪水，那么查询应返回 null。
+------------------------+
| getNthHighestSalary(2) |
+------------------------+
| 200 |
+------------------------+
*/
CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
SET N := N-1;
RETURN (
# Write your MySQL query statement below.
SELECT
salary
FROM
employee
GROUP BY
salary
ORDER BY
salary DESC
LIMIT N, 1
);
END
排名是数据库中的一个经典题目，实际上又根据排名的具体细节可分为3种场景：
连续排名，例如薪水3000、2000、2000、1000排名结果为1-2-3-4，体现同薪不同名，排名类似于编号
同薪同名但总排名不连续，例如同样的薪水分布，排名结果为1-2-2-4
同薪同名且总排名连续，同样的薪水排名结果为1-2-2-3
不同的应用场景可能需要不同的排名结果，也意味着不同的查询策略。本题的目标是实现第三种排名方式下的第N个结果，且是全局排名，不存在分组的问题，实际上还要相对简单一些。
值得一提的是：在Oracle等数据库中有窗口函数，可非常容易实现这些需求，而MySQL直到8.0版本也引入相关函数。最新OJ环境已更新至8.0版本，可直接使用窗口函数。
为此，本文提出以下几种解决思路，仅供参考。
如果有意可关注文末个人公众号，查看一篇更为详尽的分组排名问题。
思路1：单表查询
由于本题不存在分组排序，只需返回全局第N高的一个，所以自然想到的想法是用order by排序加limit限制得到。需要注意两个细节：
同薪同名且不跳级的问题，解决办法是用group by按薪水分组后再order by
排名第N高意味着要跳过N-1个薪水，由于无法直接用limit N-1，所以需先在函数开头处理N为N=N-1。
注：这里不能直接用limit N-1是因为limit和offset字段后面只接受正整数（意味着0、负数、小数都不行）或者单一变量（意味着不能用表达式），也就是说想取一条，limit 2-1、limit 1.1这类的写法都是报错的。
注：这种解法形式最为简洁直观，但仅适用于查询全局排名问题，如果要求各分组的每个第N名，则该方法不适用；而且也不能处理存在重复值的情况。
代码1
CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
SET N := N-1;
RETURN (
# Write your MySQL query statement below.
SELECT
salary
FROM
employee
GROUP BY
salary
ORDER BY
salary DESC
LIMIT N, 1
);
END
思路2：子查询
排名第N的薪水意味着该表中存在N-1个比其更高的薪水
注意这里的N-1个更高的薪水是指去重后的N-1个，实际对应人数可能不止N-1个
最后返回的薪水也应该去重，因为可能不止一个薪水排名第N
由于对于每个薪水的where条件都要执行一遍子查询，注定其效率低下
代码2
CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
RETURN (
# Write your MySQL query statement below.
SELECT
DISTINCT e.salary
FROM
employee e
WHERE
(SELECT count(DISTINCT salary) FROM employee WHERE salary>e.salary) = N-1
);
END
思路3：自连接
一般来说，能用子查询解决的问题也能用连接解决。具体到本题：
两表自连接，连接条件设定为表1的salary小于表2的salary
以表1的salary分组，统计表1中每个salary分组后对应表2中salary唯一值个数，即去重
限定步骤2中having 计数个数为N-1，即实现了该分组中表1salary排名为第N个
考虑N=1的特殊情形(特殊是因为N-1=0，计数要求为0)，此时不存在满足条件的记录数，但仍需返回结果，所以连接用left join
如果仅查询薪水这一项值，那么不用left join当然也是可以的，只需把连接条件放宽至小于等于、同时查询个数设置为N即可。因为连接条件含等号，所以一定不为空，用join即可。
注：个人认为无需考虑N<=0的情形，毕竟无实际意义。
代码3
mysqlmysql
CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
RETURN (
# Write your MySQL query statement below.
SELECT
e1.salary
FROM
employee e1 JOIN employee e2 ON e1.salary <= e2.salary
GROUP BY
e1.salary
HAVING
count(DISTINCT e2.salary) = N
);
END
思路4：笛卡尔积
当然，可以很容易将思路2中的代码改为笛卡尔积连接形式，其执行过程实际上一致的，甚至MySQL执行时可能会优化成相同的查询语句。
代码4
CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
RETURN (
# Write your MySQL query statement below.
SELECT
e1.salary
FROM
employee e1, employee e2
WHERE
e1.salary <= e2.salary
GROUP BY
e1.salary
HAVING
count(DISTINCT e2.salary) = N
);
END
思路5：自定义变量
以上方法2-4中均存在两表关联的问题，表中记录数少时尚可接受，当记录数量较大且无法建立合适索引时，实测速度会比较慢，用算法复杂度来形容大概是O(n^2)量级（实际还与索引有关）。那么，用下面的自定义变量的方法可实现O(2*n)量级，速度会快得多，且与索引无关。
自定义变量实现按薪水降序后的数据排名，同薪同名不跳级，即3000、2000、2000、1000排名后为1、2、2、3；
对带有排名信息的临时表二次筛选，得到排名为N的薪水；
因为薪水排名为N的记录可能不止1个，用distinct去重
代码5
CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
RETURN (
# Write your MySQL query statement below.
SELECT
DISTINCT salary
FROM
(SELECT
salary, @r:=IF(@p=salary, @r, @r+1) AS rnk, @p:= salary
FROM
employee, (SELECT @r:=0, @p:=NULL)init
ORDER BY
salary DESC) tmp
WHERE rnk = N
);
END
思路6：窗口函数
实际上，在mysql8.0中有相关的内置函数，而且考虑了各种排名问题：
row_number(): 同薪不同名，相当于行号，例如3000、2000、2000、1000排名后为1、2、3、4
rank(): 同薪同名，有跳级，例如3000、2000、2000、1000排名后为1、2、2、4
dense_rank(): 同薪同名，无跳级，例如3000、2000、2000、1000排名后为1、2、2、3
ntile(): 分桶排名，即首先按桶的个数分出第一二三桶，然后各桶内从1排名，实际不是很常用
显然，本题是要用第三个函数。
另外这三个函数必须要要与其搭档over()配套使用，over()中的参数常见的有两个，分别是
partition by，按某字段切分
order by，与常规order by用法一致，也区分ASC(默认)和DESC，因为排名总得有个依据
注：下面代码仅在mysql8.0以上版本可用，最新OJ已支持。
代码6
CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
RETURN (
# Write your MySQL query statement below.
SELECT
DISTINCT salary
FROM
(SELECT
salary, dense_rank() over(ORDER BY salary DESC) AS rnk
FROM
employee) tmp
WHERE rnk = N
);
END
至此，可以总结MySQL查询的一般性思路是：
能用单表优先用单表，即便是需要用group by、order by、limit等，效率一般也比多表高
不能用单表时优先用连接，连接是SQL中非常强大的用法，小表驱动大表+建立合适索引+合理运用连接条件，基本上连接可以解决绝大部分问题。但join级数不宜过多，毕竟是一个接近指数级增长的关联效果
能不用子查询、笛卡尔积尽量不用，虽然很多情况下MySQL优化器会将其优化成连接方式的执行过程，但效率仍然难以保证
自定义变量在复杂SQL实现中会很有用，例如LeetCode中困难级别的数据库题目很多都需要借助自定义变量实现
如果MySQL版本允许，某些带聚合功能的查询需求应用窗口函数是一个最优选择。除了经典的获取3种排名信息，还有聚合函数、向前向后取值、百分位等，具体可参考官方指南。以下是官方给出的几个窗口函数的介绍：
最后的最后再补充一点，本题将查询语句封装成一个自定义函数并给出了模板，实际上是降低了对函数语法的书写要求和难度，而且提供的函数写法也较为精简。然而，自定义函数更一般化和常用的写法应该是分三步：
定义变量接收返回值
执行查询条件，并赋值给相应变量
返回结果
例如以解法5为例，如下写法可能更适合函数初学者理解和掌握：
CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
# i 定义变量接收返回值
DECLARE ans INT DEFAULT NULL;
# ii 执行查询语句，并赋值给相应变量
SELECT
DISTINCT salary INTO ans
FROM
(SELECT
salary, @r:=IF(@p=salary, @r, @r+1) AS rnk, @p:= salary
FROM
employee, (SELECT @r:=0, @p:=NULL)init
ORDER BY
salary DESC) tmp
WHERE rnk = N;
# iii 返回查询结果，注意函数名中是 returns，而函数体中是 return
RETURN ans;
END
*/
178. 分数排名
SQL架构
编写一个 SQL 查询来实现分数排名。
如果两个分数相同，则两个分数排名（Rank）相同。请注意，平分后的下一个名次应该是下一个连续的整数值。换句话说，名次之间不应该有“间隔”。
+----+-------+
| Id | Score |
+----+-------+
| 1 | 3.50 |
| 2 | 3.65 |
| 3 | 4.00 |
| 4 | 3.85 |
| 5 | 4.00 |
| 6 | 3.65 |
+----+-------+
例如，根据上述给定的 Scores 表，你的查询应该返回（按分数从高到低排列）：
+-------+------+
| Score | Rank |
+-------+------+
| 4.00 | 1 |
| 4.00 | 1 |
| 3.85 | 2 |
| 3.65 | 3 |
| 3.65 | 3 |
| 3.50 | 4 |
+-------+------+
重要提示：对于 MySQL 解决方案，如果要转义用作列名的保留字，可以在关键字之前和之后使用撇号。例如 `Rank`
*/
/*
最后的结果包含两个部分，第一部分是降序排列的分数，第二部分是每个分数对应的排名。
第一部分不难写：
select a.Score as Score
from Scores a
order by a.Score DESC
比较难的是第二部分。假设现在给你一个分数X，如何算出它的排名Rank呢？
我们可以先提取出大于等于X的所有分数集合H，将H去重后的元素个数就是X的排名。比如你考了99分，但最高的就只有99分，那么去重之后集合H里就只有99一个元素，个数为1，因此你的Rank为1。
先提取集合H：
select b.Score from Scores b where b.Score >= X;
我们要的是集合H去重之后的元素个数，因此升级为：
select count(distinct b.Score) from Scores b where b.Score >= X as Rank;
而从结果的角度来看，第二部分的Rank是对应第一部分的分数来的，所以这里的X就是上面的a.Score，把两部分结合在一起为：
select a.Score as Score,
(select count(distinct b.Score) from Scores b where b.Score >= a.Score) as Rank
from Scores a
order by a.Score DESC
select a.Score as Score,
(select count(distinct b.Score) from Scores b where b.Score >= a.Score) as `Rank`
from Scores a
order by a.Score desc
/*
180. 连续出现的数字
SQL架构
编写一个 SQL 查询，查找所有至少连续出现三次的数字。
+----+-----+
| Id | Num |
+----+-----+
| 1 | 1 |
| 2 | 1 |
| 3 | 1 |
| 4 | 2 |
| 5 | 1 |
| 6 | 2 |
| 7 | 2 |
+----+-----+
例如，给定上面的 Logs 表， 1 是唯一连续出现至少三次的数字。
+-----------------+
| ConsecutiveNums |
+-----------------+
| 1 |
+-----------------+
*/
/*
方法：用 DISTINCT 和 WHERE 语句
算法
连续出现的意味着相同数字的 Id 是连着的，由于这题问的是至少连续出现 3 次，我们使用 Logs 并检查是否有 3 个连续的相同数字。
SELECT *
FROM
Logs l1,
Logs l2,
Logs l3
WHERE
l1.Id = l2.Id - 1
AND l2.Id = l3.Id - 1
AND l1.Num = l2.Num
AND l2.Num = l3.Num
;
Id Num Id Num Id Num
1 1 2 1 3 1
注意：前两列来自 l1 ，接下来两列来自 l2 ，最后两列来自 l3 。
然后我们从上表中选择任意的 Num 获得想要的答案。同时我们需要添加关键字 DISTINCT ，因为如果一个数字连续出现超过 3 次，会返回重复元素。
MySQL
SELECT DISTINCT
l1.Num AS ConsecutiveNums
FROM
Logs l1,
Logs l2,
Logs l3
WHERE
l1.Id = l2.Id - 1
AND l2.Id = l3.Id - 1
AND l1.Num = l2.Num
AND l2.Num = l3.Num
;
*/
select distinct l1.Num as ConsecutiveNums
from
Logs l1,
Logs l2,
Logs l3
where
l1.Id = l2.Id - 1
and l2.Id = l3.Id - 1
and l1.Num = l2.Num
and l2.Num = l3.Num;
/*
181. 超过经理收入的员工
SQL架构
Employee 表包含所有员工，他们的经理也属于员工。每个员工都有一个 Id，此外还有一列对应员工的经理的 Id。
+----+-------+--------+-----------+
| Id | Name | Salary | ManagerId |
+----+-------+--------+-----------+
| 1 | Joe | 70000 | 3 |
| 2 | Henry | 80000 | 4 |
| 3 | Sam | 60000 | NULL |
| 4 | Max | 90000 | NULL |
+----+-------+--------+-----------+
给定 Employee 表，编写一个 SQL 查询，该查询可以获取收入超过他们经理的员工的姓名。
在上面的表格中，Joe 是唯一一个收入超过他的经理的员工。
+----------+
| Employee |
+----------+
| Joe |
+----------+
*/
select a.Name as Employee from Employee as a join Employee as b on a.ManagerId = b.Id;
/*
方法 1：使用 WHERE 语句
算法
如下面表格所示，表格里存有每个雇员经理的信息，我们也许需要从这个表里获取两次信息。
SELECT *
FROM Employee AS a, Employee AS b
;
注意：关键词 'AS' 是可选的
Id Name Salary ManagerId Id Name Salary ManagerId
1 Joe 70000 3 1 Joe 70000 3
2 Henry 80000 4 1 Joe 70000 3
3 Sam 60000 1 Joe 70000 3
4 Max 90000 1 Joe 70000 3
1 Joe 70000 3 2 Henry 80000 4
2 Henry 80000 4 2 Henry 80000 4
3 Sam 60000 2 Henry 80000 4
4 Max 90000 2 Henry 80000 4
1 Joe 70000 3 3 Sam 60000
2 Henry 80000 4 3 Sam 60000
3 Sam 60000 3 Sam 60000
4 Max 90000 3 Sam 60000
1 Joe 70000 3 4 Max 90000
2 Henry 80000 4 4 Max 90000
3 Sam 60000 4 Max 90000
4 Max 90000 4 Max 90000
前 3 列来自表格 a ，后 3 列来自表格 b
从两个表里使用 Select 语句可能会导致产生 笛卡尔乘积 。在这种情况下，输出会产生 4*4=16 个记录。然而我们只对雇员工资高于经理的人感兴趣。所以我们应该用 WHERE 语句加 2 个判断条件。
SELECT
*
FROM
Employee AS a,
Employee AS b
WHERE
a.ManagerId = b.Id
AND a.Salary > b.Salary
;
Id Name Salary ManagerId Id Name Salary ManagerId
1 Joe 70000 3 3 Sam 60000
由于我们只需要输出雇员的名字，所以我们修改一下上面的代码，得到最终解法：
MySQL
SELECT
a.Name AS 'Employee'
FROM
Employee AS a,
Employee AS b
WHERE
a.ManagerId = b.Id
AND a.Salary > b.Salary
;
方法 2：使用 JOIN 语句
算法
实际上， JOIN 是一个更常用也更有效的将表连起来的办法，我们使用 ON 来指明条件。
SELECT
a.NAME AS Employee
FROM Employee AS a JOIN Employee AS b
ON a.ManagerId = b.Id
AND a.Salary > b.Salary
;
*/
/*
182. 查找重复的电子邮箱
SQL架构
编写一个 SQL 查询，查找 Person 表中所有重复的电子邮箱。
示例：
+----+---------+
| Id | Email |
+----+---------+
| 1 | a@b.com |
| 2 | c@d.com |
| 3 | a@b.com |
+----+---------+
根据以上输入，你的查询应返回以下结果：
+---------+
| Email |
+---------+
| a@b.com |
+---------+
说明：所有电子邮箱都是小写字母。
*/
select Email from
(select Email,count(Email) as num from Person group by Email) as statistics
where num > 1
select Email from Person group by Email having count(*) > 1
/*
方法一：使用 GROUP BY 和临时表
算法
重复的电子邮箱存在多次。要计算每封电子邮件的存在次数，我们可以使用以下代码。
MySQL
select Email, count(Email) as num
from Person
group by Email;
| Email | num |
|---------|-----|
| a@b.com | 2 |
| c@d.com | 1 |
以此作为临时表，我们可以得到下面的解决方案。
MySQL
select Email from
(
select Email, count(Email) as num
from Person
group by Email
) as statistic
where num > 1
;
方法二：使用 GROUP BY 和 HAVING 条件
向 GROUP BY 添加条件的一种更常用的方法是使用 HAVING 子句，该子句更为简单高效。
所以我们可以将上面的解决方案重写为：
MySQL
select Email
from Person
group by Email
having count(Email) > 1;
*/
/*
183. 从不订购的客户
SQL架构
某网站包含两个表，Customers 表和 Orders 表。编写一个 SQL 查询，找出所有从不订购任何东西的客户。
Customers 表：
+----+-------+
| Id | Name |
+----+-------+
| 1 | Joe |
| 2 | Henry |
| 3 | Sam |
| 4 | Max |
+----+-------+
Orders 表：
+----+------------+
| Id | CustomerId |
+----+------------+
| 1 | 3 |
| 2 | 1 |
+----+------------+
例如给定上述表格，你的查询应返回：
+-----------+
| Customers |
+-----------+
| Henry |
| Max |
+-----------+
*/
select customers.name as 'Customers' from customers
where customers.id not in (
select customerid from orders
)
/*
方法：使用子查询和 NOT IN 子句
算法
如果我们有一份曾经订购过的客户名单，就很容易知道谁从未订购过。
我们可以使用下面的代码来获得这样的列表。
select customerid from orders;
然后，我们可以使用 NOT IN 查询不在此列表中的客户。
MySQL
select customers.name as 'Customers'
from customers
where customers.id not in
(
select customerid from orders
);
*/
/*
184. 部门工资最高的员工
SQL架构
Employee 表包含所有员工信息，每个员工有其对应的 Id, salary 和 department Id。
+----+-------+--------+--------------+
| Id | Name | Salary | DepartmentId |
+----+-------+--------+--------------+
| 1 | Joe | 70000 | 1 |
| 2 | Jim | 90000 | 1 |
| 3 | Henry | 80000 | 2 |
| 4 | Sam | 60000 | 2 |
| 5 | Max | 90000 | 1 |
+----+-------+--------+--------------+
Department 表包含公司所有部门的信息。
+----+----------+
| Id | Name |
+----+----------+
| 1 | IT |
| 2 | Sales |
+----+----------+
编写一个 SQL 查询，找出每个部门工资最高的员工。对于上述表，您的 SQL 查询应返回以下行（行的顺序无关紧要）。
+------------+----------+--------+
| Department | Employee | Salary |
+------------+----------+--------+
| IT | Max | 90000 |
| IT | Jim | 90000 |
| Sales | Henry | 80000 |
+------------+----------+--------+
解释：
Max 和 Jim 在 IT 部门的工资都是最高的，Henry 在销售部的工资最高。
*/
select department.name as 'Department',
employee.name as 'Employee',
salary
from department join employee on employee.departmentid = department.id
where (employee.departmentid,salary) in
(select departmentid ,MAX(salary) from employee group by departmentid)
/*
方法：使用 JOIN 和 IN 语句
算法
因为 Employee 表包含 Salary 和 DepartmentId 字段，我们可以以此在部门内查询最高工资。
SELECT
DepartmentId, MAX(Salary)
FROM
Employee
GROUP BY DepartmentId;
注意：有可能有多个员工同时拥有最高工资，所以最好在这个查询中不包含雇员名字的信息。
| DepartmentId | MAX(Salary) |
|--------------|-------------|
| 1 | 90000 |
| 2 | 80000 |
然后，我们可以把表 Employee 和 Department 连接，再在这张临时表里用 IN 语句查询部门名字和工资的关系。
MySQL
SELECT
Department.name AS 'Department',
Employee.name AS 'Employee',
Salary
FROM
Employee
JOIN
Department ON Employee.DepartmentId = Department.Id
WHERE
(Employee.DepartmentId , Salary) IN
( SELECT
DepartmentId, MAX(Salary)
FROM
Employee
GROUP BY DepartmentId
)
;
| Department | Employee | Salary |
|------------|----------|--------|
| Sales | Henry | 80000 |
| IT | Max | 90000 |
*/
/*
185. 部门工资前三高的所有员工
SQL架构
Employee 表包含所有员工信息，每个员工有其对应的工号 Id，姓名 Name，工资 Salary 和部门编号 DepartmentId 。
+----+-------+--------+--------------+
| Id | Name | Salary | DepartmentId |
+----+-------+--------+--------------+
| 1 | Joe | 85000 | 1 |
| 2 | Henry | 80000 | 2 |
| 3 | Sam | 60000 | 2 |
| 4 | Max | 90000 | 1 |
| 5 | Janet | 69000 | 1 |
| 6 | Randy | 85000 | 1 |
| 7 | Will | 70000 | 1 |
+----+-------+--------+--------------+
Department 表包含公司所有部门的信息。
+----+----------+
| Id | Name |
+----+----------+
| 1 | IT |
| 2 | Sales |
+----+----------+
编写一个 SQL 查询，找出每个部门获得前三高工资的所有员工。例如，根据上述给定的表，查询结果应返回：
+------------+----------+--------+
| Department | Employee | Salary |
+------------+----------+--------+
| IT | Max | 90000 |
| IT | Randy | 85000 |
| IT | Joe | 85000 |
| IT | Will | 70000 |
| Sales | Henry | 80000 |
| Sales | Sam | 60000 |
+------------+----------+--------+
解释：
IT 部门中，Max 获得了最高的工资，Randy 和 Joe 都拿到了第二高的工资，Will 的工资排第三。
销售部门（Sales）只有两名员工，Henry 的工资最高，Sam 的工资排第二。
*/
/*
方法：使用 JOIN 和子查询
算法
公司里前 3 高的薪水意味着有不超过 3 个工资比这些值大。
select e1.Name as 'Employee', e1.Salary
from Employee e1
where 3 >
(
select count(distinct e2.Salary)
from Employee e2
where e2.Salary > e1.Salary
)
;
在这个代码里，我们统计了有多少人的工资比 e1.Salary 高，所以样例的输出应该如下所示。
| Employee | Salary |
|----------|--------|
| Henry | 80000 |
| Max | 90000 |
| Randy | 85000 |
然后，我们需要把表 Employee 和表 Department 连接来获得部门信息。
MySQL
SELECT
d.Name AS 'Department', e1.Name AS 'Employee', e1.Salary
FROM
Employee e1
JOIN
Department d ON e1.DepartmentId = d.Id
WHERE
3 > (SELECT
COUNT(DISTINCT e2.Salary)
FROM
Employee e2
WHERE
e2.Salary > e1.Salary
AND e1.DepartmentId = e2.DepartmentId
)
;
| Department | Employee | Salary |
|------------|----------|--------|
| IT | Joe | 70000 |
| Sales | Henry | 80000 |
| Sales | Sam | 60000 |
| IT | Max | 90000 |
| IT | Randy | 85000 |
*/
select d.Name as 'Department',e.Name as 'Employee',e.Salary from employee as e join department as d
on e.DepartmentId = d.id
where 3 > ( select count(distinct e2.salary) from employee as e2 where e2.salary > e.salary
and e2.DepartmentId = e.DepartmentId)
/*
196. 删除重复的电子邮箱
编写一个 SQL 查询，来删除 Person 表中所有重复的电子邮箱，重复的邮箱里只保留 Id 最小 的那个。
+----+------------------+
| Id | Email |
+----+------------------+
| 1 | john@example.com |
| 2 | bob@example.com |
| 3 | john@example.com |
+----+------------------+
Id 是这个表的主键。
例如，在运行你的查询语句之后，上面的 Person 表应返回以下几行:
+----+------------------+
| Id | Email |
+----+------------------+
| 1 | john@example.com |
| 2 | bob@example.com |
+----+------------------+
提示：
执行 SQL 之后，输出是整个 Person 表。
使用 delete 语句。
*/
/*
方法：使用 DELETE 和 WHERE 子句
算法
我们可以使用以下代码，将此表与它自身在电子邮箱列中连接起来。
MySQL
SELECT p1.*
FROM Person p1,
Person p2
WHERE
p1.Email = p2.Email
;
然后我们需要找到其他记录中具有相同电子邮件地址的更大 ID。所以我们可以像这样给 WHERE 子句添加一个新的条件。
MySQL
SELECT p1.*
FROM Person p1,
Person p2
WHERE
p1.Email = p2.Email AND p1.Id > p2.Id
;
因为我们已经得到了要删除的记录，所以我们最终可以将该语句更改为 DELETE。
MySQL
DELETE p1 FROM Person p1,
Person p2
WHERE
p1.Email = p2.Email AND p1.Id > p2.Id
*/
/*
方法：使用 DELETE 和 WHERE 子句
算法
我们可以使用以下代码，将此表与它自身在电子邮箱列中连接起来。
MySQL
SELECT p1.*
FROM Person p1,
Person p2
WHERE
p1.Email = p2.Email
;
然后我们需要找到其他记录中具有相同电子邮件地址的更大 ID。所以我们可以像这样给 WHERE 子句添加一个新的条件。
MySQL
SELECT p1.*
FROM Person p1,
Person p2
WHERE
p1.Email = p2.Email AND p1.Id > p2.Id
;
因为我们已经得到了要删除的记录，所以我们最终可以将该语句更改为 DELETE。
MySQL
DELETE p1 FROM Person p1,
Person p2
WHERE
p1.Email = p2.Email AND p1.Id > p2.Id
官方sql是下面这样的👇
MySQL
DELETE p1 FROM Person p1,
Person p2
WHERE
p1.Email = p2.Email AND p1.Id > p2.Id
当然这个sql是很ok的，简洁清晰，且用了自连接的方式。
有慢查询优化经验的同学会清楚，在实际生产中，面对千万上亿级别的数据，连接的效率往往最高，因为用到索引的概率较高。
因此，建议学习使用官方的题解，但是有两点，可能需要再解释下：
1、DELETE p1
在DELETE官方文档中，给出了这一用法，比如下面这个DELETE语句👇
DELETE t1 FROM t1 LEFT JOIN t2 ON t1.id=t2.id WHERE t2.id IS NULL;
这种DELETE方式很陌生，竟然和SELETE的写法类似。它涉及到t1和t2两张表，DELETE t1表示要删除t1的一些记录，具体删哪些，就看WHERE条件，满足就删；
这里删的是t1表中，跟t2匹配不上的那些记录。
所以，官方sql中，DELETE p1就表示从p1表中删除满足WHERE条件的记录。
2、p1.Id > p2.Id
继续之前，先简单看一下表的连接过程，这个搞懂了，理解WHERE条件就简单了👇
a. 从驱动表（左表）取出N条记录；
b. 拿着这N条记录，依次到被驱动表（右表）查找满足WHERE条件的记录；
所以，官方sql的过程就是👇
先把Person表搬过来( •̀∀•́ )
a. 从表p1取出3条记录；
b. 拿着第1条记录去表p2查找满足WHERE的记录，代入该条件p1.Email = p2.Email AND p1.Id > p2.Id后，发现没有满足的，所以不用删掉记录1；
c. 记录2同理；
d. 拿着第3条记录去表p2查找满足WHERE的记录，发现有一条记录满足，所以要从p1删掉记录3；
e. 3条记录遍历完，删掉了1条记录，这个DELETE也就结束了。
*/
delete p1 from Person as p1,Person p2 where p1.Email = p2.Email and p1.Id > p2.Id
/*
197. 上升的温度
SQL架构
表 Weather
+---------------+---------+
| Column Name | Type |
+---------------+---------+
| id | int |
| recordDate | date |
| temperature | int |
+---------------+---------+
id 是这个表的主键
该表包含特定日期的温度信息
编写一个 SQL 查询，来查找与之前（昨天的）日期相比温度更高的所有日期的 id 。
返回结果 不要求顺序 。
查询结果格式如下例：
Weather
+----+------------+-------------+
| id | recordDate | Temperature |
+----+------------+-------------+
| 1 | 2015-01-01 | 10 |
| 2 | 2015-01-02 | 25 |
| 3 | 2015-01-03 | 20 |
| 4 | 2015-01-04 | 30 |
+----+------------+-------------+
Result table:
+----+
| id |
+----+
| 2 |
| 4 |
+----+
2015-01-02 的温度比前一天高（10 -> 25）
2015-01-04 的温度比前一天高（30 -> 20）
*/
/*
方法：使用 JOIN 和 DATEDIFF() 子句
算法
MySQL 使用 DATEDIFF 来比较两个日期类型的值。
因此，我们可以通过将 weather 与自身相结合，并使用 DATEDIFF() 函数。
MySQL
SELECT
weather.id AS 'Id'
FROM
weather
JOIN
weather w ON DATEDIFF(weather.date, w.date) = 1
AND weather.Temperature > w.Temperature
;
*/
select w2.id as Id from Weather w1 , Weather w2 where DATEDIFF(w2.recordDate,w1.recordDate) = 1 and w2.Temperature > w1.Temperature
/*
262. 行程和用户
SQL架构
Trips 表中存所有出租车的行程信息。
每段行程有唯一键 Id，Client_Id 和 Driver_Id 是 Users 表中 Users_Id 的外键。
Status 是枚举类型，枚举成员为 (‘completed’, ‘cancelled_by_driver’, ‘cancelled_by_client’)。
+----+-----------+-----------+---------+--------------------+----------+
| 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 则是一个表示（‘client’, ‘driver’, ‘partner’）的枚举类型。
+----------+--------+--------+
| 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 |
+----------+--------+--------+
写一段 SQL 语句查出 2013年10月1日 至 2013年10月3日 期间非禁止用户的取消率。
基于上表，你的 SQL 语句应返回如下结果，取消率（Cancellation Rate）保留两位小数。
取消率的计算方式如下：(被司机或乘客取消的非禁止用户生成的订单数量) / (非禁止用户生成的订单总数)
+------------+-------------------+
| Day | Cancellation Rate |
+------------+-------------------+
| 2013-10-01 | 0.33 |
| 2013-10-02 | 0.00 |
| 2013-10-03 | 0.50 |
+------------+-------------------+
致谢:
非常感谢 @cak1erlizhou 详细的提供了这道题和相应的测试用例。
*/
/*
统计每天非禁止用户的取消率，需要知道非禁止用户有哪些，总行程数，取消的行程数。
解法一
首先确定被禁止用户的行程记录，再剔除这些行程记录。
行程表中，字段 client_id 和 driver_id，都与用户表中的 users_id 关联。
因此只要 client_id 和 driver_id 中有一个被禁止了，此条行程记录要被剔除。
先说一种错误的找出没被禁止用户行程记录的方法。此方法很有迷惑性。
思路：
if (client_id = users_id 或 driver_id = users_id) 且 users_id没有被禁止
{
此条记录没被禁止。
}
SQL 代码
SELECT *
FROM Trips AS T JOIN Users AS U
ON (T.client_id = U.users_id OR T.driver_id = U.users_id ) AND U.banned ='No'
乍一看，思路是对。其实是错误的。因为，我们不知觉得肯定了一个假设—— client_id 与 driver_id 是相同的。
只有当两者相同时，才能用此条件排除被禁止用户的行程记录。
错误的结果：
+------+-----------+-----------+---------+---------------------+------------+----------+--------+--------+
| Id | Client_Id | Driver_Id | City_Id | STATUS | Request_at | Users_Id | Banned | Role |
+------+-----------+-----------+---------+---------------------+------------+----------+--------+--------+
| 1 | 1 | 10 | 1 | completed | 2013-10-01 | 1 | No | client |
| 1 | 1 | 10 | 1 | completed | 2013-10-01 | 10 | No | driver |
| 2 | 2 | 11 | 1 | cancelled_by_driver | 2013-10-01 | 11 | No | driver |
| 3 | 3 | 12 | 6 | completed | 2013-10-01 | 3 | No | client |
| 3 | 3 | 12 | 6 | completed | 2013-10-01 | 12 | No | driver |
| 4 | 4 | 13 | 6 | cancelled_by_client | 2013-10-01 | 4 | No | client |
| 4 | 4 | 13 | 6 | cancelled_by_client | 2013-10-01 | 13 | No | driver |
| 5 | 1 | 10 | 1 | completed | 2013-10-02 | 1 | No | client |
| 5 | 1 | 10 | 1 | completed | 2013-10-02 | 10 | No | driver |
| 6 | 2 | 11 | 6 | completed | 2013-10-02 | 11 | No | driver |
| 7 | 3 | 12 | 6 | completed | 2013-10-02 | 3 | No | client |
| 7 | 3 | 12 | 6 | completed | 2013-10-02 | 12 | No | driver |
| 8 | 2 | 12 | 12 | completed | 2013-10-03 | 12 | No | driver |
| 9 | 3 | 10 | 12 | completed | 2013-10-03 | 3 | No | client |
| 9 | 3 | 10 | 12 | completed | 2013-10-03 | 10 | No | driver |
| 10 | 4 | 13 | 12 | cancelled_by_driver | 2013-10-03 | 4 | No | client |
| 10 | 4 | 13 | 12 | cancelled_by_driver | 2013-10-03 | 13 | No | driver |
+------+-----------+-----------+---------+---------------------+------------+----------+--------+--------+
结果中，被禁止的 users_id = 2，其行程记录没被剔除掉。
明显， client_id 与 driver_id 不一定相同 。
正确的做法是对 client_id 和 driver_id 各自关联的 users_id，同时检测是否被禁止。
if (client_id = users_id_1 且 users_id_1没被禁止 并且 client_id = users_id_2 且 users_id_2没被禁止){
此条记录没被禁止。
}
SQL 代码：
SELECT *
FROM Trips AS T
JOIN Users AS U1 ON (T.client_id = U1.users_id AND U1.banned ='No')
JOIN Users AS U2 ON (T.driver_id = U2.users_id AND U2.banned ='No')
在此基础上，按日期分组，统计每组的 总行程数，取消的行程数 。
每组的总行程数：COUNT(T.STATUS)。
每组的取消的行程数：
SUM(
IF(T.STATUS = 'completed',0,1)
)
取消率 = 每组的取消的行程数 / 每组的总行程数
完整逻辑为:
SELECT T.request_at AS `Day`,
ROUND(
SUM(
IF(T.STATUS = 'completed',0,1)
)
/
COUNT(T.STATUS),
2
) AS `Cancellation Rate`
FROM Trips AS T
JOIN Users AS U1 ON (T.client_id = U1.users_id AND U1.banned ='No')
JOIN Users AS U2 ON (T.driver_id = U2.users_id AND U2.banned ='No')
WHERE T.request_at BETWEEN '2013-10-01' AND '2013-10-03'
GROUP BY T.request_at
其中 SUM 求和函数，COUNT 计数函数，ROUND 四舍五入函数。
解法二
思路与解法一相同。而采用不同的方法排除掉被禁止用户的行程记录。想到排除，就联想到集合差。
client_id 和 driver_id 的全部为集合 U。被禁止的 users_id 集合为 A。
U 减去 A 的结果为没被禁止的用户。
(
SELECT users_id
FROM users
WHERE banned = 'Yes'
) AS A
好了，先演示一个错误的解法：
行程表连接表 A，排除掉被被禁止的行程。
SELECT *
FROM trips AS T,
(
SELECT users_id
FROM users
WHERE banned = 'Yes'
) AS A
WHERE (T.Client_Id != A.users_id AND T.Driver_Id != A.users_id)
剩下的逻辑与解法一后部分相同，完善后的逻辑为：
SELECT T.request_at AS `Day`,
ROUND(
SUM(
IF(T.STATUS = 'completed',0,1)
)
/
COUNT(T.STATUS),
2
) AS `Cancellation Rate`
FROM trips AS T,
(
SELECT users_id
FROM users
WHERE banned = 'Yes'
) AS A
WHERE (T.Client_Id != A.users_id AND T.Driver_Id != A.users_id) AND T.request_at BETWEEN '2013-10-01' AND '2013-10-03'
GROUP BY T.request_at
很可惜，当表 A 为空时，此方法的结果是空表。但是表 A 为空，可能是有用户但是没有被禁止的用户。因此方法是错误的。
正确的解法是：行程表 left join 表 A 两次，A.users_id 都为 NULL 的行都是没被排除的行。
SELECT *
FROM trips AS T LEFT JOIN
(
SELECT users_id
FROM users
WHERE banned = 'Yes'
) AS A ON (T.Client_Id = A.users_id)
LEFT JOIN (
SELECT users_id
FROM users
WHERE banned = 'Yes'
) AS A1
ON (T.Driver_Id = A1.users_id)
WHERE A.users_id IS NULL AND A1.users_id IS NULL
补上其它部分的逻辑为：
SELECT T.request_at AS `Day`,
ROUND(
SUM(
IF(T.STATUS = 'completed',0,1)
)
/
COUNT(T.STATUS),
2
) AS `Cancellation Rate`
FROM trips AS T LEFT JOIN
(
SELECT users_id
FROM users
WHERE banned = 'Yes'
) AS A ON (T.Client_Id = A.users_id)
LEFT JOIN (
SELECT users_id
FROM users
WHERE banned = 'Yes'
) AS A1
ON (T.Driver_Id = A1.users_id)
WHERE A.users_id IS NULL AND A1.users_id IS NULL AND T.request_at BETWEEN '2013-10-01' AND '2013-10-03'
GROUP BY T.request_at
解法三
与解法二思路相同。找出被禁止的用户后，不再连接行程表和用户表，直接从行程表中排除掉被被禁止用户的行程记录。
被禁止的用户用子查询：
(
SELECT users_id
FROM users
WHERE banned = 'Yes'
)
行程表中 client_id 和 driver_id 都在此子查询结果中的行要剔除掉。
SELECT *
FROM trips AS T
WHERE
T.Client_Id NOT IN (
SELECT users_id
FROM users
WHERE banned = 'Yes'
)
AND
T.Driver_Id NOT IN (
SELECT users_id
FROM users
WHERE banned = 'Yes'
)
补上其它部分：
SELECT T.request_at AS `Day`,
ROUND(
SUM(
IF(T.STATUS = 'completed',0,1)
)
/
COUNT(T.STATUS),
2
) AS `Cancellation Rate`
FROM trips AS T
WHERE
T.Client_Id NOT IN (
SELECT users_id
FROM users
WHERE banned = 'Yes'
)
AND
T.Driver_Id NOT IN (
SELECT users_id
FROM users
WHERE banned = 'Yes'
)
AND T.request_at BETWEEN '2013-10-01' AND '2013-10-03'
GROUP BY T.request_at
*/
select T.request_at as `Day`,
round(sum(if (T.status = 'completed',0,1)) / count(T.status),2) as `Cancellation Rate`
from Trips as T
join Users as U1 on (T.client_id = U1.users_id and U1.banned = 'NO')
join Users as U2 on (T.driver_id = U2.users_id and U2.banned = 'NO')
where T.request_at between '2013-10-01' AND '2013-10-03'
group by T.request_at
select T.request_at as `Day`,
round(sum(if (T.status = 'completed',0,1)) / count(T.status),2) as `Cancellation Rate`
from Trips as T
left join
( select users_id from users where banned = 'Yes') as A on (T.client_id = A.users_id)
left join
(select users_id from users where banned = 'Yes') as A1 on (T.Driver_id = A1.users_id)
where A.users_id is null and A1.users_id is null and T.request_at between '2013-10-01' and '2013-10-03' group by T.request_at
select T.request_at as `Day`,
round(sum(if (T.status = 'completed',0,1)) / count(T.status),2) as `Cancellation Rate`
from Trips as T
where
T.client_id not in (select users_id from users where banned = 'Yes')
and
T.driver_id not in (select users_id from users where banned = 'Yes')
and
T.request_at between '2013-10-01' AND '2013-10-03'
group by T.request_at
/*
595. 大的国家
SQL架构
这里有张 World 表
+-----------------+------------+------------+--------------+---------------+
| name | continent | area | population | gdp |
+-----------------+------------+------------+--------------+---------------+
| Afghanistan | Asia | 652230 | 25500100 | 20343000 |
| Albania | Europe | 28748 | 2831741 | 12960000 |
| Algeria | Africa | 2381741 | 37100000 | 188681000 |
| Andorra | Europe | 468 | 78115 | 3712000 |
| Angola | Africa | 1246700 | 20609294 | 100990000 |
+-----------------+------------+------------+--------------+---------------+
如果一个国家的面积超过 300 万平方公里，或者人口超过 2500 万，那么这个国家就是大国家。
编写一个 SQL 查询，输出表中所有大国家的名称、人口和面积。
例如，根据上表，我们应该输出:
+--------------+-------------+--------------+
| name | population | area |
+--------------+-------------+--------------+
| Afghanistan | 25500100 | 652230 |
| Algeria | 37100000 | 2381741 |
+--------------+-------------+--------------+
*/
/*
方法一：使用 WHERE 子句和 OR【通过】
思路
使用 WHERE 子句过滤所有记录，获得满足条件的国家。
算法
根据定义，大国家至少满足以下两个条件中的一个：
面积超过 300 万平方公里。
人口超过 2500 万。
使用下面语句获得满足条件 1 的大国家。
MySQL
SELECT name, population, area FROM world WHERE area > 3000000
使用下面语句获得满足条件 2 的大国家。
MySQL
SELECT name, population, area FROM world WHERE population > 25000000
使用 OR 将两个子查询合并在一起。
MySQL
MySQL
SELECT
name, population, area
FROM
world
WHERE
area > 3000000 OR population > 25000000
;
方法二：使用 WHERE 子句和 UNION【通过】
算法
该方法思路与 方法一 一样，但是使用 UNION 连接子查询。
MySQL
MySQL
SELECT
name, population, area
FROM
world
WHERE
area > 3000000
UNION
SELECT
name, population, area
FROM
world
WHERE
population > 25000000
;
注：方法二 比 方法一 运行速度更快，但是它们没有太大差别。
*/
select name,population,area
from world
where
area > 3000000
union
select name,population,area
from world
where
population > 25000000
select
name ,population,area
from world
where
area > 3000000 or population > 25000000
/*
596. 超过5名学生的课
SQL架构
有一个courses 表 ，有: student (学生) 和 class (课程)。
请列出所有超过或等于5名学生的课。
例如，表：
+---------+------------+
| student | class |
+---------+------------+
| A | Math |
| B | English |
| C | Math |
| D | Biology |
| E | Math |
| F | Computer |
| G | Math |
| H | Math |
| I | Math |
+---------+------------+
应该输出:
+---------+
| class |
+---------+
| Math |
+---------+
提示：
学生在每个课中不应被重复计算。
*/
/*
方法一：使用 GROUP BY 子句和子查询【通过】
思路
先统计每门课程的学生数量，再从中选择超过 5 名学生的课程。
算法
使用 GROUP BY 和 COUNT 获得每门课程的学生数量。
MySQL
SELECT
class, COUNT(DISTINCT student)
FROM
courses
GROUP BY class
;
注：使用 DISTINCT 防止在同一门课中学生被重复计算。
| class | COUNT(student) |
|----------|----------------|
| Biology | 1 |
| Computer | 1 |
| English | 1 |
| Math | 6 |
使用上面查询结果的临时表进行子查询，筛选学生数量超过 5 的课程。
MySQL
SELECT
class
FROM
(SELECT
class, COUNT(DISTINCT student) AS num
FROM
courses
GROUP BY class) AS temp_table
WHERE
num >= 5
;
注：COUNT(student) 不能直接在 WHERE 子句中使用，这里将其重命名为 num。
方法二：使用 GROUP BY 和 HAVING 条件【通过】
算法
在 GROUP BY 子句后使用 HAVING 条件是实现子查询的一种更加简单直接的方法。
MySQL
MySQL
SELECT
class
FROM
courses
GROUP BY class
HAVING COUNT(DISTINCT student) >= 5
;
*/
select class
from
(select class ,count(distinct student) as number from courses group by class) as a
where a.number >= 5
select class from courses group by class having count(distinct student) >= 5
/*
601. 体育馆的人流量
SQL架构
表：Stadium
+---------------+---------+
| Column Name | Type |
+---------------+---------+
| id | int |
| visit_date | date |
| people | int |
+---------------+---------+
visit_date 是表的主键
每日人流量信息被记录在这三列信息中：序号 (id)、日期 (visit_date)、 人流量 (people)
每天只有一行记录，日期随着 id 的增加而增加
编写一个 SQL 查询以找出每行的人数大于或等于 100 且 id 连续的三行或更多行记录。
返回按 visit_date 升序排列的结果表。
查询结果格式如下所示。
Stadium table:
+------+------------+-----------+
| id | visit_date | people |
+------+------------+-----------+
| 1 | 2017-01-01 | 10 |
| 2 | 2017-01-02 | 109 |
| 3 | 2017-01-03 | 150 |
| 4 | 2017-01-04 | 99 |
| 5 | 2017-01-05 | 145 |
| 6 | 2017-01-06 | 1455 |
| 7 | 2017-01-07 | 199 |
| 8 | 2017-01-09 | 188 |
+------+------------+-----------+
Result table:
+------+------------+-----------+
| id | visit_date | people |
+------+------------+-----------+
| 5 | 2017-01-05 | 145 |
| 6 | 2017-01-06 | 1455 |
| 7 | 2017-01-07 | 199 |
| 8 | 2017-01-09 | 188 |
+------+------------+-----------+
id 为 5、6、7、8 的四行 id 连续，并且每行都有 >= 100 的人数记录。
请注意，即使第 7 行和第 8 行的 visit_date 不是连续的，输出也应当包含第 8 行，因为我们只需要考虑 id 连续的记录。
不输出 id 为 2 和 3 的行，因为至少需要三条 id 连续的记录。
*/
/*
方法：使用 JOIN 和 WHERE 子句【通过】
思路
在表 stadium 中查询人流量超过 100 的记录，将查询结果与其自身的临时表连接，再使用 WHERE 子句获得满足条件的记录。
算法
第一步：查询人流量超过 100 的记录，然后将结果与其自身的临时表连接。
MySQL
select distinct t1.*
from stadium t1, stadium t2, stadium t3
where t1.people >= 100 and t2.people >= 100 and t3.people >= 100
;
| id | date | people | id | date | people | id | date | people |
|----|------------|--------|----|------------|--------|----|------------|--------|
| 2 | 2017-01-02 | 109 | 2 | 2017-01-02 | 109 | 2 | 2017-01-02 | 109 |
| 3 | 2017-01-03 | 150 | 2 | 2017-01-02 | 109 | 2 | 2017-01-02 | 109 |
| 5 | 2017-01-05 | 145 | 2 | 2017-01-02 | 109 | 2 | 2017-01-02 | 109 |
| 6 | 2017-01-06 | 1455 | 2 | 2017-01-02 | 109 | 2 | 2017-01-02 | 109 |
| 7 | 2017-01-07 | 199 | 2 | 2017-01-02 | 109 | 2 | 2017-01-02 | 109 |
| 8 | 2017-01-08 | 188 | 2 | 2017-01-02 | 109 | 2 | 2017-01-02 | 109 |
| 2 | 2017-01-02 | 109 | 3 | 2017-01-03 | 150 | 2 | 2017-01-02 | 109 |
| 3 | 2017-01-03 | 150 | 3 | 2017-01-03 | 150 | 2 | 2017-01-02 | 109 |
| 5 | 2017-01-05 | 145 | 3 | 2017-01-03 | 150 | 2 | 2017-01-02 | 109 |
| 6 | 2017-01-06 | 1455 | 3 | 2017-01-03 | 150 | 2 | 2017-01-02 | 109 |
| 7 | 2017-01-07 | 199 | 3 | 2017-01-03 | 150 | 2 | 2017-01-02 | 109 |
| 8 | 2017-01-08 | 188 | 3 | 2017-01-03 | 150 | 2 | 2017-01-02 | 109 |
| 2 | 2017-01-02 | 109 | 5 | 2017-01-05 | 145 | 2 | 2017-01-02 | 109 |
| 3 | 2017-01-03 | 150 | 5 | 2017-01-05 | 145 | 2 | 2017-01-02 | 109 |
| 5 | 2017-01-05 | 145 | 5 | 2017-01-05 | 145 | 2 | 2017-01-02 | 109 |
| 6 | 2017-01-06 | 1455 | 5 | 2017-01-05 | 145 | 2 | 2017-01-02 | 109 |
| 7 | 2017-01-07 | 199 | 5 | 2017-01-05 | 145 | 2 | 2017-01-02 | 109 |
| 8 | 2017-01-08 | 188 | 5 | 2017-01-05 | 145 | 2 | 2017-01-02 | 109 |
| 2 | 2017-01-02 | 109 | 6 | 2017-01-06 | 1455 | 2 | 2017-01-02 | 109 |
| 3 | 2017-01-03 | 150 | 6 | 2017-01-06 | 1455 | 2 | 2017-01-02 | 109 |
| 5 | 2017-01-05 | 145 | 6 | 2017-01-06 | 1455 | 2 | 2017-01-02 | 109 |
| 6 | 2017-01-06 | 1455 | 6 | 2017-01-06 | 1455 | 2 | 2017-01-02 | 109 |
| 7 | 2017-01-07 | 199 | 6 | 2017-01-06 | 1455 | 2 | 2017-01-02 | 109 |
| 8 | 2017-01-08 | 188 | 6 | 2017-01-06 | 1455 | 2 | 2017-01-02 | 109 |
| 2 | 2017-01-02 | 109 | 7 | 2017-01-07 | 199 | 2 | 2017-01-02 | 109 |
| 3 | 2017-01-03 | 150 | 7 | 2017-01-07 | 199 | 2 | 2017-01-02 | 109 |
| 5 | 2017-01-05 | 145 | 7 | 2017-01-07 | 199 | 2 | 2017-01-02 | 109 |
| 6 | 2017-01-06 | 1455 | 7 | 2017-01-07 | 199 | 2 | 2017-01-02 | 109 |
| 7 | 2017-01-07 | 199 | 7 | 2017-01-07 | 199 | 2 | 2017-01-02 | 109 |
| 8 | 2017-01-08 | 188 | 7 | 2017-01-07 | 199 | 2 | 2017-01-02 | 109 |
| 2 | 2017-01-02 | 109 | 8 | 2017-01-08 | 188 | 2 | 2017-01-02 | 109 |
| 3 | 2017-01-03 | 150 | 8 | 2017-01-08 | 188 | 2 | 2017-01-02 | 109 |
| 5 | 2017-01-05 | 145 | 8 | 2017-01-08 | 188 | 2 | 2017-01-02 | 109 |
| 6 | 2017-01-06 | 1455 | 8 | 2017-01-08 | 188 | 2 | 2017-01-02 | 109 |
| 7 | 2017-01-07 | 199 | 8 | 2017-01-08 | 188 | 2 | 2017-01-02 | 109 |
| 8 | 2017-01-08 | 188 | 8 | 2017-01-08 | 188 | 2 | 2017-01-02 | 109 |
| 2 | 2017-01-02 | 109 | 2 | 2017-01-02 | 109 | 3 | 2017-01-03 | 150 |
| 3 | 2017-01-03 | 150 | 2 | 2017-01-02 | 109 | 3 | 2017-01-03 | 150 |
| 5 | 2017-01-05 | 145 | 2 | 2017-01-02 | 109 | 3 | 2017-01-03 | 150 |
| 6 | 2017-01-06 | 1455 | 2 | 2017-01-02 | 109 | 3 | 2017-01-03 | 150 |
| 7 | 2017-01-07 | 199 | 2 | 2017-01-02 | 109 | 3 | 2017-01-03 | 150 |
| 8 | 2017-01-08 | 188 | 2 | 2017-01-02 | 109 | 3 | 2017-01-03 | 150 |
| 2 | 2017-01-02 | 109 | 3 | 2017-01-03 | 150 | 3 | 2017-01-03 | 150 |
| 3 | 2017-01-03 | 150 | 3 | 2017-01-03 | 150 | 3 | 2017-01-03 | 150 |
| 5 | 2017-01-05 | 145 | 3 | 2017-01-03 | 150 | 3 | 2017-01-03 | 150 |
| 6 | 2017-01-06 | 1455 | 3 | 2017-01-03 | 150 | 3 | 2017-01-03 | 150 |
| 7 | 2017-01-07 | 199 | 3 | 2017-01-03 | 150 | 3 | 2017-01-03 | 150 |
| 8 | 2017-01-08 | 188 | 3 | 2017-01-03 | 150 | 3 | 2017-01-03 | 150 |
| 2 | 2017-01-02 | 109 | 5 | 2017-01-05 | 145 | 3 | 2017-01-03 | 150 |
| 3 | 2017-01-03 | 150 | 5 | 2017-01-05 | 145 | 3 | 2017-01-03 | 150 |
| 5 | 2017-01-05 | 145 | 5 | 2017-01-05 | 145 | 3 | 2017-01-03 | 150 |
| 6 | 2017-01-06 | 1455 | 5 | 2017-01-05 | 145 | 3 | 2017-01-03 | 150 |
| 7 | 2017-01-07 | 199 | 5 | 2017-01-05 | 145 | 3 | 2017-01-03 | 150 |
| 8 | 2017-01-08 | 188 | 5 | 2017-01-05 | 145 | 3 | 2017-01-03 | 150 |
| 2 | 2017-01-02 | 109 | 6 | 2017-01-06 | 1455 | 3 | 2017-01-03 | 150 |
| 3 | 2017-01-03 | 150 | 6 | 2017-01-06 | 1455 | 3 | 2017-01-03 | 150 |
| 5 | 2017-01-05 | 145 | 6 | 2017-01-06 | 1455 | 3 | 2017-01-03 | 150 |
| 6 | 2017-01-06 | 1455 | 6 | 2017-01-06 | 1455 | 3 | 2017-01-03 | 150 |
| 7 | 2017-01-07 | 199 | 6 | 2017-01-06 | 1455 | 3 | 2017-01-03 | 150 |
| 8 | 2017-01-08 | 188 | 6 | 2017-01-06 | 1455 | 3 | 2017-01-03 | 150 |
| 2 | 2017-01-02 | 109 | 7 | 2017-01-07 | 199 | 3 | 2017-01-03 | 150 |
| 3 | 2017-01-03 | 150 | 7 | 2017-01-07 | 199 | 3 | 2017-01-03 | 150 |
| 5 | 2017-01-05 | 145 | 7 | 2017-01-07 | 199 | 3 | 2017-01-03 | 150 |
| 6 | 2017-01-06 | 1455 | 7 | 2017-01-07 | 199 | 3 | 2017-01-03 | 150 |
| 7 | 2017-01-07 | 199 | 7 | 2017-01-07 | 199 | 3 | 2017-01-03 | 150 |
| 8 | 2017-01-08 | 188 | 7 | 2017-01-07 | 199 | 3 | 2017-01-03 | 150 |
| 2 | 2017-01-02 | 109 | 8 | 2017-01-08 | 188 | 3 | 2017-01-03 | 150 |
| 3 | 2017-01-03 | 150 | 8 | 2017-01-08 | 188 | 3 | 2017-01-03 | 150 |
| 5 | 2017-01-05 | 145 | 8 | 2017-01-08 | 188 | 3 | 2017-01-03 | 150 |
| 6 | 2017-01-06 | 1455 | 8 | 2017-01-08 | 188 | 3 | 2017-01-03 | 150 |
| 7 | 2017-01-07 | 199 | 8 | 2017-01-08 | 188 | 3 | 2017-01-03 | 150 |
| 8 | 2017-01-08 | 188 | 8 | 2017-01-08 | 188 | 3 | 2017-01-03 | 150 |
| 2 | 2017-01-02 | 109 | 2 | 2017-01-02 | 109 | 5 | 2017-01-05 | 145 |
| 3 | 2017-01-03 | 150 | 2 | 2017-01-02 | 109 | 5 | 2017-01-05 | 145 |
| 5 | 2017-01-05 | 145 | 2 | 2017-01-02 | 109 | 5 | 2017-01-05 | 145 |
| 6 | 2017-01-06 | 1455 | 2 | 2017-01-02 | 109 | 5 | 2017-01-05 | 145 |
| 7 | 2017-01-07 | 199 | 2 | 2017-01-02 | 109 | 5 | 2017-01-05 | 145 |
| 8 | 2017-01-08 | 188 | 2 | 2017-01-02 | 109 | 5 | 2017-01-05 | 145 |
| 2 | 2017-01-02 | 109 | 3 | 2017-01-03 | 150 | 5 | 2017-01-05 | 145 |
| 3 | 2017-01-03 | 150 | 3 | 2017-01-03 | 150 | 5 | 2017-01-05 | 145 |
| 5 | 2017-01-05 | 145 | 3 | 2017-01-03 | 150 | 5 | 2017-01-05 | 145 |
| 6 | 2017-01-06 | 1455 | 3 | 2017-01-03 | 150 | 5 | 2017-01-05 | 145 |
| 7 | 2017-01-07 | 199 | 3 | 2017-01-03 | 150 | 5 | 2017-01-05 | 145 |
| 8 | 2017-01-08 | 188 | 3 | 2017-01-03 | 150 | 5 | 2017-01-05 | 145 |
| 2 | 2017-01-02 | 109 | 5 | 2017-01-05 | 145 | 5 | 2017-01-05 | 145 |
| 3 | 2017-01-03 | 150 | 5 | 2017-01-05 | 145 | 5 | 2017-01-05 | 145 |
| 5 | 2017-01-05 | 145 | 5 | 2017-01-05 | 145 | 5 | 2017-01-05 | 145 |
| 6 | 2017-01-06 | 1455 | 5 | 2017-01-05 | 145 | 5 | 2017-01-05 | 145 |
| 7 | 2017-01-07 | 199 | 5 | 2017-01-05 | 145 | 5 | 2017-01-05 | 145 |
| 8 | 2017-01-08 | 188 | 5 | 2017-01-05 | 145 | 5 | 2017-01-05 | 145 |
| 2 | 2017-01-02 | 109 | 6 | 2017-01-06 | 1455 | 5 | 2017-01-05 | 145 |
| 3 | 2017-01-03 | 150 | 6 | 2017-01-06 | 1455 | 5 | 2017-01-05 | 145 |
| 5 | 2017-01-05 | 145 | 6 | 2017-01-06 | 1455 | 5 | 2017-01-05 | 145 |
| 6 | 2017-01-06 | 1455 | 6 | 2017-01-06 | 1455 | 5 | 2017-01-05 | 145 |
| 7 | 2017-01-07 | 199 | 6 | 2017-01-06 | 1455 | 5 | 2017-01-05 | 145 |
| 8 | 2017-01-08 | 188 | 6 | 2017-01-06 | 1455 | 5 | 2017-01-05 | 145 |
| 2 | 2017-01-02 | 109 | 7 | 2017-01-07 | 199 | 5 | 2017-01-05 | 145 |
| 3 | 2017-01-03 | 150 | 7 | 2017-01-07 | 199 | 5 | 2017-01-05 | 145 |
| 5 | 2017-01-05 | 145 | 7 | 2017-01-07 | 199 | 5 | 2017-01-05 | 145 |
| 6 | 2017-01-06 | 1455 | 7 | 2017-01-07 | 199 | 5 | 2017-01-05 | 145 |
| 7 | 2017-01-07 | 199 | 7 | 2017-01-07 | 199 | 5 | 2017-01-05 | 145 |
| 8 | 2017-01-08 | 188 | 7 | 2017-01-07 | 199 | 5 | 2017-01-05 | 145 |
| 2 | 2017-01-02 | 109 | 8 | 2017-01-08 | 188 | 5 | 2017-01-05 | 145 |
| 3 | 2017-01-03 | 150 | 8 | 2017-01-08 | 188 | 5 | 2017-01-05 | 145 |
| 5 | 2017-01-05 | 145 | 8 | 2017-01-08 | 188 | 5 | 2017-01-05 | 145 |
| 6 | 2017-01-06 | 1455 | 8 | 2017-01-08 | 188 | 5 | 2017-01-05 | 145 |
| 7 | 2017-01-07 | 199 | 8 | 2017-01-08 | 188 | 5 | 2017-01-05 | 145 |
| 8 | 2017-01-08 | 188 | 8 | 2017-01-08 | 188 | 5 | 2017-01-05 | 145 |
| 2 | 2017-01-02 | 109 | 2 | 2017-01-02 | 109 | 6 | 2017-01-06 | 1455 |
| 3 | 2017-01-03 | 150 | 2 | 2017-01-02 | 109 | 6 | 2017-01-06 | 1455 |
| 5 | 2017-01-05 | 145 | 2 | 2017-01-02 | 109 | 6 | 2017-01-06 | 1455 |
| 6 | 2017-01-06 | 1455 | 2 | 2017-01-02 | 109 | 6 | 2017-01-06 | 1455 |
| 7 | 2017-01-07 | 199 | 2 | 2017-01-02 | 109 | 6 | 2017-01-06 | 1455 |
| 8 | 2017-01-08 | 188 | 2 | 2017-01-02 | 109 | 6 | 2017-01-06 | 1455 |
| 2 | 2017-01-02 | 109 | 3 | 2017-01-03 | 150 | 6 | 2017-01-06 | 1455 |
| 3 | 2017-01-03 | 150 | 3 | 2017-01-03 | 150 | 6 | 2017-01-06 | 1455 |
| 5 | 2017-01-05 | 145 | 3 | 2017-01-03 | 150 | 6 | 2017-01-06 | 1455 |
| 6 | 2017-01-06 | 1455 | 3 | 2017-01-03 | 150 | 6 | 2017-01-06 | 1455 |
| 7 | 2017-01-07 | 199 | 3 | 2017-01-03 | 150 | 6 | 2017-01-06 | 1455 |
| 8 | 2017-01-08 | 188 | 3 | 2017-01-03 | 150 | 6 | 2017-01-06 | 1455 |
| 2 | 2017-01-02 | 109 | 5 | 2017-01-05 | 145 | 6 | 2017-01-06 | 1455 |
| 3 | 2017-01-03 | 150 | 5 | 2017-01-05 | 145 | 6 | 2017-01-06 | 1455 |
| 5 | 2017-01-05 | 145 | 5 | 2017-01-05 | 145 | 6 | 2017-01-06 | 1455 |
| 6 | 2017-01-06 | 1455 | 5 | 2017-01-05 | 145 | 6 | 2017-01-06 | 1455 |
| 7 | 2017-01-07 | 199 | 5 | 2017-01-05 | 145 | 6 | 2017-01-06 | 1455 |
| 8 | 2017-01-08 | 188 | 5 | 2017-01-05 | 145 | 6 | 2017-01-06 | 1455 |
| 2 | 2017-01-02 | 109 | 6 | 2017-01-06 | 1455 | 6 | 2017-01-06 | 1455 |
| 3 | 2017-01-03 | 150 | 6 | 2017-01-06 | 1455 | 6 | 2017-01-06 | 1455 |
| 5 | 2017-01-05 | 145 | 6 | 2017-01-06 | 1455 | 6 | 2017-01-06 | 1455 |
| 6 | 2017-01-06 | 1455 | 6 | 2017-01-06 | 1455 | 6 | 2017-01-06 | 1455 |
| 7 | 2017-01-07 | 199 | 6 | 2017-01-06 | 1455 | 6 | 2017-01-06 | 1455 |
| 8 | 2017-01-08 | 188 | 6 | 2017-01-06 | 1455 | 6 | 2017-01-06 | 1455 |
| 2 | 2017-01-02 | 109 | 7 | 2017-01-07 | 199 | 6 | 2017-01-06 | 1455 |
| 3 | 2017-01-03 | 150 | 7 | 2017-01-07 | 199 | 6 | 2017-01-06 | 1455 |
| 5 | 2017-01-05 | 145 | 7 | 2017-01-07 | 199 | 6 | 2017-01-06 | 1455 |
| 6 | 2017-01-06 | 1455 | 7 | 2017-01-07 | 199 | 6 | 2017-01-06 | 1455 |
| 7 | 2017-01-07 | 199 | 7 | 2017-01-07 | 199 | 6 | 2017-01-06 | 1455 |
| 8 | 2017-01-08 | 188 | 7 | 2017-01-07 | 199 | 6 | 2017-01-06 | 1455 |
| 2 | 2017-01-02 | 109 | 8 | 2017-01-08 | 188 | 6 | 2017-01-06 | 1455 |
| 3 | 2017-01-03 | 150 | 8 | 2017-01-08 | 188 | 6 | 2017-01-06 | 1455 |
| 5 | 2017-01-05 | 145 | 8 | 2017-01-08 | 188 | 6 | 2017-01-06 | 1455 |
| 6 | 2017-01-06 | 1455 | 8 | 2017-01-08 | 188 | 6 | 2017-01-06 | 1455 |
| 7 | 2017-01-07 | 199 | 8 | 2017-01-08 | 188 | 6 | 2017-01-06 | 1455 |
| 8 | 2017-01-08 | 188 | 8 | 2017-01-08 | 188 | 6 | 2017-01-06 | 1455 |
| 2 | 2017-01-02 | 109 | 2 | 2017-01-02 | 109 | 7 | 2017-01-07 | 199 |
| 3 | 2017-01-03 | 150 | 2 | 2017-01-02 | 109 | 7 | 2017-01-07 | 199 |
| 5 | 2017-01-05 | 145 | 2 | 2017-01-02 | 109 | 7 | 2017-01-07 | 199 |
| 6 | 2017-01-06 | 1455 | 2 | 2017-01-02 | 109 | 7 | 2017-01-07 | 199 |
| 7 | 2017-01-07 | 199 | 2 | 2017-01-02 | 109 | 7 | 2017-01-07 | 199 |
| 8 | 2017-01-08 | 188 | 2 | 2017-01-02 | 109 | 7 | 2017-01-07 | 199 |
| 2 | 2017-01-02 | 109 | 3 | 2017-01-03 | 150 | 7 | 2017-01-07 | 199 |
| 3 | 2017-01-03 | 150 | 3 | 2017-01-03 | 150 | 7 | 2017-01-07 | 199 |
| 5 | 2017-01-05 | 145 | 3 | 2017-01-03 | 150 | 7 | 2017-01-07 | 199 |
| 6 | 2017-01-06 | 1455 | 3 | 2017-01-03 | 150 | 7 | 2017-01-07 | 199 |
| 7 | 2017-01-07 | 199 | 3 | 2017-01-03 | 150 | 7 | 2017-01-07 | 199 |
| 8 | 2017-01-08 | 188 | 3 | 2017-01-03 | 150 | 7 | 2017-01-07 | 199 |
| 2 | 2017-01-02 | 109 | 5 | 2017-01-05 | 145 | 7 | 2017-01-07 | 199 |
| 3 | 2017-01-03 | 150 | 5 | 2017-01-05 | 145 | 7 | 2017-01-07 | 199 |
| 5 | 2017-01-05 | 145 | 5 | 2017-01-05 | 145 | 7 | 2017-01-07 | 199 |
| 6 | 2017-01-06 | 1455 | 5 | 2017-01-05 | 145 | 7 | 2017-01-07 | 199 |
| 7 | 2017-01-07 | 199 | 5 | 2017-01-05 | 145 | 7 | 2017-01-07 | 199 |
| 8 | 2017-01-08 | 188 | 5 | 2017-01-05 | 145 | 7 | 2017-01-07 | 199 |
| 2 | 2017-01-02 | 109 | 6 | 2017-01-06 | 1455 | 7 | 2017-01-07 | 199 |
| 3 | 2017-01-03 | 150 | 6 | 2017-01-06 | 1455 | 7 | 2017-01-07 | 199 |
| 5 | 2017-01-05 | 145 | 6 | 2017-01-06 | 1455 | 7 | 2017-01-07 | 199 |
| 6 | 2017-01-06 | 1455 | 6 | 2017-01-06 | 1455 | 7 | 2017-01-07 | 199 |
| 7 | 2017-01-07 | 199 | 6 | 2017-01-06 | 1455 | 7 | 2017-01-07 | 199 |
| 8 | 2017-01-08 | 188 | 6 | 2017-01-06 | 1455 | 7 | 2017-01-07 | 199 |
| 2 | 2017-01-02 | 109 | 7 | 2017-01-07 | 199 | 7 | 2017-01-07 | 199 |
| 3 | 2017-01-03 | 150 | 7 | 2017-01-07 | 199 | 7 | 2017-01-07 | 199 |
| 5 | 2017-01-05 | 145 | 7 | 2017-01-07 | 199 | 7 | 2017-01-07 | 199 |
| 6 | 2017-01-06 | 1455 | 7 | 2017-01-07 | 199 | 7 | 2017-01-07 | 199 |
| 7 | 2017-01-07 | 199 | 7 | 2017-01-07 | 199 | 7 | 2017-01-07 | 199 |
| 8 | 2017-01-08 | 188 | 7 | 2017-01-07 | 199 | 7 | 2017-01-07 | 199 |
| 2 | 2017-01-02 | 109 | 8 | 2017-01-08 | 188 | 7 | 2017-01-07 | 199 |
| 3 | 2017-01-03 | 150 | 8 | 2017-01-08 | 188 | 7 | 2017-01-07 | 199 |
| 5 | 2017-01-05 | 145 | 8 | 2017-01-08 | 188 | 7 | 2017-01-07 | 199 |
| 6 | 2017-01-06 | 1455 | 8 | 2017-01-08 | 188 | 7 | 2017-01-07 | 199 |
| 7 | 2017-01-07 | 199 | 8 | 2017-01-08 | 188 | 7 | 2017-01-07 | 199 |
| 8 | 2017-01-08 | 188 | 8 | 2017-01-08 | 188 | 7 | 2017-01-07 | 199 |
| 2 | 2017-01-02 | 109 | 2 | 2017-01-02 | 109 | 8 | 2017-01-08 | 188 |
| 3 | 2017-01-03 | 150 | 2 | 2017-01-02 | 109 | 8 | 2017-01-08 | 188 |
| 5 | 2017-01-05 | 145 | 2 | 2017-01-02 | 109 | 8 | 2017-01-08 | 188 |
| 6 | 2017-01-06 | 1455 | 2 | 2017-01-02 | 109 | 8 | 2017-01-08 | 188 |
| 7 | 2017-01-07 | 199 | 2 | 2017-01-02 | 109 | 8 | 2017-01-08 | 188 |
| 8 | 2017-01-08 | 188 | 2 | 2017-01-02 | 109 | 8 | 2017-01-08 | 188 |
| 2 | 2017-01-02 | 109 | 3 | 2017-01-03 | 150 | 8 | 2017-01-08 | 188 |
| 3 | 2017-01-03 | 150 | 3 | 2017-01-03 | 150 | 8 | 2017-01-08 | 188 |
| 5 | 2017-01-05 | 145 | 3 | 2017-01-03 | 150 | 8 | 2017-01-08 | 188 |
| 6 | 2017-01-06 | 1455 | 3 | 2017-01-03 | 150 | 8 | 2017-01-08 | 188 |
| 7 | 2017-01-07 | 199 | 3 | 2017-01-03 | 150 | 8 | 2017-01-08 | 188 |
| 8 | 2017-01-08 | 188 | 3 | 2017-01-03 | 150 | 8 | 2017-01-08 | 188 |
| 2 | 2017-01-02 | 109 | 5 | 2017-01-05 | 145 | 8 | 2017-01-08 | 188 |
| 3 | 2017-01-03 | 150 | 5 | 2017-01-05 | 145 | 8 | 2017-01-08 | 188 |
| 5 | 2017-01-05 | 145 | 5 | 2017-01-05 | 145 | 8 | 2017-01-08 | 188 |
| 6 | 2017-01-06 | 1455 | 5 | 2017-01-05 | 145 | 8 | 2017-01-08 | 188 |
| 7 | 2017-01-07 | 199 | 5 | 2017-01-05 | 145 | 8 | 2017-01-08 | 188 |
| 8 | 2017-01-08 | 188 | 5 | 2017-01-05 | 145 | 8 | 2017-01-08 | 188 |
| 2 | 2017-01-02 | 109 | 6 | 2017-01-06 | 1455 | 8 | 2017-01-08 | 188 |
| 3 | 2017-01-03 | 150 | 6 | 2017-01-06 | 1455 | 8 | 2017-01-08 | 188 |
| 5 | 2017-01-05 | 145 | 6 | 2017-01-06 | 1455 | 8 | 2017-01-08 | 188 |
| 6 | 2017-01-06 | 1455 | 6 | 2017-01-06 | 1455 | 8 | 2017-01-08 | 188 |
| 7 | 2017-01-07 | 199 | 6 | 2017-01-06 | 1455 | 8 | 2017-01-08 | 188 |
| 8 | 2017-01-08 | 188 | 6 | 2017-01-06 | 1455 | 8 | 2017-01-08 | 188 |
| 2 | 2017-01-02 | 109 | 7 | 2017-01-07 | 199 | 8 | 2017-01-08 | 188 |
| 3 | 2017-01-03 | 150 | 7 | 2017-01-07 | 199 | 8 | 2017-01-08 | 188 |
| 5 | 2017-01-05 | 145 | 7 | 2017-01-07 | 199 | 8 | 2017-01-08 | 188 |
| 6 | 2017-01-06 | 1455 | 7 | 2017-01-07 | 199 | 8 | 2017-01-08 | 188 |
| 7 | 2017-01-07 | 199 | 7 | 2017-01-07 | 199 | 8 | 2017-01-08 | 188 |
| 8 | 2017-01-08 | 188 | 7 | 2017-01-07 | 199 | 8 | 2017-01-08 | 188 |
| 2 | 2017-01-02 | 109 | 8 | 2017-01-08 | 188 | 8 | 2017-01-08 | 188 |
| 3 | 2017-01-03 | 150 | 8 | 2017-01-08 | 188 | 8 | 2017-01-08 | 188 |
| 5 | 2017-01-05 | 145 | 8 | 2017-01-08 | 188 | 8 | 2017-01-08 | 188 |
| 6 | 2017-01-06 | 1455 | 8 | 2017-01-08 | 188 | 8 | 2017-01-08 | 188 |
| 7 | 2017-01-07 | 199 | 8 | 2017-01-08 | 188 | 8 | 2017-01-08 | 188 |
| 8 | 2017-01-08 | 188 | 8 | 2017-01-08 | 188 | 8 | 2017-01-08 | 188 |
共有 6 天人流量超过 100 人，笛卡尔积 后有 216（666） 条记录。
前 3 列来自表 t1，中间 3 列来自表 t2，最后 3 列来自表 t3。
表 t1，t2 和 t3 相同，需要考虑添加哪些条件能够得到想要的结果。以 t1 为例，它有可能是高峰期的第 1 天，第 2 天，或第 3 天。
t1 是高峰期第 1 天：(t1.id - t2.id = 1 and t1.id - t3.id = 2 and t2.id - t3.id =1) -- t1, t2, t3
t1 是高峰期第 2 天：(t2.id - t1.id = 1 and t2.id - t3.id = 2 and t1.id - t3.id =1) -- t2, t1, t3
t1 是高峰期第 3 天：(t3.id - t2.id = 1 and t2.id - t1.id =1 and t3.id - t1.id = 2) -- t3, t2, t1
MySQL
select t1.*
from stadium t1, stadium t2, stadium t3
where t1.people >= 100 and t2.people >= 100 and t3.people >= 100
and
(
(t1.id - t2.id = 1 and t1.id - t3.id = 2 and t2.id - t3.id =1) -- t1, t2, t3
or
(t2.id - t1.id = 1 and t2.id - t3.id = 2 and t1.id - t3.id =1) -- t2, t1, t3
or
(t3.id - t2.id = 1 and t2.id - t1.id =1 and t3.id - t1.id = 2) -- t3, t2, t1
)
;
| id | date | people |
|----|------------|--------|
| 7 | 2017-01-07 | 199 |
| 6 | 2017-01-06 | 1455 |
| 8 | 2017-01-08 | 188 |
| 7 | 2017-01-07 | 199 |
| 5 | 2017-01-05 | 145 |
| 6 | 2017-01-06 | 1455 |
可以看到查询结果中存在重复的记录，再使用 DISTINCT 去重。
MySQL
MySQL
select distinct t1.*
from stadium t1, stadium t2, stadium t3
where t1.people >= 100 and t2.people >= 100 and t3.people >= 100
and
(
(t1.id - t2.id = 1 and t1.id - t3.id = 2 and t2.id - t3.id =1) -- t1, t2, t3
or
(t2.id - t1.id = 1 and t2.id - t3.id = 2 and t1.id - t3.id =1) -- t2, t1, t3
or
(t3.id - t2.id = 1 and t2.id - t1.id =1 and t3.id - t1.id = 2) -- t3, t2, t1
)
order by t1.id
;
*/
select distinct t1.* from
stadium t1,stadium t2, stadium t3
where t1.people >= 100 and t2.people >= 100 and t3.people >= 100
and (
(t1.id - t2.id = 1 and t1.id - t3.id = 2 and t2.id - t3.id = 1)
or
(t2.id - t1.id = 1 and t2.id - t3.id = 2 and t1.id - t3.id =1)
or
(t3.id - t2.id = 1 and t2.id - t1.id =1 and t3.id - t1.id = 2)
)
order by t1.id
/*
620. 有趣的电影
SQL架构
某城市开了一家新的电影院，吸引了很多人过来看电影。该电影院特别注意用户体验，专门有个 LED显示板做电影推荐，上面公布着影评和相关电影描述。
作为该电影院的信息部主管，您需要编写一个 SQL查询，找出所有影片描述为非 boring (不无聊) 的并且 id 为奇数 的影片，结果请按等级 rating 排列。
例如，下表 cinema:
+---------+-----------+--------------+-----------+
| id | movie | description | rating |
+---------+-----------+--------------+-----------+
| 1 | War | great 3D | 8.9 |
| 2 | Science | fiction | 8.5 |
| 3 | irish | boring | 6.2 |
| 4 | Ice song | Fantacy | 8.6 |
| 5 | House card| Interesting| 9.1 |
+---------+-----------+--------------+-----------+
对于上面的例子，则正确的输出是为：
+---------+-----------+--------------+-----------+
| id | movie | description | rating |
+---------+-----------+--------------+-----------+
| 5 | House card| Interesting| 9.1 |
| 1 | War | great 3D | 8.9 |
+---------+-----------+--------------+-----------+
*/
select * from cinema where mod(id,2) = 1 and description != 'boring' order by rating desc
/*
626. 换座位
SQL架构
小美是一所中学的信息科技老师，她有一张 seat 座位表，平时用来储存学生名字和与他们相对应的座位 id。
其中纵列的 id 是连续递增的
小美想改变相邻俩学生的座位。
你能不能帮她写一个 SQL query 来输出小美想要的结果呢？
示例：
+---------+---------+
| id | student |
+---------+---------+
| 1 | Abbot |
| 2 | Doris |
| 3 | Emerson |
| 4 | Green |
| 5 | Jeames |
+---------+---------+
假如数据输入的是上表，则输出结果如下：
+---------+---------+
| id | student |
+---------+---------+
| 1 | Doris |
| 2 | Abbot |
| 3 | Green |
| 4 | Emerson |
| 5 | Jeames |
+---------+---------+
注意：
如果学生人数是奇数，则不需要改变最后一个同学的座位。
*/
/*
方法一：使用 CASE【通过】
算法
对于所有座位 id 是奇数的学生，修改其 id 为 id+1，如果最后一个座位 id 也是奇数，则最后一个座位 id 不修改。对于所有座位 id 是偶数的学生，修改其 id 为 id-1。
首先查询座位的数量。
MySQL
SELECT
COUNT(*) AS counts
FROM
seat
然后使用 CASE 条件和 MOD 函数修改每个学生的座位 id。
MySQL
MySQL
SELECT
(CASE
WHEN MOD(id, 2) != 0 AND counts != id THEN id + 1
WHEN MOD(id, 2) != 0 AND counts = id THEN id
ELSE id - 1
END) AS id,
student
FROM
seat,
(SELECT
COUNT(*) AS counts
FROM
seat) AS seat_counts
ORDER BY id ASC;
方法二：使用位操作和 COALESCE()【通过】
算法
使用 (id+1)^1-1 计算交换后每个学生的座位 id。
MySQL
SELECT id, (id+1)^1-1, student FROM seat;
| id | (id+1)^1-1 | student |
|----|------------|---------|
| 1 | 2 | Abbot |
| 2 | 1 | Doris |
| 3 | 4 | Emerson |
| 4 | 3 | Green |
| 5 | 6 | Jeames |
然后连接原来的座位表和更新 id 后的座位表。
MySQL
SELECT
*
FROM
seat s1
LEFT JOIN
seat s2 ON (s1.id+1)^1-1 = s2.id
ORDER BY s1.id;
| id | student | id | student |
|----|---------|----|---------|
| 1 | Abbot | 2 | Doris |
| 2 | Doris | 1 | Abbot |
| 3 | Emerson | 4 | Green |
| 4 | Green | 3 | Emerson |
| 5 | Jeames | | |
注：前两列来自表 s1，后两列来自表 s2。
最后输出 s1.id 和 s2.student。
但是 id=5 的学生，s1.student 正确，s2.student 为 NULL。因此使用 COALESCE() 函数为最后一行记录生成正确的输出。
MySQL
MySQL
SELECT
s1.id, COALESCE(s2.student, s1.student) AS student
FROM
seat s1
LEFT JOIN
seat s2 ON ((s1.id + 1) ^ 1) - 1 = s2.id
ORDER BY s1.id;
*/
select
( case
when mod(id,2) != 0 and id != seat_counts.counts then id + 1
when mod(id,2) != 0 and id = seat_counts.counts then id
else id - 1
end)
as id,student
from seat,(select count(*) as counts from seat) as seat_counts
order by id asc
/*
COALESCE(value1,value2,...);
COALESCE函数需要许多参数，并返回第一个非NULL参数。如果所有参数都为NULL，则COALESCE函数返回NULL。
*/
select s1.id,coalesce(s2.student,s1.student) as student
from seat s1 left join seat s2 on ((s1.id+1)^1) - 1 = s2.id
order by s1.id
/*
627. 变更性别
SQL架构
给定一个 salary 表，如下所示，有 m = 男性 和 f = 女性 的值。
交换所有的 f 和 m 值（例如，将所有 f 值更改为 m，反之亦然）。
要求只使用一个更新（Update）语句，并且没有中间的临时表。
注意，您必只能写一个 Update 语句，请不要编写任何 Select 语句。
例如：
| id | name | sex | salary |
|----|------|-----|--------|
| 1 | A | m | 2500 |
| 2 | B | f | 1500 |
| 3 | C | m | 5500 |
| 4 | D | f | 500 |
运行你所编写的更新语句之后，将会得到以下表:
| id | name | sex | salary |
|----|------|-----|--------|
| 1 | A | f | 2500 |
| 2 | B | m | 1500 |
| 3 | C | f | 5500 |
| 4 | D | m | 500 |
*/
update salary
set
sex = case sex
when 'm' then 'f'
else 'm'
end;
/*
方法：使用 UPDATE 和 CASE...WHEN
算法
要想动态地将值设置成列，我们可以在使用 CASE...WHEN... 流程控制语句的同时使用 UPDATE 语句。
MySQL
UPDATE salary
SET
sex = CASE sex
WHEN 'm' THEN 'f'
ELSE 'm'
END;
*/
- 行转列
/*
1179. 重新格式化部门表
SQL架构
部门表 Department：
+---------------+---------+
| Column Name | Type |
+---------------+---------+
| id | int |
| revenue | int |
| month | varchar |
+---------------+---------+
(id, month) 是表的联合主键。
这个表格有关于每个部门每月收入的信息。
月份（month）可以取下列值 ["Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec"]。
编写一个 SQL 查询来重新格式化表，使得新的表中有一个部门 id 列和一些对应 每个月 的收入（revenue）列。
查询结果格式如下面的示例所示：
Department 表：
+------+---------+-------+
| id | revenue | month |
+------+---------+-------+
| 1 | 8000 | Jan |
| 2 | 9000 | Jan |
| 3 | 10000 | Feb |
| 1 | 7000 | Feb |
| 1 | 6000 | Mar |
+------+---------+-------+
查询得到的结果表：
+------+-------------+-------------+-------------+-----+-------------+
| id | Jan_Revenue | Feb_Revenue | Mar_Revenue | ... | Dec_Revenue |
+------+-------------+-------------+-------------+-----+-------------+
| 1 | 8000 | 7000 | 6000 | ... | null |
| 2 | 9000 | null | null | ... | null |
| 3 | null | 10000 | null | ... | null |
+------+-------------+-------------+-------------+-----+-------------+
注意，结果表有 13 列 (1个部门 id 列 + 12个月份的收入列)。
*/
select id,
SUM(CASE WHEN month='Jan' THEN revenue END) as Jan_Revenue,
SUM(CASE WHEN month='Feb' THEN revenue END) as Feb_Revenue,
SUM(CASE WHEN month='Mar' THEN revenue END) AS Mar_Revenue,
SUM(CASE WHEN month='Apr' THEN revenue END) AS Apr_Revenue,
SUM(CASE WHEN month='May' THEN revenue END) AS May_Revenue,
SUM(CASE WHEN month='Jun' THEN revenue END) AS Jun_Revenue,
SUM(CASE WHEN month='Jul' THEN revenue END) AS Jul_Revenue,
SUM(CASE WHEN month='Aug' THEN revenue END) AS Aug_Revenue,
SUM(CASE WHEN month='Sep' THEN revenue END) AS Sep_Revenue,
SUM(CASE WHEN month='Oct' THEN revenue END) AS Oct_Revenue,
SUM(CASE WHEN month='Nov' THEN revenue END) AS Nov_Revenue,
SUM(CASE WHEN month='Dec' THEN revenue END) AS Dec_Revenue
from department
group by id
order by id
- 列转行
建表语句：
CREATE TABLE tb_score1(
id INT(11) NOT NULL auto_increment,
userid VARCHAR(20) NOT NULL COMMENT '用户id',
cn_score DOUBLE COMMENT '语文成绩',
math_score DOUBLE COMMENT '数学成绩',
en_score DOUBLE COMMENT '英语成绩',
po_score DOUBLE COMMENT '政治成绩',
PRIMARY KEY(id)
)ENGINE = INNODB DEFAULT CHARSET = utf8;
插入数据：
INSERT INTO tb_score1(userid,cn_score,math_score,en_score,po_score) VALUES ('001',90,92,80,0);
INSERT INTO tb_score1(userid,cn_score,math_score,en_score,po_score) VALUES ('002',88,90,75.5,0);
INSERT INTO tb_score1(userid,cn_score,math_score,en_score,po_score) VALUES ('003',70,85,90,82);
查询数据表中的内容（即转换前的结果）
SELECT * FROM tb_score1
image.png
转换后：
image.png
本质是将userid的每个科目分数分散成一条记录显示出来。
直接上SQL：
SELECT userid,'语文' AS course,cn_score AS score FROM tb_score1
UNION ALL
SELECT userid,'数学' AS course,math_score AS score FROM tb_score1
UNION ALL
SELECT userid,'英语' AS course,en_score AS score FROM tb_score1
UNION ALL
SELECT userid,'政治' AS course,po_score AS score FROM tb_score1
ORDER BY userid
这里将每个userid对应的多个科目的成绩查出来，通过UNION ALL将结果集加起来，达到上图的效果。
附：UNION与UNION ALL的区别（摘）：
1.对重复结果的处理：UNION会去掉重复记录，UNION ALL不会；
2.对排序的处理：UNION会排序，UNION ALL只是简单地将两个结果集合并；
3.效率方面的区别：因为UNION 会做去重和排序处理，因此效率比UNION ALL慢很多；
网友评论