• 数据库系统原理与应用教程（062）—— MySQL 练习题：操作题 32-38（六）

    数据库系统原理与应用教程（062）—— MySQL 练习题：操作题 32-38（六）：分组查询与数据排序

    32、子查询

    题目：查询每个学校的最低 gpa，输出结果按 university 升序排列。

    示例：user_profile 表的数据如下。

    iddevice_idgenderageuniversitygpaactive_days_within_30question_cntanswer_cnt
    12138male21北京大学3.47212
    23214male复旦大学415525
    36543female20北京大学3.212330
    42315female23浙江大学3.6512
    55432male25山东大学3.8201570
    62131male28山东大学3.315713
    74321female26复旦大学3.69652

    查询应返回以下结果：

    device_iduniversitygpa
    6543北京大学3.2000
    4321复旦大学3.6000
    2131山东大学3.3000
    2315浙江大学3.6000

    表结构及数据如下：

    /*
drop table if exists user_profile;
CREATE TABLE `user_profile` (
`id` int NOT NULL,
`device_id` int NOT NULL,
`gender` varchar(14) NOT NULL,
`age` int ,
`university` varchar(32) NOT NULL,
`gpa` float,
`active_days_within_30` int ,
`question_cnt` int ,
`answer_cnt` int 
);

INSERT INTO user_profile VALUES(1,2138,'male',21,'北京大学',3.4,7,2,12);
INSERT INTO user_profile VALUES(2,3214,'male',null,'复旦大学',4.0,15,5,25);
INSERT INTO user_profile VALUES(3,6543,'female',20,'北京大学',3.2,12,3,30);
INSERT INTO user_profile VALUES(4,2315,'female',23,'浙江大学',3.6,5,1,2);
INSERT INTO user_profile VALUES(5,5432,'male',25,'山东大学',3.8,20,15,70);
INSERT INTO user_profile VALUES(6,2131,'male',28,'山东大学',3.3,15,7,13);
INSERT INTO user_profile VALUES(7,4321,'male',28,'复旦大学',3.6,9,6,52);
*/

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22

    解答：

    /*
select a.device_id, b.university, round(b.gpa,4) gpa
from user_profile a join 
       (select university, min(gpa) gpa
        from user_profile group by university) b
        on a.gpa = b.gpa and a.university = b.university
order by a.university;
*/
mysql> select a.device_id, b.university, round(b.gpa,4) gpa
    -> from user_profile a join 
    ->        (select university, min(gpa) gpa
    ->         from user_profile group by university) b
    ->         on a.gpa = b.gpa and a.university = b.university
    -> order by a.university;
+-----------+--------------+--------+
| device_id | university   | gpa    |
+-----------+--------------+--------+
|      6543 | 北京大学     | 3.2000 |
|      4321 | 复旦大学     | 3.6000 |
|      2131 | 山东大学     | 3.3000 |
|      2315 | 浙江大学     | 3.6000 |
+-----------+--------------+--------+
4 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

    33、分组查询与统计（1）

    题目： 查询复旦大学的每个用户在 8 月份练习的题目数和回答正确的题目数，取出相应的明细数据。对于在 8 月份没有练习过的用户，答题数结果返回 0。

    示例：user_profile 表的数据如下。

    iddevice_idgenderageuniversitygpaactive_days_within_30
    12138male21北京大学3.47
    23214male复旦大学4.015
    36543female20北京大学3.212
    42315female23浙江大学3.65
    55432male25山东大学3.820
    62131male28山东大学3.315
    74321female26复旦大学3.69

    示例：question_practice_detail 表的数据如下。

    iddevice_idquestion_idresultdate
    12138111wrong2021-05-03
    23214112wrong2021-05-09
    33214113wrong2021-06-15
    46543111right2021-08-13
    52315115right2021-08-13
    62315116right2021-08-14
    72315117wrong2021-08-15
    ……

    查询应返回以下结果：

    device_iduniversityquestion_cntright_question_cnt
    3214复旦大学30
    4321复旦大学00

    表结构及数据如下：

    /*
drop table if exists `user_profile`;
drop table if  exists `question_practice_detail`;

CREATE TABLE `user_profile` (
`id` int NOT NULL,
`device_id` int NOT NULL,
`gender` varchar(14) NOT NULL,
`age` int ,
`university` varchar(32) NOT NULL,
`gpa` float,
`active_days_within_30` int
);

CREATE TABLE `question_practice_detail` (
`id` int NOT NULL,
`device_id` int NOT NULL,
`question_id`int NOT NULL,
`result` varchar(32) NOT NULL,
`date` date NOT NULL
);

INSERT INTO user_profile VALUES(1,2138,'male',21,'北京大学',3.4,7);
INSERT INTO user_profile VALUES(2,3214,'male',null,'复旦大学',4.0,15);
INSERT INTO user_profile VALUES(3,6543,'female',20,'北京大学',3.2,12);
INSERT INTO user_profile VALUES(4,2315,'female',23,'浙江大学',3.6,5);
INSERT INTO user_profile VALUES(5,5432,'male',25,'山东大学',3.8,20);
INSERT INTO user_profile VALUES(6,2131,'male',28,'山东大学',3.3,15);
INSERT INTO user_profile VALUES(7,4321,'male',28,'复旦大学',3.6,9);

INSERT INTO question_practice_detail VALUES(1,2138,111,'wrong','2021-05-03');
INSERT INTO question_practice_detail VALUES(2,3214,112,'wrong','2021-05-09');
INSERT INTO question_practice_detail VALUES(3,3214,113,'wrong','2021-06-15');
INSERT INTO question_practice_detail VALUES(4,6543,111,'right','2021-08-13');
INSERT INTO question_practice_detail VALUES(5,2315,115,'right','2021-08-13');
INSERT INTO question_practice_detail VALUES(6,2315,116,'right','2021-08-14');
INSERT INTO question_practice_detail VALUES(7,2315,117,'wrong','2021-08-15');
INSERT INTO question_practice_detail VALUES(8,3214,112,'wrong','2021-05-09');
INSERT INTO question_practice_detail VALUES(9,3214,113,'wrong','2021-08-15');
INSERT INTO question_practice_detail VALUES(10,6543,111,'right','2021-08-13');
INSERT INTO question_practice_detail VALUES(11,2315,115,'right','2021-08-13');
INSERT INTO question_practice_detail VALUES(12,2315,116,'right','2021-08-14');
INSERT INTO question_practice_detail VALUES(13,2315,117,'wrong','2021-08-15');
INSERT INTO question_practice_detail VALUES(14,3214,112,'wrong','2021-08-16');
INSERT INTO question_practice_detail VALUES(15,3214,113,'wrong','2021-08-18');
INSERT INTO question_practice_detail VALUES(16,6543,111,'right','2021-08-13');
*/

    • 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

    解答：

    /*
select u.device_id, u.university,
       count(question_id) question_cnt, 
       sum(if(q.result = 'right', 1, 0)) right_question_cnt 
from user_profile u left join question_practice_detail q
on u.device_id = q.device_id
where u.university = '复旦大学' and (month(q.date) = 8 or month(q.date) is null)
group by u.device_id, u.university;
*/
mysql> select u.device_id, u.university,
    ->        count(question_id) question_cnt, 
    ->        sum(if(q.result = 'right', 1, 0)) right_question_cnt 
    -> from user_profile u left join question_practice_detail q
    -> on u.device_id = q.device_id
    -> where u.university = '复旦大学' and (month(q.date) = 8 or month(q.date) is null)
    -> group by u.device_id, u.university;
+-----------+--------------+--------------+--------------------+
| device_id | university   | question_cnt | right_question_cnt |
+-----------+--------------+--------------+--------------------+
|      3214 | 复旦大学     |            3 |                  0 |
|      4321 | 复旦大学     |            0 |                  0 |
+-----------+--------------+--------------+--------------------+
2 rows in set (0.00 sec)

/*
 说明：
（1）count(question_id)：此处不能使用 count(*)，因为 count(*) 统计的是所有记录，当然也包括 question_id 为空的记录，count(question_id) 只统计 question_id 不为空的记录
（2）where 必须指定条件：month(q.date) is null，否则即使使用了 left join，不满足连接条件的记录也会被过滤掉

    • 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

    34、分组查询与统计（2）

    题目：查询浙江大学的用户在不同难度题目下答题的正确率情况，取出相应数据并按照准确率升序输出。

    示例： user_profile 表的数据如下。

    iddevice_idgenderageuniversitygpaactive_days_within_30question_cntanswer_cnt
    12138male21北京大学3.47212
    23214male复旦大学415525
    36543female20北京大学3.212330
    42315female23浙江大学3.6512
    55432male25山东大学3.8201570
    62131male28山东大学3.315713
    74321female26复旦大学3.69652

    示例： question_practice_detail 表的数据如下。

    iddevice_idquestion_idresult
    12138111wrong
    23214112wrong
    33214113wrong
    46543111right
    52315115right
    62315116right
    72315117wrong

    示例： question_detail 表的数据如下。

    question_iddifficult_level
    111hard
    112medium
    113easy
    115easy
    116medium
    117easy

    查询应返回以下结果：

    difficult_levelcorrect_rate
    easy0.5000
    medium1.0000

    表结构及数据如下：

    /*
drop table if exists `user_profile`;
drop table if  exists `question_practice_detail`;
drop table if  exists `question_detail`;

CREATE TABLE `user_profile` (
`id` int NOT NULL,
`device_id` int NOT NULL,
`gender` varchar(14) NOT NULL,
`age` int ,
`university` varchar(32) NOT NULL,
`gpa` float,
`active_days_within_30` int ,
`question_cnt` int ,
`answer_cnt` int 
);

CREATE TABLE `question_practice_detail` (
`id` int NOT NULL,
`device_id` int NOT NULL,
`question_id`int NOT NULL,
`result` varchar(32) NOT NULL,
`date` date NOT NULL
);

CREATE TABLE `question_detail` (
`id` int NOT NULL,
`question_id`int NOT NULL,
`difficult_level` varchar(32) NOT NULL
);

INSERT INTO user_profile VALUES(1,2138,'male',21,'北京大学',3.4,7,2,12);
INSERT INTO user_profile VALUES(2,3214,'male',null,'复旦大学',4.0,15,5,25);
INSERT INTO user_profile VALUES(3,6543,'female',20,'北京大学',3.2,12,3,30);
INSERT INTO user_profile VALUES(4,2315,'female',23,'浙江大学',3.6,5,1,2);
INSERT INTO user_profile VALUES(5,5432,'male',25,'山东大学',3.8,20,15,70);
INSERT INTO user_profile VALUES(6,2131,'male',28,'山东大学',3.3,15,7,13);
INSERT INTO user_profile VALUES(7,4321,'male',28,'复旦大学',3.6,9,6,52);

INSERT INTO question_practice_detail VALUES(1,2138,111,'wrong','2021-05-03');
INSERT INTO question_practice_detail VALUES(2,3214,112,'wrong','2021-05-09');
INSERT INTO question_practice_detail VALUES(3,3214,113,'wrong','2021-06-15');
INSERT INTO question_practice_detail VALUES(4,6543,111,'right','2021-08-13');
INSERT INTO question_practice_detail VALUES(5,2315,115,'right','2021-08-13');
INSERT INTO question_practice_detail VALUES(6,2315,116,'right','2021-08-14');
INSERT INTO question_practice_detail VALUES(7,2315,117,'wrong','2021-08-15');
INSERT INTO question_practice_detail VALUES(8,3214,112,'wrong','2021-05-09');
INSERT INTO question_practice_detail VALUES(9,3214,113,'wrong','2021-08-15');
INSERT INTO question_practice_detail VALUES(10,6543,111,'right','2021-08-13');
INSERT INTO question_practice_detail VALUES(11,2315,115,'right','2021-08-13');
INSERT INTO question_practice_detail VALUES(12,2315,116,'right','2021-08-14');
INSERT INTO question_practice_detail VALUES(13,2315,117,'wrong','2021-08-15');
INSERT INTO question_practice_detail VALUES(14,3214,112,'wrong','2021-08-16');
INSERT INTO question_practice_detail VALUES(15,3214,113,'wrong','2021-08-18');
INSERT INTO question_practice_detail VALUES(16,6543,111,'right','2021-08-13');

INSERT INTO question_detail VALUES(1,111,'hard');
INSERT INTO question_detail VALUES(2,112,'medium');
INSERT INTO question_detail VALUES(3,113,'easy');
INSERT INTO question_detail VALUES(4,115,'easy');
INSERT INTO question_detail VALUES(5,116,'medium');
INSERT INTO question_detail VALUES(6,117,'easy');
*/

    • 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
    • 52
    • 53
    • 54
    • 55
    • 56
    • 57
    • 58
    • 59
    • 60
    • 61
    • 62
    • 63

    解答：

    /*
select b.difficult_level,
       round(sum(if(result = 'right', 1, 0))/count(*), 4) correct_rate
from question_practice_detail a join question_detail b
on a.question_id = b.question_id
where a.device_id in (select device_id from user_profile where university = '浙江大学')
group by b.difficult_level
order by correct_rate;
*/
mysql> select b.difficult_level,
    ->        round(sum(if(result = 'right', 1, 0))/count(*), 4) correct_rate
    -> from question_practice_detail a join question_detail b
    -> on a.question_id = b.question_id
    -> where a.device_id in (select device_id from user_profile where university = '浙江大学')
    -> group by b.difficult_level
    -> order by correct_rate;
+-----------------+--------------+
| difficult_level | correct_rate |
+-----------------+--------------+
| easy            |       0.5000 |
| medium          |       1.0000 |
+-----------------+--------------+
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

    35、对查询结果排序（1）

    题目：查询用户信息表中的用户年龄，取出相应数据并按照年龄升序排序。

    示例：user_profile 表的数据如下。

    iddevice_idgenderageuniversitygpa
    12138male21北京大学3.4
    23214male23复旦大学4
    36543female20北京大学3.2
    42315female23浙江大学3.6
    55432male25山东大学3.8
    62131male28北京师范大学3.3

    查询应返回以下结果：

    device_idage
    653420
    213821
    321423
    231523
    543225
    213128

    表结构及数据如下：

    /*
drop table if exists user_profile;
CREATE TABLE `user_profile` (
`id` int NOT NULL,
`device_id` int NOT NULL,
`gender` varchar(14) NOT NULL,
`age` int ,
`university` varchar(32) NOT NULL,
`gpa` float);

INSERT INTO user_profile VALUES(1,2138,'male',21,'北京大学',3.4);
INSERT INTO user_profile VALUES(2,3214,'male',23,'复旦大学',4.0);
INSERT INTO user_profile VALUES(3,6543,'female',20,'北京大学',3.2);
INSERT INTO user_profile VALUES(4,2315,'female',23,'浙江大学',3.6);
INSERT INTO user_profile VALUES(5,5432,'male',25,'山东大学',3.8);
INSERT INTO user_profile VALUES(6,2131,'male',28,'北京师范大学',3.3);
*/

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17

    解答：

    mysql> select device_id, age from user_profile order by age;
+-----------+------+
| device_id | age  |
+-----------+------+
|      6543 |   20 |
|      2138 |   21 |
|      3214 |   23 |
|      2315 |   23 |
|      5432 |   25 |
|      2131 |   28 |
+-----------+------+
6 rows in set (0.00 sec)

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12

    36、对查询结果排序（2）

    题目：查询用户信息表中的年龄和 gpa 数据，并且先按照 gpa 升序排序，再按照年龄升序排序输出。

    示例：user_profile 表的数据如下。

    iddevice_idgenderageuniversitygpa
    12138male21北京大学3.4
    23214male23复旦大学4
    36543female20北京大学3.2
    42315female23浙江大学3.6
    55432male25山东大学3.8
    62131male28北京师范大学3.3

    查询应返回以下结果：

    device_idgpaage
    65433.220
    21313.328
    21383.421
    23153.623
    54323.825
    3214423

    解答：

    mysql> select device_id, gpa, age from user_profile order by gpa, age;
+-----------+------+------+
| device_id | gpa  | age  |
+-----------+------+------+
|      6543 |  3.2 |   20 |
|      2131 |  3.3 |   28 |
|      2138 |  3.4 |   21 |
|      2315 |  3.6 |   23 |
|      5432 |  3.8 |   25 |
|      3214 |    4 |   23 |
+-----------+------+------+
6 rows in set (0.00 sec)

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12

    37、对查询结果排序（3）

    题目：查询用户信息表中对应的数据，并且先按照 gpa 降序、年龄降序排序输出。

    示例：user_profile 表的数据如下。

    iddevice_idgenderageuniversitygpa
    12138male21北京大学3.4
    23214male23复旦大学4
    36543female20北京大学3.2
    42315female23浙江大学3.6
    55432male25山东大学3.8
    62131male28北京师范大学3.3

    查询应返回以下结果：

    device_idgpaage
    3214423
    54323.825
    23153.623
    21383.421
    21313.328
    65433.220

    解答：

    mysql> select device_id, gpa, age from user_profile order by gpa desc, age desc;
+-----------+------+------+
| device_id | gpa  | age  |
+-----------+------+------+
|      3214 |    4 |   23 |
|      5432 |  3.8 |   25 |
|      2315 |  3.6 |   23 |
|      2138 |  3.4 |   21 |
|      2131 |  3.3 |   28 |
|      6543 |  3.2 |   20 |
+-----------+------+------+
6 rows in set (0.00 sec)

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12

    38、聚合函数的使用

    题目：查询 2021 年 8月份所有练习过题目的总用户数和练习过题目的总次数，取出相应结果。

    示例：question_practice_detail 表的数据如下。

    iddevice_idquestion_idresultdate
    12138111wrong2021-05-03
    23214112wrong2021-05-09
    33214113wrong2021-06-15
    46543111right2021-08-13
    52315115right2021-08-13
    62315116right2021-08-14
    72315117wrong2021-08-15
    ……

    查询应返回以下结果：

    did_cntquestion_cnt
    312

    解答：

    /*
select count(distinct device_id) did_cnt,
       count(*) question_cnt
from question_practice_detail
where date between '2021-8-1' and '2021-8-31';
*/
mysql> select count(distinct device_id) did_cnt,
    ->        count(*) question_cnt
    -> from question_practice_detail
    -> where date between '2021-8-1' and '2021-8-31';
+---------+--------------+
| did_cnt | question_cnt |
+---------+--------------+
|       3 |           12 |
+---------+--------------+
1 row in set (0.02 sec)

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
  • 相关阅读:
    第58节——redux-toolkit中的createAsyncThunk
    ARM架构源码编译RXTX LINUX JAVA串口开发项目部署
    面试官：你对Redis缓存了解吗？面对这11道面试题你是否有很多问号？
    21. 概率与统计 - 数学期望、统计描述&分布
    SMTP协议浅析
    [附源码]计算机毕业设计在线招聘网站Springboot程序
    【C++进阶之路】第八篇：智能指针
    HarmonyOS/OpenHarmony原生应用-ArkTS万能卡片组件Badge
    Python某建筑平台数据, 实现网站JS逆向解密
    2023-09-26力扣每日一题-水题
  • 原文地址：https://blog.csdn.net/weixin_44377973/article/details/126032257