character_set_results

精讲Mysql各种高难度Sql编写（二）

• sql1

• sql2

• sql3

今天是大年初九，首先给各位道一句新年好，祝各位大佬新的一年，升职加薪，早日暴富，红红火火，虎虎生威！！！

然后继续我们的sql编写，先来个简单的

sql1

这次用LeetCode 数据库 196题为例

sql脚本

CREATE TABLE Person2(
 Id INT,
 Email VARCHAR(50),
 PRIMARY KEY(Id)
 );

INSERT INTO Person2 VALUE(1,‘[e-mail]’),(2,'[e-mail] '),(3,‘[e-mail]’);

题目:删除表里面重复的email数据，保留id最小的那个

解法一、先看老哥的菜鸡解法，哈哈哈

#这里注意:不可以对同一个表即进行查询又更新删除操作，
#解决方案（把查询结果作为一个临时表）
delete from Person2 where id not in (
    select a.id from (
		select min(id) as id from Person2 GROUP BY email) a
)

解法二、官方解法，将两边自连接，然后删除大的id，啊啊啊，绝了，瞬间觉得我好菜！

DELETE p1
FROM Person2 p1,
     Person2 p2
WHERE
    p1.Email = p2.Email 
AND p1.id > p2.id

解法三、大神解法，他这个，有点难以学习

DELETE U
FROM Person2 AS U
LEFT JOIN (
    SELECT MIN(id) AS `id`,email
    FROM Person2
    GROUP BY email
) AS A ON (U.email = A.email AND U.id = A.id)
WHERE A.id IS NULL

sql2

这次用LeetCode 数据库 196题为例

题目:查找与之前（昨天的）日期相比温度更高的所有日期的 Id

CREATE TABLE Weather(
Id INT,
RecordDate DATE,
Temperature INT );

INSERT INTO Weather VALUE(1,‘2022-01-01’,10),(2,‘2022-01-02’,25),(3,‘2022-01-03’,20),(4,‘2022-01-04’,30)

老哥先不讲解法,先说下大家可能会遇到的坑，聚合函数，什么是聚合函数呢? 像max,min,avg,count,sum 就是聚合函数，它有个特点，就是聚合函数和聚合函数一起查可以，但是聚合函数和单个字段一起查不行，需要加上group by +单个字段

有点像江湖武林的意思，聚合函数是一个组织，单个字段如果想跟我混的话，老老实实使用 group by 加入组织

不禁让我想起一句话，江湖不是打打杀杀，江湖是人情世故

#max,avg是聚合函数，不能又查询单个字段x，又使用聚合，
#如果实在需要,后面要加上group by + 单个字段x
SELECT temperature,max(recordDate)
FROM Weather GROUP BY temperature

解法一、这题需要使用DATEDIFF函数，一般很少使用这个

SELECT tod.`Id` AS Id
FROM Weather yes
LEFT JOIN Weather tod
ON DATEDIFF(tod.`RecordDate`, yes.`RecordDate`) = 1
WHERE tod.`Temperature` > yes.`Temperature`

解法二、大神解法，我觉得看看就行了，学不来

SELECT W2.Id
FROM weather AS W1 JOIN weather AS W2 
ON (TIMESTAMPDIFF(DAY,W1.RecordDate,W2.RecordDate) = 1 AND W1.Temperature < W2.Temperature)

sql3

sql脚本

题目要求:列出所有超过或等于5名学生的课

最终结果:

CREATE TABLE courses (
student varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
class varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;

INSERT INTO courses VALUES (‘A’, ‘Math’);
INSERT INTO courses VALUES (‘B’, ‘English’);
INSERT INTO courses VALUES (‘C’, ‘Math’);
INSERT INTO courses VALUES (‘D’, ‘Biology’);
INSERT INTO courses VALUES (‘E’, ‘Math’);
INSERT INTO courses VALUES (‘F’, ‘Computer’);
INSERT INTO courses VALUES (‘G’, ‘Math’);
INSERT INTO courses VALUES (‘H’, ‘Math’);
INSERT INTO courses VALUES (‘I’, ‘Math’);

解法一、先看看老哥解法，常规思路

select a.class from (
select count(distinct student) as sum,class from courses 
GROUP BY class HAVING sum >=5) a

解法二、大神解法，他这个就直接去通过课程分组，拿到课程以后，having 最后在mysql内存做过滤

select class from courses GROUP BY class having count(DISTINCT student) >=5

解法三、官方解法,差别不大，用了where做条件过滤

SELECT
    class
FROM
    (SELECT
        class, COUNT(DISTINCT student) AS num
    FROM
        courses
    GROUP BY class) AS temp_table
WHERE
    num >= 5
;