sql

• 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慢很多；