sql语句练习（三）：LeetCode – 小飞侠

sql语句练习（三）：LeetCode

Post author:xfxia
Post published:2023年10月12日
Post category:其他

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
;

版权声明：本文为weixin_39010770原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。

原文链接：https://blog.csdn.net/weixin_39010770/article/details/88575877