记录一些 sql 刷题笔记

力扣

1. 删除重复的电子邮箱

表: Person

+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| id          | int     |
| email       | varchar |
+-------------+---------+
id是该表的主键列。
该表的每一行包含一封电子邮件。电子邮件将不包含大写字母。

编写一个 SQL 删除语句 来 删除 所有重复的电子邮件，只保留一个id最小的唯一电子邮件。

以 任意顺序 返回结果表。 （注意： 仅需要写删除语句，将自动对剩余结果进行查询）

查询结果格式如下所示。
示例 1:

输入: 
Person 表:
+----+------------------+
| id | email            |
+----+------------------+
| 1  | john@example.com |
| 2  | bob@example.com  |
| 3  | john@example.com |
+----+------------------+
输出: 
+----+------------------+
| id | email            |
+----+------------------+
| 1  | john@example.com |
| 2  | bob@example.com  |
+----+------------------+
解释: john@example.com重复两次。我们保留最小的Id = 1。

题解

# where 子查询
delete from Person where id not in (select * from (select min(id) from Person group by Email) as x )

# LeetCode

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

196. 删除重复的电子邮箱

2. 按日期分组销售产品

表 Activities：

+-------------+---------+
| 列名         | 类型    |
+-------------+---------+
| sell_date   | date    |
| product     | varchar |
+-------------+---------+
此表没有主键，它可能包含重复项。
此表的每一行都包含产品名称和在市场上销售的日期。

编写一个 SQL 查询来查找每个日期、销售的不同产品的数量及其名称。
每个日期的销售产品名称应按词典序排列。
返回按 sell_date 排序的结果表。
查询结果格式如下例所示。

示例 1:

输入：
Activities 表：
+------------+-------------+
| sell_date  | product     |
+------------+-------------+
| 2020-05-30 | Headphone   |
| 2020-06-01 | Pencil      |
| 2020-06-02 | Mask        |
| 2020-05-30 | Basketball  |
| 2020-06-01 | Bible       |
| 2020-06-02 | Mask        |
| 2020-05-30 | T-Shirt     |
+------------+-------------+
输出：
+------------+----------+------------------------------+
| sell_date  | num_sold | products                     |
+------------+----------+------------------------------+
| 2020-05-30 | 3        | Basketball,Headphone,T-shirt |
| 2020-06-01 | 2        | Bible,Pencil                 |
| 2020-06-02 | 1        | Mask                         |
+------------+----------+------------------------------+
解释：
对于2020-05-30，出售的物品是 (Headphone, Basketball, T-shirt)，按词典序排列，并用逗号 ',' 分隔。
对于2020-06-01，出售的物品是 (Pencil, Bible)，按词典序排列，并用逗号分隔。
对于2020-06-02，出售的物品是 (Mask)，只需返回该物品名。

题解

select sell_date, count(distinct product) as num_sold,group_concat(distinct product) as products from Activities group by sell_date;

按日期分组销售产品

丢失信息的雇员

表: Employees

+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| employee_id | int     |
| name        | varchar |
+-------------+---------+
employee_id 是这个表的主键。
每一行表示雇员的id 和他的姓名。

表: Salaries

+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| employee_id | int     |
| salary      | int     |
+-------------+---------+
employee_id is 这个表的主键。
每一行表示雇员的id 和他的薪水。

写出一个查询语句，找到所有 丢失信息 的雇员id。当满足下面一个条件时，就被认为是雇员的信息丢失：

• 雇员的 姓名 丢失了，或者
• 雇员的 薪水信息 丢失了，或者

返回这些雇员的id employee_id ， 从小到大排序 。
查询结果格式如下面的例子所示。
示例 1：

输入：
Employees table:
+-------------+----------+
| employee_id | name     |
+-------------+----------+
| 2           | Crew     |
| 4           | Haven    |
| 5           | Kristian |
+-------------+----------+
Salaries table:
+-------------+--------+
| employee_id | salary |
+-------------+--------+
| 5           | 76071  |
| 1           | 22517  |
| 4           | 63539  |
+-------------+--------+
输出：
+-------------+
| employee_id |
+-------------+
| 1           |
| 2           |
+-------------+
解释：
雇员1，2，4，5 都工作在这个公司。
1号雇员的姓名丢失了。
2号雇员的薪水信息丢失了。

题解

select employee_id FROM (
SELECT employee_id FROM Employees 
UNION ALL 
SELECT employee_id FROM Salaries
) as t 
group by employee_id
having count(*)=1
order by employee_id

丢失信息的雇员

CSDN 题目

各个表的基本信息

# 学生表
CREATE TABLE `Student`(
`s_id` VARCHAR(20),
`s_name` VARCHAR(20) NOT NULL DEFAULT '',
`s_birth` VARCHAR(20) NOT NULL DEFAULT '',
`s_sex` VARCHAR(10) NOT NULL DEFAULT '',
PRIMARY KEY(`s_id`)
);
#课程表
CREATE TABLE `Course`(
`c_id` VARCHAR(20),
`c_name` VARCHAR(20) NOT NULL DEFAULT '',
`t_id` VARCHAR(20) NOT NULL,
PRIMARY KEY(`c_id`)
);
#教师表
CREATE TABLE `Teacher`(
`t_id` VARCHAR(20),
`t_name` VARCHAR(20) NOT NULL DEFAULT '',
PRIMARY KEY(`t_id`)
);
#成绩表
CREATE TABLE `Score`(
`s_id` VARCHAR(20), # 学号
`c_id` VARCHAR(20),# 课程号
`s_score` INT(3),
PRIMARY KEY(`s_id`,`c_id`)
);

#插入学生表测试数据
insert into Student values('01' , '赵雷' , '1990-01-01' , '男');
insert into Student values('02' , '钱电' , '1990-12-21' , '男');
insert into Student values('03' , '孙风' , '1990-05-20' , '男');
insert into Student values('04' , '李云' , '1990-08-06' , '男');
insert into Student values('05' , '周梅' , '1991-12-01' , '女');
insert into Student values('06' , '吴兰' , '1992-03-01' , '女');
insert into Student values('07' , '郑竹' , '1989-07-01' , '女');
insert into Student values('08' , '王菊' , '1990-01-20' , '女');
#课程表测试数据
insert into Course values('01' , '语文' , '02');
insert into Course values('02' , '数学' , '01');
insert into Course values('03' , '英语' , '03');

#教师表测试数据
insert into Teacher values('01' , '张三');
insert into Teacher values('02' , '李四');
insert into Teacher values('03' , '王五');

#成绩表测试数据
insert into Score values('01' , '01' , 80);
insert into Score values('01' , '02' , 90);
insert into Score values('01' , '03' , 99);
insert into Score values('02' , '01' , 70);
insert into Score values('02' , '02' , 60);
insert into Score values('02' , '03' , 80);
insert into Score values('03' , '01' , 80);
insert into Score values('03' , '02' , 80);
insert into Score values('03' , '03' , 80);
insert into Score values('04' , '01' , 50);
insert into Score values('04' , '02' , 30);
insert into Score values('04' , '03' , 20);
insert into Score values('05' , '01' , 76);
insert into Score values('05' , '02' , 87);
insert into Score values('06' , '01' , 31);
insert into Score values('06' , '03' , 34);
insert into Score values('07' , '02' , 89);
insert into Score values('07' , '03' , 98);

题型

1. 查询课程编号为“01”的课程比“02”的课程成绩高的所有学生的学号

SELECT
    t1.s_id,
    t3.s_name,
    t1.s_score '01',
    t2.s_score '02'
FROM
    Score AS t1
    JOIN Score AS t2 ON t1.s_id = t2.s_id 
    JOIN Student as t3 on t1.s_id=t3.s_id
    AND t1.s_score > t2.s_score 
    AND t2.c_id = '02' 
WHERE
    t1.c_id = '01'

2. 查询平均成绩大于60分的学生的学号和平均成绩

SELECT
    t1.s_id ,
    avg(t1.s_score )
FROM
    Score AS t1 
GROUP BY
    t1.s_id 
HAVING
    avg(t1.s_score ) > 60

3. 查询所有学生的学号、姓名、选课数、总成绩

SELECT
    t1.s_id,
    t1.s_name,
    count( t2.c_id ) AS c,
    SUM( t2.s_score ) 'total' 
FROM
    Student AS t1
    left JOIN Score AS t2 ON t1.s_id = t2.s_id 
GROUP BY
    t1.s_id

4. 查询姓“猴”的老师的个数

select count(*) from Teacher where t_name like '猴%';

5. 查询没学过“张三”老师课的学生的学号、姓名

SELECT
    * 
FROM
    student 
WHERE
    s_id NOT IN ( SELECT s_id FROM score WHERE c_id = ( SELECT c_id FROM course WHERE t_id = ( SELECT t_id FROM teacher WHERE t_name = '张三' ) ) )

6. 查询学过“张三”老师所教的所有课的同学的学号、姓名

SELECT
    * 
FROM
    student 
WHERE
    s_id  IN ( SELECT s_id FROM score WHERE c_id = ( SELECT c_id FROM course WHERE t_id = ( SELECT t_id FROM teacher WHERE t_name = '张三' ) ) )

7. 查询学过编号为“01”的课程并且也学过编号为“02”的课程的学生的学号、姓名

SELECT
    t1.s_id,
    t3.s_name
FROM
    Score AS t1
    JOIN Score AS t2 ON t1.s_id = t2.s_id
    JOIN Student AS t3 ON t1.s_id = t3.s_id 
WHERE
    t1.c_id = '01' 
    AND t2.c_id = '02' 
GROUP BY
    t1.s_id

8. 查询课程编号为“02”的总成绩

select SUM(s_score) total FROM Score WHERE c_id='02'

9. 查询学生每门课程成绩均小于60分的学生的学号、姓名

SELECT
    t3.s_id,
    t3.s_name 
FROM
    ( SELECT s_id, count(*) total FROM Score WHERE s_score < 60 GROUP BY s_id ) AS t1
    JOIN ( SELECT s_id, count(*) total FROM Score GROUP BY s_id ) AS t2 ON t1.s_id = t2.s_id
    JOIN Student AS t3 ON t1.s_id = t3.s_id 
WHERE
    t1.total = t2.total

10. 查询没有学全所有课的学生的学号、姓名

SELECT
    t2.s_id,
    t2.s_name 
FROM
    Student AS t2
    LEFT JOIN ( SELECT s_id, count(*) total FROM Score GROUP BY s_id ) AS t1 ON t1.s_id = t2.s_id 
WHERE
    total <>(
    SELECT
        COUNT(*) total 
    FROM
        Course 
    ) 
    OR t1.s_id IS NULL

11. 查询至少有一门课与学号为“01”的学生所学课程相同的学生的学号和姓名

SELECT
    Student.s_id,
    Student.s_name 
FROM
    Score
    JOIN Student ON Score.s_id = Student.s_id 
WHERE
    c_id IN ( SELECT c_id FROM Score WHERE s_id = '01' ) 
    AND Score.s_id <> '01' 
GROUP BY
    s_id

12. 查询和“01”号同学所学课程完全相同的其他同学的学号

SELECT
    t1.s_id,COUNT(t2.c_id)
FROM
    Score AS t1
    LEFT JOIN Score AS t2 ON t1.c_id = t2.c_id 
    AND t2.s_id = '01' 
    AND t1.s_id <> '01' 
WHERE
    t1.s_id <> '01'
    GROUP BY t1.s_id
    having count(t2.c_id)=(select count(*) FROM Score where s_id='01')

13. 查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩

SELECT
    t1.s_id,
    t2.s_name,
    AVG( t1.s_score ) AS score 
FROM
    Score AS t1
    JOIN Student AS t2 ON t1.s_id = t2.s_id 
WHERE
    t1.s_id IN ( SELECT s_id FROM score WHERE s_score < 60 GROUP BY s_id HAVING COUNT( s_id )>= 2 ) 
GROUP BY
    t1.s_id;

14. 检索”01”课程分数小于60，按分数降序排列的学生信息

SELECT
    Score.s_id,
    Student.s_name,
    Score.s_score 
FROM
    Score
    JOIN Student ON Score.s_id = Student.s_id 
WHERE
    s_score < 60 
    AND c_id = '01' 
ORDER BY
    s_score DESC

15. 按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩

SELECT
    t1.s_id,
    t1.s_name,
    AVG( t2.s_score ) AS 'avg',
    SUM( t2.s_score ) 'total' 
FROM
    Student AS t1
    LEFT JOIN Score AS t2 ON t1.s_id = t2.s_id 
GROUP BY
    t1.s_id 
ORDER BY
    avg DESC

16. 查询各科成绩最高分、最低分和平均分：以如下形式显示：课程ID，课程name，最高分，最低分，平均分，及格率，中等率，优良率，优秀率 及格为>=60，中等为：70-80，优良为：80-90，优秀为：>=90

SELECT
    t1.c_id '课程ID',
    t2.c_name '课程名',
    MAX( s_score ) '最高分',
    min( s_score ) '最低分',
    avg( s_score ) '平均分',
    SUM( CASE WHEN t1.s_score >= 60 THEN 1 ELSE 0 END )/ COUNT( t1.s_id ) '及格率',
    SUM( CASE WHEN t1.s_score >= 70 AND t1.s_score < 80 THEN 1 ELSE 0 END )/ COUNT( t1.s_id ) '中等率',
    SUM( CASE WHEN t1.s_score >= 80 AND t1.s_score < 90 THEN 1 ELSE 0 END )/ COUNT( t1.s_id ) '优良率',
    SUM( CASE WHEN t1.s_score >= 90 THEN 1 ELSE 0 END )/ COUNT( t1.s_id ) '优秀率' 
FROM
    Score AS t1
    JOIN Course AS t2 ON t1.c_id = t2.c_id 
GROUP BY
    t1.c_id;

17. 查询学生的总成绩并进行排名

SELECT
    t1.s_id,
    t2.s_name,
    SUM( t1.s_score ) AS 'total' 
FROM
    Score AS t1
    JOIN Student AS t2 ON t1.s_id = t2.s_id 
GROUP BY
    t1.s_id 
ORDER BY
    total DESC;

18. 查询每个老师所教课程平均分从高到低显示以课程为主体来求平均分

SELECT
    t1.t_name,
    t2.c_name,
    t3.c_id,
    avg( t3.s_score ) AS 'avg' 
FROM
    Teacher AS t1
    JOIN Course AS t2 ON t1.t_id = t2.t_id
    JOIN Score AS t3 ON t2.c_id = t3.c_id 
GROUP BY
    t3.c_id 
ORDER BY
    avg DESC;

19. 使用分段[100-85],[85-70],[70-60],[<60]来统计各科成绩，分别统计各分数段人数：课程ID和课程名称

SELECT
    t1.c_id,t2.c_name,(
    SUM( CASE WHEN t1.s_score < 60 THEN 1 ELSE 0 END )) AS '0-60',
    (
    SUM( CASE WHEN t1.s_score >= 60 AND t1.s_score <= 70 THEN 1 ELSE 0 END )) AS '60-70',
    (
    SUM( CASE WHEN t1.s_score > 70 AND t1.s_score <= 85 THEN 1 ELSE 0 END )) AS '70-85',
    (
    SUM( CASE WHEN t1.s_score > 85 AND t1.s_score <= 100 THEN 1 ELSE 0 END )) AS '85-100' 
FROM
    Score as t1
    JOIN Course as t2 on t1.c_id=t2.c_id
GROUP BY
    t1.c_id

20. 查询学生平均成绩及其名次

SELECT
    t2.s_name,
    AVG( t1.s_score ) 'avg' 
FROM
    score AS t1
    JOIN Student AS t2 ON t1.s_id = t2.s_id 
GROUP BY
    t1.s_id 
ORDER BY
    'avg' DESC

21. 查询每门课程被选修的学生数

SELECT
    t1.c_id,
    t2.c_name,
    COUNT(t1.s_id) as total
FROM
    Score AS t1
    JOIN Course AS t2 ON t1.c_id = t2.c_id 
GROUP BY
    t1.c_id

22. 查询出只有两门课程的学生的学号和姓名

SELECT
    t1.s_id,
    t2.s_name 
FROM
    Score AS t1
    JOIN Student AS t2 ON t1.s_id = t2.s_id 
GROUP BY
    t1.s_id 
HAVING
    COUNT( t1.c_id )= 2

23. 查询男生、女生人数

SELECT
    s_sex,
    COUNT(*) as total
FROM
    Student 
GROUP BY
    s_sex

24. 查询名字中含有”风”字的学生信息

SELECT *FROM student 
WHERE s_name LIKE '%风%'

25. 查询1990年出生的学生名单

SELECT
    * 
FROM
    Student 
WHERE
    YEAR ( s_birth )= '1990'

26. 查询平均成绩大于等于85的所有学生的学号、姓名和平均成绩

SELECT
    t2.s_name,
    t1.s_id,
    avg( t1.s_score ) 'avg' 
FROM
    Score AS t1
    JOIN Student AS t2 ON t1.s_id = t2.s_id 
GROUP BY
    t1.s_id 
HAVING
    avg( t1.s_score )> 85

27. 查询每门课程的平均成绩，结果按平均成绩升序排序，平均成绩相同时，按课程号降序排列

SELECT
    t1.c_id,
    t2.c_name,
    avg( t1.s_score ) 'avg' 
FROM
    Score AS t1
    JOIN Course AS t2 ON t1.c_id = t2.c_id 
GROUP BY
    t1.c_id 
ORDER BY
    avg ASC,
    t1.c_id DESC

28. 查询课程名称为”数学”，且分数低于60的学生姓名和分数

SELECT
    t2.s_name,
    t1.s_score 
FROM
    Score AS t1
    JOIN Student AS t2 ON t1.s_id = t2.s_id 
WHERE
    t1.c_id =(
    SELECT
        c_id 
    FROM
        Course 
    WHERE
        c_name = '数学' 
    ) 
    AND t1.s_score < 60

29. 查询所有学生的课程及分数情况

SELECT t1.s_id,t1.s_name,
MAX(CASE WHEN t3.c_name='语文' THEN t2.s_score ELSE NULL END)AS '语文',
MAX(CASE WHEN t3.c_name='英语' THEN t2.s_score ELSE NULL END)AS '英语'
MAX(CASE WHEN t3.c_name='数学' THEN t2.s_score ELSE NULL END)AS '数学',
FROM student AS t1 LEFT JOIN 
score AS t2 ON t1.s_id=t2.s_id LEFT JOIN 
course AS t3 ON t2.c_id=t3.c_id 
GROUP BY t1.s_name ,t1.s_id 
ORDER BY t1.s_id

30. 查询任何一门课程成绩在70分以上的姓名、课程名称和分数

SELECT
    t1.s_id,
    t3.s_name,
    GROUP_CONCAT(t4.c_name ) as c_name,
    GROUP_CONCAT(t1.s_score ) as s_score
FROM
    Score AS t1
    JOIN Student AS t3 ON t1.s_id = t3.s_id
    JOIN Course AS t4 ON t1.c_id = t4.c_id 
WHERE
    t1.s_score > 70 
GROUP BY
    t1.s_id 
HAVING
    COUNT( t1.s_score )>=(
    SELECT
        COUNT( t2.s_score ) 
    FROM
        Score AS t2 
    WHERE
        t2.s_id = t1.s_id 
GROUP BY
    t2.s_id)

31. 查询不及格的课程并按课程号从大到小排列

SELECT
    t1.c_id,
    t2.c_name 
FROM
    Score AS t1
    JOIN Course AS t2 ON t1.c_id = t2.c_id 
WHERE
    t1.s_score < 60 
GROUP BY
    t1.c_id 
ORDER BY
    t1.c_id DESC

32. 查询课程编号为03且课程成绩在80分以上的学生的学号和姓名

SELECT
    t1.s_id,
    t2.s_name
FROM
    Score as t1
    JOIN Student as t2 on t1.s_id=t2.s_id
WHERE
    t1.c_id = '03' 
    AND t1.s_score > 80 
GROUP BY
    t1.s_id

33. 查询选修“张三”老师所授课程的学生中成绩最高的学生姓名及其成绩

SELECT
    t3.s_id,
    t4.s_name,
    t3.s_score
FROM
    Score AS t3 
    JOIN Student as t4 on t3.s_id=t4.s_id
WHERE
    t3.c_id =(
    SELECT
        t2.c_id 
    FROM
        Course AS t2 
    WHERE
        t2.t_id =(
        SELECT
            t1.t_id 
        FROM
            Teacher AS t1 
        WHERE
            t1.t_name = '张三' 
        )) 
ORDER BY t3.s_score desc 
LIMIT 1

34. 查询 2 门及以上课程成绩相同的学生的学生编号、课程编号、学生成绩

SELECT
    t1.s_id,
    t3.s_name,
    t1.c_id AS '第一门课程id',
    t1.s_score AS '第一门课程成绩',
    t2.c_id AS '第二门课程id',
    t2.s_score AS '第二门课程成绩'
FROM
    Score AS t1
    INNER JOIN Score AS t2 ON t1.s_id = t2.s_id 
    AND t1.c_id <> t2.c_id 
    AND t1.s_score = t2.s_score
    JOIN Student AS t3 ON t1.s_id = t3.s_id;

35. 查询各学生的年龄

select *,YEAR(NOW())-YEAR(s_birth) as 'age' from Student;

36. 查询选修了全部课程的学生

SELECT
    t1.s_id,
    t2.s_name,
    COUNT( t1.c_id ) AS total 
FROM
    score AS t1
    JOIN Student AS t2 ON t1.s_id = t2.s_id 
GROUP BY
    t1.s_id 
HAVING
    total =(
    SELECT
        COUNT( c_id ) 
FROM
    course)

37. 查询下周过生日的学生

SELECT
    * 
FROM
    student 
WHERE
    WEEK ( '2023-04-11' )+ 1 = WEEK (
        CONCAT(
            YEAR (
            NOW()),
    SUBSTRING( s_birth, 5, 6 )))

38. 查找本月过生日的人

SELECT
    * 
FROM
    student 
WHERE
    MONTH (
    NOW())= MONTH (
    s_birth)