数据库系统原理与应用教程（076）—— MySQL 练习题：操作题 160-167（二十）：综合练习

160、聚合函数的使用（1）

该题目使用的表和数据如下：

/*
drop table if exists resume_info;
CREATE TABLE resume_info (
id int(4) NOT NULL,
job varchar(64) NOT NULL,
date date NOT NULL,
num int(11) NOT NULL,
PRIMARY KEY (id));

INSERT INTO resume_info VALUES
(1,'C++','2025-01-02',53),
(2,'Python','2025-01-02',23),
(3,'Java','2025-01-02',12),
(4,'Java','2025-02-03',24),
(5,'C++','2025-02-03',23),
(6,'Python','2025-02-03',34),
(7,'Python','2025-03-04',54),
(8,'C++','2025-03-04',65),
(9,'Java','2025-03-04',92),
(10,'Java','2026-01-04',230);
*/
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

简历信息表：resume_info，表中数据如下：

mysql> select * from resume_info;
+----+--------+------------+-----+
| id | job    | date       | num |
+----+--------+------------+-----+
|  1 | C++    | 2025-01-02 |  53 |
|  2 | Python | 2025-01-02 |  23 |
|  3 | Java   | 2025-01-02 |  12 |
|  4 | Java   | 2025-02-03 |  24 |
|  5 | C++    | 2025-02-03 |  23 |
|  6 | Python | 2025-02-03 |  34 |
|  7 | Python | 2025-03-04 |  54 |
|  8 | C++    | 2025-03-04 |  65 |
|  9 | Java   | 2025-03-04 |  92 |
| 10 | Java   | 2026-01-04 | 230 |
+----+--------+------------+-----+
10 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

【问题】请编写 SQL 语句，查询在 2025 年内投递简历的岗位和数量，并且按数量降序排序，查询结果如下：

解答：

/*
select job, sum(num) cnt
from resume_info where date between '2025-1-1' and '2025-12-31'
group by job
order by cnt desc;
*/
mysql> select job, sum(num) cnt
    -> from resume_info where date between '2025-1-1' and '2025-12-31'
    -> group by job
    -> order by cnt desc;
+--------+------+
| job    | cnt  |
+--------+------+
| C++    |  141 |
| Java   |  128 |
| Python |  111 |
+--------+------+
3 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

161、聚合函数的使用（2）

该题目使用的表和数据如下：

/*
drop table if exists resume_info;
CREATE TABLE resume_info (
id int(4) NOT NULL,
job varchar(64) NOT NULL,
date date NOT NULL,
num int(11) NOT NULL,
PRIMARY KEY (id));

INSERT INTO resume_info VALUES
(1,'C++','2025-01-02',53),
(2,'Python','2025-01-02',23),
(3,'Java','2025-01-02',12),
(4,'C++','2025-01-03',54),
(5,'Python','2025-01-03',43),
(6,'Java','2025-01-03',41),
(7,'Java','2025-02-03',24),
(8,'C++','2025-02-03',23),
(9,'Python','2025-02-03',34),
(10,'Java','2025-02-04',42),
(11,'C++','2025-02-04',45),
(12,'Python','2025-02-04',59),
(13,'Python','2025-03-04',54),
(14,'C++','2025-03-04',65),
(15,'Java','2025-03-04',92),
(16,'Python','2025-03-05',34),
(17,'C++','2025-03-05',34),
(18,'Java','2025-03-05',34),
(19,'Python','2026-01-04',230),
(20,'C++','2026-02-06',231);
*/
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31

简历信息表：resume_info，表中数据如下：

mysql> select * from resume_info;
+----+--------+------------+-----+
| id | job    | date       | num |
+----+--------+------------+-----+
|  1 | C++    | 2025-01-02 |  53 |
|  2 | Python | 2025-01-02 |  23 |
|  3 | Java   | 2025-01-02 |  12 |
|  4 | C++    | 2025-01-03 |  54 |
|  5 | Python | 2025-01-03 |  43 |
|  6 | Java   | 2025-01-03 |  41 |
|  7 | Java   | 2025-02-03 |  24 |
|  8 | C++    | 2025-02-03 |  23 |
|  9 | Python | 2025-02-03 |  34 |
| 10 | Java   | 2025-02-04 |  42 |
| 11 | C++    | 2025-02-04 |  45 |
| 12 | Python | 2025-02-04 |  59 |
| 13 | Python | 2025-03-04 |  54 |
| 14 | C++    | 2025-03-04 |  65 |
| 15 | Java   | 2025-03-04 |  92 |
| 16 | Python | 2025-03-05 |  34 |
| 17 | C++    | 2025-03-05 |  34 |
| 18 | Java   | 2025-03-05 |  34 |
| 19 | Python | 2026-01-04 | 230 |
| 20 | C++    | 2026-02-06 | 231 |
+----+--------+------------+-----+
20 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26

【问题】请编写 SQL 语句，查询在 2025 年内投递简历的每个岗位，每一个月内收到简历的数量，先按月份降序排序，再按简历数目降序排序，查询结果如下:

/*
select job, 
       concat(left(date, 4), '-', substr(date, 6, 2)) mon,
       sum(num) cnt
from resume_info
where date between '2025-1-1' and '2025-12-31'
group by job, mon
order by mon desc, cnt desc;
*/
mysql> select job, 
    ->        concat(left(date, 4), '-', substr(date, 6, 2)) mon,
    ->        sum(num) cnt
    -> from resume_info
    -> where date between '2025-1-1' and '2025-12-31'
    -> group by job, mon
    -> order by mon desc, cnt desc;
+--------+---------+------+
| job    | mon     | cnt  |
+--------+---------+------+
| Java   | 2025-03 |  126 |
| C++    | 2025-03 |   99 |
| Python | 2025-03 |   88 |
| Python | 2025-02 |   93 |
| C++    | 2025-02 |   68 |
| Java   | 2025-02 |   66 |
| C++    | 2025-01 |  107 |
| Python | 2025-01 |   66 |
| Java   | 2025-01 |   53 |
+--------+---------+------+
9 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30

162、聚合函数的使用（3）

该题目使用的表和数据如下：

/*
drop table if exists resume_info;
CREATE TABLE resume_info (
id int(4) NOT NULL,
job varchar(64) NOT NULL,
date date NOT NULL,
num int(11) NOT NULL,
PRIMARY KEY (id));

INSERT INTO resume_info VALUES
(1,'C++','2025-01-02',53),
(2,'Python','2025-01-02',23),
(3,'Java','2025-01-02',12),
(4,'C++','2025-01-03',54),
(5,'Python','2025-01-03',43),
(6,'Java','2025-01-03',41),
(7,'Java','2025-02-03',24),
(8,'C++','2025-02-03',23),
(9,'Python','2025-02-03',34),
(10,'Java','2025-02-04',42),
(11,'C++','2025-02-04',45),
(12,'Python','2025-02-04',59),
(13,'C++','2026-01-04',230),
(14,'Java','2026-01-04',764),
(15,'Python','2026-01-04',644),
(16,'C++','2026-01-06',240),
(17,'Java','2026-01-06',714),
(18,'Python','2026-01-06',624),
(19,'C++','2026-02-14',260),
(20,'Java','2026-02-14',721),
(21,'Python','2026-02-14',321),
(22,'C++','2026-02-24',134),
(23,'Java','2026-02-24',928),
(24,'Python','2026-02-24',525),
(25,'C++','2027-02-06',231);
*/
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36

简历信息表：resume_info，表中数据如下：

mysql> select * from resume_info;
+----+--------+------------+-----+
| id | job    | date       | num |
+----+--------+------------+-----+
|  1 | C++    | 2025-01-02 |  53 |
|  2 | Python | 2025-01-02 |  23 |
|  3 | Java   | 2025-01-02 |  12 |
|  4 | C++    | 2025-01-03 |  54 |
|  5 | Python | 2025-01-03 |  43 |
|  6 | Java   | 2025-01-03 |  41 |
|  7 | Java   | 2025-02-03 |  24 |
|  8 | C++    | 2025-02-03 |  23 |
|  9 | Python | 2025-02-03 |  34 |
| 10 | Java   | 2025-02-04 |  42 |
| 11 | C++    | 2025-02-04 |  45 |
| 12 | Python | 2025-02-04 |  59 |
| 13 | C++    | 2026-01-04 | 230 |
| 14 | Java   | 2026-01-04 | 764 |
| 15 | Python | 2026-01-04 | 644 |
| 16 | C++    | 2026-01-06 | 240 |
| 17 | Java   | 2026-01-06 | 714 |
| 18 | Python | 2026-01-06 | 624 |
| 19 | C++    | 2026-02-14 | 260 |
| 20 | Java   | 2026-02-14 | 721 |
| 21 | Python | 2026-02-14 | 321 |
| 22 | C++    | 2026-02-24 | 134 |
| 23 | Java   | 2026-02-24 | 928 |
| 24 | Python | 2026-02-24 | 525 |
| 25 | C++    | 2027-02-06 | 231 |
+----+--------+------------+-----+
25 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31

【问题】请编写 SQL 语句，查询在 2025 年投递简历的每个岗位，每一个月内收到简历的数目，和对应的 2026 年的同一个月同岗位，收到简历的数目，最后的结果先按 first_year_mon 月份降序，再按 job 降序排序显示，查询结果如下：

解答：

/*
select a.job, a.mon first_year_mon, a.cnt first_year_cnt, 
       b.mon second_year_mon, b.cnt second_year_cnt
from
(select job, 
       concat(left(date, 4), '-', substr(date, 6, 2)) mon,
       sum(num) cnt
 from resume_info
 where date between '2025-1-1' and '2025-12-31'
 group by job, mon
 order by mon desc, cnt desc) a join
(select job, 
       concat(left(date, 4), '-', substr(date, 6, 2)) mon,
       sum(num) cnt
 from resume_info
 where date between '2026-1-1' and '2026-12-31'
 group by job, mon
 order by mon desc, cnt desc) b
 on a.job = b.job and right(a.mon, 2) = right(b.mon, 2)
 order by first_year_mon desc, job desc;
 */
 mysql> select a.job, a.mon first_year_mon, a.cnt first_year_cnt, 
    ->        b.mon second_year_mon, b.cnt second_year_cnt
    -> from
    -> (select job, 
    ->        concat(left(date, 4), '-', substr(date, 6, 2)) mon,
    ->        sum(num) cnt
    ->  from resume_info
    ->  where date between '2025-1-1' and '2025-12-31'
    ->  group by job, mon
    ->  order by mon desc, cnt desc) a join
    -> (select job, 
    ->        concat(left(date, 4), '-', substr(date, 6, 2)) mon,
    ->        sum(num) cnt
    ->  from resume_info
    ->  where date between '2026-1-1' and '2026-12-31'
    ->  group by job, mon
    ->  order by mon desc, cnt desc) b
    ->  on a.job = b.job and right(a.mon, 2) = right(b.mon, 2)
    ->  order by first_year_mon desc, job desc;
+--------+----------------+----------------+-----------------+-----------------+
| job    | first_year_mon | first_year_cnt | second_year_mon | second_year_cnt |
+--------+----------------+----------------+-----------------+-----------------+
| Python | 2025-02        |             93 | 2026-02         |             846 |
| Java   | 2025-02        |             66 | 2026-02         |            1649 |
| C++    | 2025-02        |             68 | 2026-02         |             394 |
| Python | 2025-01        |             66 | 2026-01         |            1268 |
| Java   | 2025-01        |             53 | 2026-01         |            1478 |
| C++    | 2025-01        |            107 | 2026-01         |             470 |
+--------+----------------+----------------+-----------------+-----------------+
6 rows in set (0.01 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51

163、聚合函数的使用（4）

该题目使用的表和数据如下：

/*
drop table if exists user;
drop table if exists grade_info;

CREATE TABLE user (
id  int(4) NOT NULL,
name varchar(32) NOT NULL
);

CREATE TABLE grade_info (
user_id  int(4) NOT NULL,
grade_num int(4) NOT NULL,
type varchar(32) NOT NULL
);

INSERT INTO user VALUES
(1,'tm'),
(2,'wwy'),
(3,'zk'),
(4,'qq'),
(5,'lm');

INSERT INTO grade_info VALUES
(1,3,'add'),
(2,3,'add'),
(1,1,'add'),
(3,3,'add'),
(4,3,'add'),
(5,3,'add');
*/
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30

用户表：user，表中数据如下：

mysql> select * from user;
+----+------+
| id | name |
+----+------+
|  1 | tm   |
|  2 | wwy  |
|  3 | zk   |
|  4 | qq   |
|  5 | lm   |
+----+------+
5 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11

积分表：grade_info，表中数据如下：

mysql> select * from grade_info;
+---------+-----------+------+
| user_id | grade_num | type |
+---------+-----------+------+
|       1 |         3 | add  |
|       2 |         3 | add  |
|       1 |         1 | add  |
|       3 |         3 | add  |
|       4 |         3 | add  |
|       5 |         3 | add  |
+---------+-----------+------+
6 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12

【问题】请编写一个 SQL 语句，查找积分增加最高的用户的名字，以及他的总积分是多少，查询结果如下：

解答：

/*
select u.name, a.grade_num
from (select user_id, sum(grade_num) grade_num
      from grade_info
      group by user_id
      order by grade_num desc limit 1) a join user u
      on a.user_id = u.id;
*/
mysql> select u.name, a.grade_num
    -> from (select user_id, sum(grade_num) grade_num
    ->       from grade_info
    ->       group by user_id
    ->       order by grade_num desc limit 1) a join user u
    ->       on a.user_id = u.id;
+------+-----------+
| name | grade_num |
+------+-----------+
| tm   |         4 |
+------+-----------+
1 row in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

164、聚合函数的使用（5）

该题目使用的表和数据如下：

/*
drop table if exists user;
drop table if exists grade_info;

CREATE TABLE user (
id  int(4) NOT NULL,
name varchar(32) NOT NULL
);

CREATE TABLE grade_info (
user_id  int(4) NOT NULL,
grade_num int(4) NOT NULL,
type varchar(32) NOT NULL
);

INSERT INTO user VALUES
(1,'tm'),
(2,'wwy'),
(3,'zk'),
(4,'qq'),
(5,'lm');

INSERT INTO grade_info VALUES
(1,3,'add'),
(2,3,'add'),
(1,1,'add'),
(3,3,'add'),
(4,3,'add'),
(5,3,'add'),
(3,1,'add'); 
*/
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31

用户表：user，表中数据如下：

mysql> select * from user;
+----+------+
| id | name |
+----+------+
|  1 | tm   |
|  2 | wwy  |
|  3 | zk   |
|  4 | qq   |
|  5 | lm   |
+----+------+
5 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11

积分表：grade_info，表中数据如下：

mysql> select * from grade_info;
+---------+-----------+------+
| user_id | grade_num | type |
+---------+-----------+------+
|       1 |         3 | add  |
|       2 |         3 | add  |
|       1 |         1 | add  |
|       3 |         3 | add  |
|       4 |         3 | add  |
|       5 |         3 | add  |
|       3 |         1 | add  |
+---------+-----------+------+
7 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13

【问题】请编写 SQL 语句，查找积分增加最高的用户的 id，名字以及总积分，查询结果按照 id 升序排序，查询结果如下：

解答：

/*
select u.id, u.name, sum(if(gi.type = 'add', grade_num, - grade_num)) grade_num
from user u join grade_info gi on u.id = gi.user_id
group by u.id, u.name
having grade_num in
    (select max(grade_num) from 
         (select sum(if(type = 'add', grade_num, - grade_num)) grade_num 
          from grade_info group by user_id) a)
order by u.id;
*/
mysql> select u.id, u.name, sum(grade_num) grade_num
    -> from user u join grade_info gi on u.id = gi.user_id
    -> group by u.id, u.name
    -> having grade_num in
    ->     (select max(grade_num) from 
    ->          (select sum(grade_num) grade_num from grade_info group by user_id) a)
    -> order by u.id;
+----+------+-----------+
| id | name | grade_num |
+----+------+-----------+
|  1 | tm   |         4 |
|  3 | zk   |         4 |
+----+------+-----------+
2 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

165、聚合函数的使用（6）

该题目使用的表和数据如下：

/*
drop table if exists user;
drop table if exists grade_info;

CREATE TABLE user (
id  int(4) NOT NULL,
name varchar(32) NOT NULL
);

CREATE TABLE grade_info (
user_id  int(4) NOT NULL,
grade_num int(4) NOT NULL,
type varchar(32) NOT NULL
);

INSERT INTO user VALUES
(1,'tm'),
(2,'wwy'),
(3,'zk'),
(4,'qq'),
(5,'lm');

INSERT INTO grade_info VALUES
(1,3,'add'),
(2,3,'add'),
(1,1,'reduce'),
(3,3,'add'),
(4,3,'add'),
(5,3,'add'),
(3,1,'reduce');
*/
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31

用户表：user，表中数据如下：

mysql> select * from user;
+----+------+
| id | name |
+----+------+
|  1 | tm   |
|  2 | wwy  |
|  3 | zk   |
|  4 | qq   |
|  5 | lm   |
+----+------+
5 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11

积分表：grade_info，表中数据如下：

mysql> select * from grade_info;
+---------+-----------+--------+
| user_id | grade_num | type   |
+---------+-----------+--------+
|       1 |         3 | add    |
|       2 |         3 | add    |
|       1 |         1 | reduce |
|       3 |         3 | add    |
|       4 |         3 | add    |
|       5 |         3 | add    |
|       3 |         1 | reduce |
+---------+-----------+--------+
7 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13

【问题】请编写一个 SQL 语句，查找积分增加最高的用户的名字，以及他的总积分是多少，查询结果如下：

解答：

/*
select u.id, u.name, sum(if(gi.type = 'add', grade_num, - grade_num)) grade_num
from user u join grade_info gi on u.id = gi.user_id
group by u.id, u.name
having grade_num in
    (select max(grade_num) from 
         (select sum(if(type = 'add', grade_num, - grade_num)) grade_num 
          from grade_info group by user_id) a)
order by u.id;
*/
mysql> select u.id, u.name, sum(if(gi.type = 'add', grade_num, - grade_num)) grade_num
    -> from user u join grade_info gi on u.id = gi.user_id
    -> group by u.id, u.name
    -> having grade_num in
    ->     (select max(grade_num) from 
    ->          (select sum(if(type = 'add', grade_num, - grade_num)) grade_num 
    ->           from grade_info group by user_id) a)
    -> order by u.id;
+----+------+-----------+
| id | name | grade_num |
+----+------+-----------+
|  2 | wwy  |         3 |
|  4 | qq   |         3 |
|  5 | lm   |         3 |
+----+------+-----------+
3 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26

166、聚合函数的使用（7）

该题目使用的表和数据如下：

/*
CREATE TABLE `goods` (
  `id` int(11) NOT NULL,
  `name` varchar(10)  DEFAULT NULL,
  `weight` int(11) NOT NULL,
  PRIMARY KEY (`id`)
);
CREATE TABLE `trans` (
  `id` int(11) NOT NULL,
  `goods_id` int(11) NOT NULL,
  `count` int(11) NOT NULL,
  PRIMARY KEY (`id`)
);
insert into goods values(1,'A1',100);
insert into goods values(2,'A2',20);
insert into goods values(3,'B3',29);
insert into goods values(4,'T1',60);
insert into goods values(5,'G2',33);
insert into goods values(6,'C0',55);
insert into trans values(1,3,10);
insert into trans values(2,1,44);
insert into trans values(3,6,9);
insert into trans values(4,1,2);
insert into trans values(5,2,65);
insert into trans values(6,5,23);
insert into trans values(7,3,20);
insert into trans values(8,2,16);
insert into trans values(9,4,5);
insert into trans values(10,1,3);
*/
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30

商品表：goods，字段依次为：商品id，商品名，商品质量。表中数据如下：

mysql> select * from goods;
+----+------+--------+
| id | name | weight |
+----+------+--------+
|  1 | A1   |    100 |
|  2 | A2   |     20 |
|  3 | B3   |     29 |
|  4 | T1   |     60 |
|  5 | G2   |     33 |
|  6 | C0   |     55 |
+----+------+--------+
6 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12

交易表：trans，字段依次为：交易id，商品id，商品购买个数。表中数据如下：

mysql> select * from trans;
+----+----------+-------+
| id | goods_id | count |
+----+----------+-------+
|  1 |        3 |    10 |
|  2 |        1 |    44 |
|  3 |        6 |     9 |
|  4 |        1 |     2 |
|  5 |        2 |    65 |
|  6 |        5 |    23 |
|  7 |        3 |    20 |
|  8 |        2 |    16 |
|  9 |        4 |     5 |
| 10 |        1 |     3 |
+----+----------+-------+
10 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

【问题】查询购买个数超过 20、质量小于 50 的商品，按照商品 id 升序排序，查询结果如下：

解答：

/*
select g.id, g.name, g.weight, sum(count) total
from trans t join goods g on t.goods_id = g.id
where g.weight < 50
group by g.id, g.name, g.weight
having total > 20
order by g.id;
*/
mysql> select g.id, g.name, g.weight, sum(count) total
    -> from trans t join goods g on t.goods_id = g.id
    -> where g.weight < 50
    -> group by g.id, g.name, g.weight
    -> having total > 20
    -> order by g.id;
+----+------+--------+-------+
| id | name | weight | total |
+----+------+--------+-------+
|  2 | A2   |     20 |    81 |
|  3 | B3   |     29 |    30 |
|  5 | G2   |     33 |    23 |
+----+------+--------+-------+
3 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

167、连接查询

该题目使用的表和数据如下：

/*
CREATE TABLE `follow` (
`user_id` int(4) NOT NULL,
`follower_id` int(4) NOT NULL,
PRIMARY KEY (`user_id`,`follower_id`));

CREATE TABLE `music_likes` (
`user_id` int(4) NOT NULL,
`music_id` int(4) NOT NULL,
PRIMARY KEY (`user_id`,`music_id`));

CREATE TABLE `music` (
`id` int(4) NOT NULL,
`music_name` varchar(32) NOT NULL,
PRIMARY KEY (`id`));

INSERT INTO follow VALUES(1,2);
INSERT INTO follow VALUES(1,4);
INSERT INTO follow VALUES(2,3);

INSERT INTO music_likes VALUES(1,17);
INSERT INTO music_likes VALUES(2,18);
INSERT INTO music_likes VALUES(2,19);
INSERT INTO music_likes VALUES(3,20);
INSERT INTO music_likes VALUES(4,17);

INSERT INTO music VALUES(17,'yueyawang');
INSERT INTO music VALUES(18,'kong');
INSERT INTO music VALUES(19,'MOM');
INSERT INTO music VALUES(20,'Sold Out');
*/
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31

关注表：follow，第一列是关注人的 id，第二列是被关注人的 id，这 2 列的 id 组成主键，表中数据如下：

mysql> select * from follow;
+---------+-------------+
| user_id | follower_id |
+---------+-------------+
|       1 |           2 |
|       1 |           4 |
|       2 |           3 |
+---------+-------------+
3 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9

个人的喜欢的音乐表：music_likes，第一列是用户 id，第二列是喜欢的音乐 id，这 2 列的 id 组成主键，表中数据如下：

mysql> select * from music_likes;
+---------+----------+
| user_id | music_id |
+---------+----------+
|       1 |       17 |
|       2 |       18 |
|       2 |       19 |
|       3 |       20 |
|       4 |       17 |
+---------+----------+
5 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11

音乐表：music，第一列是音乐 id，第二列是音乐 name，id 是主键，表中数据如下：

mysql> select * from music;
+----+------------+
| id | music_name |
+----+------------+
| 17 | yueyawang  |
| 18 | kong       |
| 19 | MOM        |
| 20 | Sold Out   |
+----+------------+
4 rows in set (0.02 sec)
1
2
3
4
5
6
7
8
9
10

【问题】请编写一个 SQL 语句，查询向 user_id = 1 的用户，推荐其关注的人喜欢的音乐。不要推荐该用户已经喜欢的音乐，并且按 music 的 id 升序排列。查询结果中不包含重复项。

查询结果如下：

解答：

/*
select distinct m.id, m.music_name 
from music m join
     (select user_id, music_id from music_likes
      where user_id in (select follower_id from follow where user_id = 1)) a 
      on a.music_id = m.id
where a.music_id not in (select music_id from music_likes where user_id = 1)
order by m.id;
*/
mysql> select distinct m.id, m.music_name 
    -> from music m join
    ->      (select user_id, music_id from music_likes
    ->       where user_id in (select follower_id from follow where user_id = 1)) a 
    ->       on a.music_id = m.id
    -> where a.music_id not in (select music_id from music_likes where user_id = 1)
    -> order by m.id;
+----+------------+
| id | music_name |
+----+------------+
| 18 | kong       |
| 19 | MOM        |
+----+------------+
2 rows in set (0.02 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23