sql语句练习（三）：LeetCode

1. 更换性别（“m”改为“f”，“f”改为“m”）。

• 题目详情
  
• 解题方法

知识点：
① ord()：返回字符对应的ASCII数值（或Unicode数值），是chr()函数（对于8位的ASCII字符串）或unichr()函数（对于Unicode对象）的配对函数
② bin()：返回一个整数int或者长整数long int的二进制表示
③ 按位异或运算符（^）：当两对应的二进位相异时，结果为1

UPDATE salary 
SET sex = CHAR(ASCII('f') ^ ASCII('m') ^ ASCII(sex));

2. 查询当天气温比前一天气温高的日期。

• 题目详情
  
• 解题方法1

知识点：
① DATEDIFF(date1, date2)：返回两个日期之间的天数

SELECT w1.Id 
FROM Weather w1
INNER JOIN Weather w2 ON DATEDIFF(w1.RecordDate, w2.RecordDate) = 1 
AND w1.Temperature > w2.Temperature;

• 解题方法2

知识点：
① 窗口函数中的前后函数LAG(expr, 1)：返回位于当前行的前n行的expr的值
但这个方法在LeetCode中没法通过，总报语法错误，不解。

mysql> SELECT Id
    -> FROM (
    ->     SELECT *,
    ->     LAG(Temperature, 1) OVER w AS pre_Temperature
    ->     FROM Weather
    ->     WINDOW w AS (ORDER BY Id)) a
    -> WHERE Temperature > pre_Temperature;
+------+
| Id   |
+------+
|    2 |
|    4 |
+------+

3. 删除重复的邮件信息。

• 题目详情
  

• 解题方法1

知识点：
① 删除操作：DELETE FROM tb_name WHERE...
② MIN()函数

DELETE 
FROM Person
WHERE id NOT IN (
    SELECT id_email
    FROM (
        SELECT email, MIN(id) id_email
        FROM Person
        GROUP BY email)t1);

• 解题方法2

这个方法没有使用DELETE，不满足题目的要求。

mysql> SELECT id, email
    -> FROM (
    ->     SELECT *, ROW_NUMBER() OVER (PARTITION BY email ORDER BY id) 'col_3'
    ->     FROM tb_196)t
    -> WHERE col_3 = 1
    -> ORDER BY id;
+------+------------------+
| id   | email            |
+------+------------------+
|    1 | john@example.com |
|    2 | bob@example.com  |
+------+------------------+

4. 检索第二高薪。

• 题目详情
  
• 解题方法1

知识点：
① DISTINCT关键字
② 临时表：若表中只有一个记录，则不存在第二高薪。为了解决NULL问题，可以使用临时表

SELECT (
    SELECT DISTINCT Salary
    FROM Employee
    ORDER BY Salary DESC
    LIMIT 1,1) AS SecondHighestSalary;

• 解题方法2

知识点：
① IFNULL(expr1,expr2)：如果expr1不是NULL，返回expr1；否则返回expr2
这题可以使用IFNULL()解决NULL问题。

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

• 测试语句

mysql> SELECT * FROM test;
+--------+
| salary |
+--------+
|    100 |
|    100 |
+--------+

情况1：不使用DISTINCT关键字，不符合条件
mysql> SELECT salary
    -> FROM test
    -> ORDER BY salary
    -> LIMIT 1,1;
+--------+
| salary |
+--------+
|    100 |
+--------+
1 row in set (0.00 sec)

情况2：使用DISTINCT关键字，符合条件
mysql> SELECT DISTINCT salary
    -> FROM test
    -> ORDER BY salary
    -> LIMIT 1,1;
Empty set (0.00 sec)

5. 交换相邻座位的座位号。

• 题目详情
  
  
• 解题方法

知识点：
① MOD(n1,n2)：返回余数
② CASE WHEN ...THEN... ELSE... END
思路：
情况1：奇数id & 非末尾id —> id+1
情况2：奇数id & 末尾id —> id不变
情况3：偶数id —> id-1

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;

6. 检索连续出现3次的数字。

• 题目详情
  
• 解题方法

知识点：
① 自联结；②DISTINCT关键字
思路：
连续出现意味着Num的Id与彼此相邻。由于此问题要求数字连续出现至少三次，我们可以为此表Logs使用3个别名，然后检查3个连续数字是否全部相同。

SELECT DISTINCT t1.num AS ConsecutiveNums
FROM Logs t1, Logs t2, Logs t3
WHERE t1.id = t2.id-1 AND t2.id = t3.id-1
AND t1.num = t2.num AND t2.num = t3.num;

• 测试语句

mysql> SELECT * FROM tb_180;
+------+------+
| id   | num  |
+------+------+
|    1 |    1 |
|    2 |    1 |
|    3 |    1 |
|    4 |    2 |
|    5 |    1 |
|    6 |    2 |
|    7 |    2 |
|    8 |    2 |
|    8 |    2 |
+------+------+

情况1：不使用DISTINCT关键字，不符合条件
mysql> SELECT t1.num
    -> FROM tb_180 t1, tb_180 t2, tb_180 t3
    -> WHERE t1.id = t2.id-1 AND t2.id = t3.id-1
    -> AND t1.num = t2.num AND t2.num = t3.num;
+------+
| num  |
+------+
|    1 |
|    2 |
|    2 |
+------+

情况1：使用DISTINCT关键字，符合条件
mysql> SELECT DISTINCT t1.num
    -> FROM tb_180 t1, tb_180 t2, tb_180 t3
    -> WHERE t1.id = t2.id-1 AND t2.id = t3.id-1
    -> AND t1.num = t2.num AND t2.num = t3.num;
+------+
| num  |
+------+
|    1 |
|    2 |
+------+

7. 检索部门最高薪水。

• 题目详情
  
  
• 解题方法1

知识点：
① 内联结；
② WHERE子句：WHERE (col1, col2) IN (SELECT col1, col2 FROM ...)

SELECT t2.name AS 'Department', t1.name AS 'Employee', salary
FROM Employee t1
INNER JOIN Department t2 ON t1.departmentid = t2.id
WHERE  (t1.departmentid, salary) IN
    (SELECT departmentid, MAX(salary)
    FROM Employee
    GROUP BY departmentid);

• 解题方法2

知识点：
窗口函数：序号函数（ROW_NUMBER()）

mysql> SELECT t2.name AS Department, t1.name AS Employee, salary
    -> FROM
    ->     (SELECT *, ROW_NUMBER() OVER (PARTITION BY departmentid ORDER BY salary DESC) AS 'salary_order' FROM tb_1841)t1,
    ->     tb_1842 t2
    -> WHERE departmentid = t2.id AND t1.salary_order = 1;
+------------+----------+--------+
| Department | Employee | salary |
+------------+----------+--------+
| IT         | Max      |  90000 |
| Sales      | Henry    |  80000 |
+------------+----------+--------+

8. 检索第N高薪（创建自定义函数）。

• 题目详情
  
• 解题方法

知识点：
① 创建自定义函数：
CREATE FUNCTION func_name(参数 参数类型) RETURNS 返回值类型 BEGIN... END

CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
  SET N = N-1;
  RETURN (
      SELECT Salary 
      FROM Employee
      GROUP BY Salary
      ORDER BY Salary DESC
      LIMIT N, 1
  );
END

9. 检索最少连续3天超过100人参观体育馆的信息。

• 题目详情
  
  
• 解题方法

思路：
① 笛卡尔积：检索出6x6x6条信息
② 假设t1的检索列分别位于三天中的第1天、第2天、第3天，其中t2、t3的顺序保持不变：
情况1：t1 ->t2 ->t3
情况2：t2 ->t1 ->t3
情况3：t2 ->t3 ->t1

SELECT DISTINCT t1.*
FROM stadium t1, stadium t2, stadium t3
WHERE t1.people >= 100 AND t2.people >= 100 AND t3.people >= 100
AND (
    (t3.id-t2.id = 1 AND t3.id-t1.id = 2 AND t2.id-t1.id = 1)
    OR
    (t3.id-t1.id = 1 AND t3.id-t2.id = 2 AND t1.id-t2.id = 1)
    OR
    (t1.id-t3.id = 1 AND t1.id-t2.id = 2 AND t3.id-t2.id = 1)
)
ORDER BY id;

10. 检索每个部门的前3高薪。

• 题目详情
  
  
• 解题方法1

思路：
前3高薪，则说明同部门比当前高薪高的人数少于3，使用自联结。

SELECT a.name AS 'Department', Employee, Salary
FROM Department a
INNER JOIN (
    SELECT t1.name AS 'Employee', t1.Salary, t1.departmentid
    FROM Employee t1
    WHERE 3 > (
        SELECT COUNT(DISTINCT t2.Salary)
        FROM Employee t2
        WHERE t2.Salary > t1.Salary AND t1.departmentid = t2.departmentid)
    )b
ON departmentid = id
ORDER BY Department, Salary DESC;

• 解题方法2

知识点：
窗口函数：序号函数（DENSE_RANK()）

mysql> SELECT Department, Employee, Salary
    -> FROM (
    ->     SELECT t2.name AS 'Department', t1.name AS 'Employee', salary,  DENSE_RANK() OVER (PARTITION BY departmentid ORDER BY salary DESC) AS 'salary_order'
    ->     FROM tb_1851 t1
    ->     INNER JOIN tb_1852 t2 ON departmentid = t2.id) a
    -> WHERE salary_order IN (1,2,3);
+------------+----------+--------+
| Department | Employee | Salary |
+------------+----------+--------+
| IT         | Max      |  90000 |
| IT         | Randy    |  85000 |
| IT         | Joe      |  70000 |
| Sales      | Henry    |  80000 |
| Sales      | Sam      |  60000 |
+------------+----------+--------+

11. 查找指定日期的未受限用户的请求取消率。

• 题目详情
  
• 解题方法

思路：
注意要在子查询中控制日期在10月1日至10月3日，LeetCode的测试案例给了一条10月4日的数据，查询结果应该为空。

SELECT request_at AS 'Day',
    (CASE
     WHEN request_at = '2013-10-01'
     THEN ROUND(SUM(CASE WHEN banned = 'No' AND status != 'completed' THEN 1 ELSE 0 END) / SUM(CASE WHEN banned = 'No' THEN 1 ELSE 0 END), 2)
     WHEN request_at = '2013-10-02'
     THEN ROUND(SUM(CASE WHEN banned = 'No' AND status != 'completed' THEN 1 ELSE 0 END) / SUM(CASE WHEN banned = 'No' THEN 1 ELSE 0 END), 2)
     WHEN request_at = '2013-10-03'
     THEN ROUND(SUM(CASE WHEN banned = 'No' AND status != 'completed' THEN 1 ELSE 0 END) / SUM(CASE WHEN banned = 'No' THEN 1 ELSE 0 END), 2)
     END) AS 'Cancellation Rate'
FROM (
    SELECT client_id, banned, status, request_at
    FROM Trips a
    INNER JOIN Users b ON client_id = users_id
    WHERE request_at IN ('2013-10-01','2013-10-02','2013-10-03')) t
GROUP BY request_at
;