LeetCode-SQL练习题总结(MySQL实现)

野性酷女 2022-12-05 02:16 336阅读 0赞

### 文章目录 ###

*  前言
 *  176. 第二高的薪水
 *  177. 第N高的薪水
 *   *  单次查询
     *  子查询
     *  自连接
     *  自定义变量
 *  178. 分数排名
 *  180. 连续出现的数字
 *  181. 超过经理收入的员工
 *  182. 查找重复的电子邮箱
 *  183. 从不订购的客户
 *  184. 部门工资最高的员工
 *   *  子查询
     *  内连接+子查询
 *  185. 部门工资前三高的所有员工
 *   *  子查询判断工资前三
     *  内连接判断工资前三
 *  196. 删除重复的电子邮箱
 *   *  not in
     *  自连接
 *  197. 上升的温度
 *  262. 行程和用户
 *   *  自连接 排除禁止用户相关的订单
     *  巧用avg
 *  601. 体育馆的人流量
 *   *  自连接
 *  626. 换座位
 *   *  case when
     *  位操作
 *  627. 交换工资
 *  总结

# 前言 #

对LeetCode上的SQL练习题的免费部分进行了部分讲解，考虑大部分公司并没有升级到Mysql8，所以本文也没有使用窗口函数的解法（以后可以考虑补充，毕竟用了窗口函数解法就没有了通用性）。

# 176. 第二高的薪水 #

编写一个 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,1) as SecondHighestSalary

本来直接使用子查询即可，但如果表只有一条记录或没有记录，那么返回的结果集的行数为0。而题目的要求是这种情况下，返回的结果集也得有1行，且这行数据为null。但把空的结果集放到select之后的话，则会返回一条null数据。

SELECT
        IFNULL(
          (SELECT DISTINCT Salary
           FROM Employee
           ORDER BY Salary DESC
            LIMIT 1 OFFSET 1),
        NULL) AS SecondHighestSalary

> IFNULL(expr1,expr2)  
> 如果expr1不是NULL，IFNULL()返回expr1，否则它返回expr2。IFNULL()返回一个数字或字符串值。

利用IFNULL函数，当子查询结果集一行数据都没有时，返回NULL。

# 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 (
            select distinct Salary from Employee order by Salary desc limit N,1
            #最后不能有分号
      );
    END

limit后面不支持表达式，只支持单个变量，所以需要提前set。

## 子查询 ##

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
              (SELECT count(DISTINCT salary) FROM employee WHERE salary>e.salary) = N - 1
      );
    END

*  从表中每遍历一行就执行一个子查询，子查询检查有多少工资大于当前行的工资，想要的是第N高的工资，那么就会有N-1个工资比它高。
    
     *  如果条件是`salary>=e.salary`，则是自己算在内。
 *  第N高的工资可能也有好几个，所以得去重。

## 自连接 ##

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

*  使用内连接连接两个表，连接条件是右表工资大于等于左表工资。
 *  固定住左表工资，那么右表工资要么是大于左表的，要么是等于左表的。
    
     *  所以以`e1.salary`分组，统计右表部分的不重复值，即可知道左表工资是第几的。
 *  把内连接改成左连接，那么结果集不会多出来一行记录，因为左连接是先根据连接条件建立笛卡尔积，建立完后如果左表还有记录没被挑选过（这条左表记录通过连接条件无法和任何一条右表记录连接起来），那才把这些左表记录作为差集放到结果里去。但现在连接条件包含等于，所以必然同样大小的能成立，所以不会有左表记录没被挑选过。
    
     *  上面程序，改写LEFT JOIN一样能通过。

CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
    BEGIN
      RETURN (
          # Write your MySQL query statement below.
          SELECT 
              e1.salary
          FROM 
              employee e1 LEFT JOIN employee e2 ON e1.salary < e2.salary
          GROUP BY
              e1.salary
          HAVING 
              count(DISTINCT e2.salary) = N - 1
      );
    END

*  如果条件是`e1.salary < e2.salary`，那么就只需要统计右表部分是否有N-1个不重复值了。
 *  但这个程序，必须使用LEFT JOIN。因为有可能N为1，即要找最大工资，假如连接还是INNER JOIN，但现在连接条件是`e1.salary < e2.salary`，那么最大工资是不可能有它更大的工资的，那么左表的最大工资就不能和右表的任何记录相连。
    
     *  考虑N为1，把INNER JOIN改成LEFT JOIN，那么左表的最大工资对应的右边记录都为null，而count函数可以忽略null，那么count函数得到的值肯定为0，刚好等于1-1。

## 自定义变量 ##

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

先看子查询：

*  按照降序排列。
 *  把变量初始化部分`(SELECT @r:=0, @p:=NULL)init`放到FROM子句的后面。
 *  第一次遍历行时，`@p`为NULL，`@p=salary`肯定不成立，所以返回`@r+1`即0+1=1。
 *  每次遍历结束前，赋值`@p:= salary`。
 *  如果`@p=salary`成立，说明与上一次相比是相同工资，则排名应该相同，所以不加1。

再看外查询：  
因为想要的第N高工资可能有多个，所以要去重，条件就是`rnk = N`。

# 178. 分数排名 #

编写一个 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    |
    +-------+------+

select t.score Score,convert(t.rank, SIGNED) 'Rank' from 
    (
        select score, @r:=IF(@sc=score,@r,@r+1) as 'rank', @sc:=score
        from Scores, (select @r:=0, @sc:=NULL)init
        order by score desc
    ) t

*  使用自定义变量得到这个rank值。
 *  子查询的rank字段会变成一个字符串类型，所以在外层查询需要转换一下类型。

# 180. 连续出现的数字 #

编写一个 SQL 查询，查找所有至少连续出现三次的数字。

+----+-----+
    | Id | Num |
    +----+-----+
    | 1  |  1  |
    | 2  |  1  |
    | 3  |  1  |
    | 4  |  2  |
    | 5  |  1  |
    | 6  |  2  |
    | 7  |  2  |
    +----+-----+

例如，给定上面的 Logs 表， 1 是唯一连续出现至少三次的数字。

+-----------------+
    | ConsecutiveNums |
    +-----------------+
    | 1               |
    +-----------------+

# Write your MySQL query statement below
    
    select distinct t.Num as ConsecutiveNums 
    from 
    (
        select Num, @count:=IF(@prev=Num,@count+1,1) as co, @prev:=Num
        from Logs, (select @prev:=null,@count:=1)init 
    )t
    where t.co >= 3

*  依然通过自定义变量解决。
 *  如果有连续4个或更多，那么子查询里面`>= 3`的数字就会有重复的，所以外查询需要去重。

# 181. 超过经理收入的员工 #

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 t1.Name as Employee 
    from Employee t1 inner join Employee t2
    on t1.ManagerId = t2.Id
    and t1.Salary > t2.Salary

建立内连接，连接条件是左边的员工连接上右边的经理。

# 182. 查找重复的电子邮箱 #

编写一个 SQL 查询，查找 Person 表中所有重复的电子邮箱。

示例：

+----+---------+
    | Id | Email   |
    +----+---------+
    | 1  | a@b.com |
    | 2  | c@d.com |
    | 3  | a@b.com |
    +----+---------+

根据以上输入，你的查询应返回以下结果：

+---------+
    | Email   |
    +---------+
    | a@b.com |
    +---------+

select Email
    from Person
    group by Email
    having count(Email) > 1;

最简单的，分组，然后在having中筛选。

select Email from
    (
      select Email, count(Email) as num
      from Person
      group by Email
    ) as statistic
    where num > 1;

这个和上面完全一模一样，只是它把count放到了字段里，所以外层查询还得通过这个字段过滤一遍。

select distinct Email
    from Person
    where Id not in
    (
        select min(Id) from Person group by Email
    )

还有本人的奇思妙想。利用Id不重复，从每种邮箱中挑选出一个id来，然后外层查询排除这些id，如果排除后还有记录，说明剩余的记录都是重复的邮箱。注意，如果一个重复邮箱有3个，排除后还会剩两个，所以一定要distinct。

# 183. 从不订购的客户 #

某网站包含两个表，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 left join Orders 
    on Customers.Id = Orders.CustomerId 
    group by Customers.Id
    having count(Orders.CustomerId) = 0

*  利用左外连接，没有订购过的客户的左表记录肯定无法和右表记录连接起来。
 *  如果左表无法与右表连接，那么这种记录的右边记录都为null，而且count函数会过滤null的字段。所以这个查询好使。
    
     *  因为这里利用了Mysql的特性，就是不是group by的字段也可以去select，但只会返回分组的某一条，而不会报错。

select Customers.Name as Customers 
    from Customers left join Orders 
    on Customers.Id = Orders.CustomerId 
    where Orders.CustomerId is null

左外连接后，直接判断 连接记录的右边记录都为null。

# 184. 部门工资最高的员工 #

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 
    (select Name from Department where DepartmentId = Id) as Department,
    Name as Employee, 
    Salary 
    from Employee 
    where (DepartmentId,Salary) in
    (
        select DepartmentId,max(Salary) from Employee group by DepartmentId 
    ) 
    and (select Name from Department where DepartmentId = Id) is not null

*  条件说了，如果一个部门如果有两个人都是最高工资，那么这两个人都需要返回。
 *  用子查询分组，找出 部门和最高工资 的唯一组合，放到一个结果集里。
 *  在外查询用IN条件，检查这样组合的 部门和工资。
 *  关于Department表的子查询，只是个工具人。
    
     *  有可能员工表的部门id在部门表找不到，或者部门表根本没有记录，这种情况不能返回这个部门的最高工资（因为部门找不到），所以需要最后加个where判断。

## 内连接+子查询 ##

select 
    Department.Name as Department,
    Employee.Name as Employee, 
    Salary 
    from Employee inner join Department on Employee.DepartmentId = Department.Id
    where (DepartmentId,Salary) in
    (
        select DepartmentId,max(Salary) from Employee group by DepartmentId 
    )

与上面的区别是：

*  使用了内连接来排除掉了 员工的部门id在部门表中找不到 的情况，所以不用加where条件了。
 *  也因为内连接把两个表连接了起来，所以部门名称不用子查询去找了。直接在join后的行里就有了部门名称。

# 185. 部门工资前三高的所有员工 #

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 的工资排第二。

## 子查询判断工资前三 ##

SELECT
        d.Name AS 'Department', e1.Name AS 'Employee', e1.Salary
    FROM
        Employee e1
            JOIN
        Department d ON e1.DepartmentId = d.Id
    WHERE
        (SELECT
            COUNT(DISTINCT e2.Salary)
        FROM
            Employee e2
        WHERE
            e2.Salary > e1.Salary
                AND e1.DepartmentId = e2.DepartmentId
        ) < 3
    ;

*  外部查询每得到一行数据都会去执行一遍where条件里的子查询。
    
     *  子查询直接去查询有哪些工资大于了当前行的工资，利用DISTINCT去重，最后COUNT便得到了当前工资处于第几。
 *  外部查询内连接了Department表，只是为了得到部门名称。

## 内连接判断工资前三 ##

当然，子查询一般都能转换为连接来做。

SELECT d.Name AS 'Department', e1.Name AS 'Employee', e1.Salary
    FROM
        Employee e1
            JOIN
        Department d ON e1.DepartmentId = d.Id
    Where (DepartmentId, Salary) in
    (
        SELECT
            e1.DepartmentId, e1.Salary
        FROM
            Employee e1
                JOIN
            Employee e2 ON e1.DepartmentId = e2.DepartmentId and e2.Salary >= e1.Salary
        group by 
            e1.DepartmentId, e1.Salary
        having 
            COUNT(DISTINCT e2.Salary) <= 3
    )

*  在子查询中，得到了每个部门前三高的工资，以部门id和工资的组合返回。
    
     *  连接条件：必须部门相同才连接；右表工资是作为统计用的，所以让右表工资大于等于左表，大于等于是为了最高工资能与自己相连。
     *  group by：以左表的部门id和工资来分组，这样就固定住了左表的任意工资，而右表组合则是大于等于这个任意工资的所有其他工资（前提是同部门的）。
     *  having：统计大于等于这个任意工资的所有其他工资，即可。

# 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 语句。

## not in ##

delete 
    from Person
    where Id not in
    (
        select Id from
        (
            select min(Id) as Id
            from Person 
            group by Email
        )t
    )

*  子查询先得到每个邮箱的最小id，然后删除掉不是这些id的行。
 *  子查询需要套个壳子，不然报错You can’t specify target table ‘Person’ for update in FROM clause。

## 自连接 ##

果然，子查询基本都可以转换为连接。

DELETE p1 FROM Person p1,
        Person p2
    WHERE
        p1.Email = p2.Email AND p1.Id > p2.Id

*  自连接后，相同邮箱的记录都会连在一起（`p1.Email = p2.Email`），且左表记录一定是重复的id大于最小id的邮箱。
 *  DELETE p1删除左表的记录，因为条件是`p1.Id > p2.Id`。
 *  假设有一个重复邮箱有三个id，分别是3，2，1。如果是select的话，会形成3-2,3-1,2-1三条记录，这看起来好像会删除id 3两次。其实不会，建立笛卡尔积时，左表记录遍历到id 3了，然后从右表挑选记录出来连接，发现连接成功了然后就删除掉这条左表记录，然后遍历下一行左表记录。

# 197. 上升的温度 #

给定一个 Weather 表，编写一个 SQL 查询，来查找与之前（昨天的）日期相比温度更高的所有日期的 Id。

+---------+------------------+------------------+
    | Id(INT) | RecordDate(DATE) | Temperature(INT) |
    +---------+------------------+------------------+
    |       1 |       2015-01-01 |               10 |
    |       2 |       2015-01-02 |               25 |
    |       3 |       2015-01-03 |               20 |
    |       4 |       2015-01-04 |               30 |
    +---------+------------------+------------------+

例如，根据上述给定的 Weather 表格，返回如下 Id:

+----+
    | Id |
    +----+
    |  2 |
    |  4 |
    +----+

select w1.Id
    from Weather w1, Weather w2
    where DATEDIFF(w1.RecordDate,w2.RecordDate) = 1
    and w1.Temperature > w2.Temperature

*  考察Date数据类型和DATEDIFF函数的使用。
 *  使用自连接，第一个条件用来固定左表为今天，右表为昨天；第二个条件，检查今天的温度大于昨天的温度。
 *  当然这个自连接肯定可以转换为子查询，子查询做的事情和where条件一样。

# 262. 行程和用户 #

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        |
    +------------+-------------------+

## 自连接 排除禁止用户相关的订单 ##

关心的角色只有driver和client，关键是理解“非禁止用户生成的订单”，一个订单的产生既需要乘客也需要司机，所以“非禁止用户生成的订单”是指订单的乘客和司机都是非禁止用户。

SELECT T.request_at AS `Day`, 
        ROUND(SUM(IF(T.STATUS = 'completed',0,1))/COUNT(*), 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

*  `JOIN Users AS U1 ON (T.client_id = U1.users_id AND U1.banned ='No')`。使用内连接来连接订单表的乘客和用户表的用户，但加了条件是非禁止用户，所以内连接以后，订单中的乘客是禁止用户的订单会被剔除。
 *  下一个内连接同理，剔除了订单中的司机是禁止用户的订单。
 *  最后按照时间分组，每个分组中的记录都是当天的订单。再求出每个分组中取消订单的数量。最后取消订单数除以总数。

## 巧用avg ##

SELECT T.request_at AS `Day`, 
        ROUND(AVG(T.STATUS != 'completed'), 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

`T.STATUS != 'completed'`返回一个布尔值，当是取消订单时返回一个true。布尔值本质上就是0和1。所以AVG就能求出0和1之中有多少个1。

# 601. 体育馆的人流量 #

X 市建了一个新的体育馆，每日人流量信息被记录在这三列信息中：序号 (id)、日期 (visit\_date)、 人流量 (people)。

请编写一个查询语句，找出人流量的高峰期。高峰期时，至少连续三行记录中的人流量不少于100。

例如，表 stadium：

+------+------------+-----------+
    | 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-08 | 188       |
    +------+------------+-----------+

对于上面的示例数据，输出为：

+------+------------+-----------+
    | id   | visit_date | people    |
    +------+------------+-----------+
    | 5    | 2017-01-05 | 145       |
    | 6    | 2017-01-06 | 1455      |
    | 7    | 2017-01-07 | 199       |
    | 8    | 2017-01-08 | 188       |
    +------+------------+-----------+

提示：

每天只有一行记录，日期随着 id 的增加而增加。  
体育馆并不是每天都开放的，所以记录中的日期可能会出现断层。

## 自连接 ##

select distinct id, stadium.visit_date, people
    from stadium,
    (
        select s1.visit_date 
        from stadium s1,stadium s2,stadium s3
        where DATEDIFF(s1.visit_date,s2.visit_date) = 1
        and DATEDIFF(s2.visit_date,s3.visit_date) = 1
        and s1.people >=100 and s2.people >=100 and s3.people >=100    
    ) t
    where DATEDIFF(t.visit_date, stadium.visit_date) between 0 and 2

*  子查询三表自连接，三表记录连接后，从右到左天数就多一天，所以最左表就是连续三天的最大的一天。并且加上条件：连续三天的人流量必须都不小于100。
    
     *  从示例数据库来说，我们就得到了`2017-01-07`和`2017-01-08`。
 *  外查询检查和`2017-01-07`或`2017-01-08`相差在3天之内的天数。

不过理解错了题意，因为天数可能断层，id连续3个才是最重要的。

select distinct stadium.id, visit_date, people
    from stadium,
    (
        select s1.id 
        from stadium s1,stadium s2,stadium s3
        where s1.id - s2.id = 1
        and s2.id - s3.id = 1
        and s1.people >=100 and s2.people >=100 and s3.people >=100    
    ) t
    where (t.id - stadium.id) between 0 and 2

# 626. 换座位 #

小美是一所中学的信息科技老师，她有一张 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 when ##

# Write your MySQL query statement below
    
    SELECT
        (CASE
            WHEN MOD(id, 2) != 0 AND counts != id THEN id + 1  #对于所有座位 id 是奇数的学生，修改其 id 为 id+1
            WHEN MOD(id, 2) != 0 AND counts = id THEN id       #如果最后一个座位 id 也是奇数，则最后一个座位 id 不修改
            ELSE id - 1                                        #对于所有座位 id 是偶数的学生，修改其 id 为 id-1
        END) AS id,
        student
    FROM
        seat,
        (SELECT
            COUNT(*) AS counts
        FROM
            seat) AS seat_counts
    ORDER BY id ASC;

*  这个解法建立在id肯定是从1开始的，而且肯定是连续的。
 *  重点在于case when的用法。
 *  from后面的子查询比较特殊，因为它只执行一次，且是刚开始就执行了的。相当于作为初始条件，而外层查询可以直接使用这个初始查询的字段，这个字段可以是一条记录（本解法是这样），或是多条记录。

## 位操作 ##

使用 `(id+1)^1-1` 计算交换后每个学生的座位 id。

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  |

利用了异或，任何bit与0异或都是bit本身，任何bit与1异或都是相反值。这里相当于是`^...0001`，所以前面的bit都不变，最低位bit取反。  
所以`id ^ 1`就是：

*  偶数`==>`偶数+1
 *  奇数`==>`奇数-1

但显然没有达到上面的互换要求，所以先加1再异或：

*  (奇数+1)`==>`(奇数+1)+1`==>`奇数+2
 *  (偶数+1)`==>`(偶数+1)-1`==>`偶数

最后都再减一：

*  奇数+1
 *  偶数-1

显然，上面的表格除了最后一行，都可以通过新id列和旧id连接在一起。这样连接后，左表是原始学生，右表是与之交换的学生。

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;

> COALESCE ( expression,value1,value2……,valuen)  
> COALESCE()函数的第一个参数expression为待检测的表达式，而其后的参数个数不定。  
> COALESCE()函数将会返回包括expression在内的所有参数中的第一个非空表达式。

在已连接上的情况下，COALESCE优先使用右表的学生姓名，但考虑最后一个奇数同学左连接后，没有右表记录与之相连，所以这行还是使用左表记录。

# 627. 交换工资 #

给定一个 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;

这样使用了case when另一种用法，即case后面带参数的。

# 总结 #

*  把子查询放到select里作为一个计算字段时，每遍历一行都会执行一遍子查询。
    
     *  把子查询放到where条件中，也同理。
 *  把子查询放到from里，可作为一个初始条件，只执行一次。
 *  子查询一般都可以转换为表连接。
    
     *  深刻理解笛卡尔积后，表连接会使得思路更加清晰。
     *  内连接可以用来排除一些行，只保留满足条件的行。