• 数据库系统原理与应用教程（080）—— MySQL 练习题：操作题 186-193（二十四）：综合练习

    数据库系统原理与应用教程（080）—— MySQL 练习题：操作题 186-193（二十四）：综合练习

    186、连接查询与分组统计（1）

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

    /*
drop table if exists course_tb;
CREATE TABLE course_tb(
course_id int(10) NOT NULL, 
course_name char(10) NOT NULL,
course_datetime char(30) NOT NULL);

INSERT INTO course_tb VALUES(1, 'Python', '2021-12-1 19:00-21:00');
INSERT INTO course_tb VALUES(2, 'SQL', '2021-12-2 19:00-21:00');
INSERT INTO course_tb VALUES(3, 'R', '2021-12-3 19:00-21:00');

drop table if exists behavior_tb;
CREATE TABLE behavior_tb(
user_id int(10) NOT NULL, 
if_vw int(10) NOT NULL,
if_fav int(10) NOT NULL,
if_sign int(10) NOT NULL,
course_id int(10) NOT NULL);

INSERT INTO behavior_tb VALUES(100, 1, 1, 1, 1);
INSERT INTO behavior_tb VALUES(100, 1, 1, 1, 2);
INSERT INTO behavior_tb VALUES(100, 1, 1, 1, 3);
INSERT INTO behavior_tb VALUES(101, 1, 1, 1, 1);
INSERT INTO behavior_tb VALUES(101, 1, 1, 1, 2);
INSERT INTO behavior_tb VALUES(101, 1, 0, 0, 3);
INSERT INTO behavior_tb VALUES(102, 1, 1, 1, 1);
INSERT INTO behavior_tb VALUES(102, 1, 1, 1, 2);
INSERT INTO behavior_tb VALUES(102, 1, 1, 1, 3);
INSERT INTO behavior_tb VALUES(103, 1, 1, 0, 1);
INSERT INTO behavior_tb VALUES(103, 1, 0, 0, 2);
INSERT INTO behavior_tb VALUES(103, 1, 0, 0, 3);
INSERT INTO behavior_tb VALUES(104, 1, 1, 1, 1);
INSERT INTO behavior_tb VALUES(104, 1, 1, 1, 2);
INSERT INTO behavior_tb VALUES(104, 1, 1, 0, 3);
INSERT INTO behavior_tb VALUES(105, 1, 0, 0, 1);
INSERT INTO behavior_tb VALUES(106, 1, 0, 0, 1);
INSERT INTO behavior_tb VALUES(107, 1, 0, 0, 1);
INSERT INTO behavior_tb VALUES(107, 1, 1, 1, 2);
INSERT INTO behavior_tb VALUES(108, 1, 1, 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
    • 31
    • 32
    • 33
    • 34
    • 35
    • 36
    • 37
    • 38
    • 39
    • 40

    课程表：course_tb（course_id：课程编号，course_name：课程名称，course_datetime：上课时间），表中数据如下：

    mysql> select * from course_tb;
+-----------+-------------+-----------------------+
| course_id | course_name | course_datetime       |
+-----------+-------------+-----------------------+
|         1 | Python      | 2021-12-1 19:00-21:00 |
|         2 | SQL         | 2021-12-2 19:00-21:00 |
|         3 | R           | 2021-12-3 19:00-21:00 |
+-----------+-------------+-----------------------+
3 rows in set (0.03 sec)

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9

    用户行为表：behavior_tb（user_id：用户编号，if_vw：是否浏览，if_fav：是否收藏，if_sign：是否报名，course_id：课程编号），表中数据如下：

    mysql> select * from behavior_tb;
+---------+-------+--------+---------+-----------+
| user_id | if_vw | if_fav | if_sign | course_id |
+---------+-------+--------+---------+-----------+
|     100 |     1 |      1 |       1 |         1 |
|     100 |     1 |      1 |       1 |         2 |
|     100 |     1 |      1 |       1 |         3 |
|     101 |     1 |      1 |       1 |         1 |
|     101 |     1 |      1 |       1 |         2 |
|     101 |     1 |      0 |       0 |         3 |
|     102 |     1 |      1 |       1 |         1 |
|     102 |     1 |      1 |       1 |         2 |
|     102 |     1 |      1 |       1 |         3 |
|     103 |     1 |      1 |       0 |         1 |
|     103 |     1 |      0 |       0 |         2 |
|     103 |     1 |      0 |       0 |         3 |
|     104 |     1 |      1 |       1 |         1 |
|     104 |     1 |      1 |       1 |         2 |
|     104 |     1 |      1 |       0 |         3 |
|     105 |     1 |      0 |       0 |         1 |
|     106 |     1 |      0 |       0 |         1 |
|     107 |     1 |      0 |       0 |         1 |
|     107 |     1 |      1 |       1 |         2 |
|     108 |     1 |      1 |       1 |         3 |
+---------+-------+--------+---------+-----------+
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

    【问题】请统计每个科目的转换率（sign_rate(%)，转化率 = 报名人数/浏览人数，结果保留两位小数），按照 course_id升序排序，查询结果如下：

    course_idcourse_namesign_rate(%)
    1Python50.00
    2SQL83.33
    3R50.00

    解答：

    /*
select c.course_id, c.course_name,
       round(sum(if_sign) / sum(if_vw) * 100 , 2) `sign_rate(%)`
from course_tb c join behavior_tb b 
on c.course_id = b.course_id
group by c.course_id, c.course_name;
*/
mysql> select c.course_id, c.course_name,
    ->        round(sum(if_sign) / sum(if_vw) * 100 , 2) `sign_rate(%)`
    -> from course_tb c join behavior_tb b 
    -> on c.course_id = b.course_id
    -> group by c.course_id, c.course_name;
+-----------+-------------+--------------+
| course_id | course_name | sign_rate(%) |
+-----------+-------------+--------------+
|         1 | Python      |        50.00 |
|         2 | SQL         |        83.33 |
|         3 | R           |        50.00 |
+-----------+-------------+--------------+
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

    187、连接查询与分组统计（2）

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

    /*
drop table if exists course_tb;
CREATE TABLE course_tb(
course_id int(10) NOT NULL, 
course_name char(10) NOT NULL,
course_datetime char(30) NOT NULL);
INSERT INTO course_tb VALUES(1, 'Python', '2021-12-1 19:00-21:00');
INSERT INTO course_tb VALUES(2, 'SQL', '2021-12-2 19:00-21:00');
INSERT INTO course_tb VALUES(3, 'R', '2021-12-3 19:00-21:00');

drop table if attend_tb;
CREATE TABLE attend_tb(
user_id int(10) NOT NULL, 
course_id int(10) NOT NULL,
in_datetime datetime NOT NULL,
out_datetime datetime NOT NULL
);
INSERT INTO attend_tb VALUES(100, 1, '2021-12-1 19:00:00', '2021-12-1 19:28:00');
INSERT INTO attend_tb VALUES(100, 1, '2021-12-1 19:30:00', '2021-12-1 19:53:00');
INSERT INTO attend_tb VALUES(101, 1, '2021-12-1 19:00:00', '2021-12-1 20:55:00');
INSERT INTO attend_tb VALUES(102, 1, '2021-12-1 19:00:00', '2021-12-1 19:05:00');
INSERT INTO attend_tb VALUES(104, 1, '2021-12-1 19:00:00', '2021-12-1 20:59:00');
INSERT INTO attend_tb VALUES(101, 2, '2021-12-2 19:05:00', '2021-12-2 20:58:00');
INSERT INTO attend_tb VALUES(102, 2, '2021-12-2 18:55:00', '2021-12-2 21:00:00');
INSERT INTO attend_tb VALUES(104, 2, '2021-12-2 18:57:00', '2021-12-2 20:56:00');
INSERT INTO attend_tb VALUES(107, 2, '2021-12-2 19:10:00', '2021-12-2 19:18:00');
INSERT INTO attend_tb VALUES(100, 3, '2021-12-3 19:01:00', '2021-12-3 21:00:00');
INSERT INTO attend_tb VALUES(102, 3, '2021-12-3 18:58:00', '2021-12-3 19:05:00');
INSERT INTO attend_tb VALUES(108, 3, '2021-12-3 19:01:00', '2021-12-3 19:56:00');
*/

    • 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

    课程表：course_tb（course_id：课程编号，course_name：课程名称，course_datetime：上课时间），表中数据如下：

    mysql> select * from course_tb;
+-----------+-------------+-----------------------+
| course_id | course_name | course_datetime       |
+-----------+-------------+-----------------------+
|         1 | Python      | 2021-12-1 19:00-21:00 |
|         2 | SQL         | 2021-12-2 19:00-21:00 |
|         3 | R           | 2021-12-3 19:00-21:00 |
+-----------+-------------+-----------------------+
3 rows in set (0.00 sec)

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9

    上课情况表：attend_tb（user_id：用户编号，course_id：课程编号，in_datetime：进入直播间的时间，out_datetime：离开直播间的时间），表中数据如下：

    mysql> select * from attend_tb;
+---------+-----------+---------------------+---------------------+
| user_id | course_id | in_datetime         | out_datetime        |
+---------+-----------+---------------------+---------------------+
|     100 |         1 | 2021-12-01 19:00:00 | 2021-12-01 19:28:00 |
|     100 |         1 | 2021-12-01 19:30:00 | 2021-12-01 19:53:00 |
|     101 |         1 | 2021-12-01 19:00:00 | 2021-12-01 20:55:00 |
|     102 |         1 | 2021-12-01 19:00:00 | 2021-12-01 19:05:00 |
|     104 |         1 | 2021-12-01 19:00:00 | 2021-12-01 20:59:00 |
|     101 |         2 | 2021-12-02 19:05:00 | 2021-12-02 20:58:00 |
|     102 |         2 | 2021-12-02 18:55:00 | 2021-12-02 21:00:00 |
|     104 |         2 | 2021-12-02 18:57:00 | 2021-12-02 20:56:00 |
|     107 |         2 | 2021-12-02 19:10:00 | 2021-12-02 19:18:00 |
|     100 |         3 | 2021-12-03 19:01:00 | 2021-12-03 21:00:00 |
|     102 |         3 | 2021-12-03 18:58:00 | 2021-12-03 19:05:00 |
|     108 |         3 | 2021-12-03 19:01:00 | 2021-12-03 19:56:00 |
|     100 |         1 | 2021-12-01 19:00:00 | 2021-12-01 19:28:00 |
|     100 |         1 | 2021-12-01 19:30:00 | 2021-12-01 19:53:00 |
|     101 |         1 | 2021-12-01 19:00:00 | 2021-12-01 20:55:00 |
|     102 |         1 | 2021-12-01 19:00:00 | 2021-12-01 19:05:00 |
|     104 |         1 | 2021-12-01 19:00:00 | 2021-12-01 20:59:00 |
|     101 |         2 | 2021-12-02 19:05:00 | 2021-12-02 20:58:00 |
|     102 |         2 | 2021-12-02 18:55:00 | 2021-12-02 21:00:00 |
|     104 |         2 | 2021-12-02 18:57:00 | 2021-12-02 20:56:00 |
|     107 |         2 | 2021-12-02 19:10:00 | 2021-12-02 19:18:00 |
|     100 |         3 | 2021-12-03 19:01:00 | 2021-12-03 21:00:00 |
|     102 |         3 | 2021-12-03 18:58:00 | 2021-12-03 19:05:00 |
|     108 |         3 | 2021-12-03 19:01:00 | 2021-12-03 19:56:00 |
+---------+-----------+---------------------+---------------------+
24 rows in set (0.03 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

    【问题】请统计直播开始时（19：00），各科目的在线人数（按照 course_id 升序排序），表中数据如下：

    course_idcourse_nameonline_num
    1Python8
    2SQL4
    3R2

    解答：

    /*
select c.course_id, c.course_name,
       count(*) online_num
from course_tb c join attend_tb a
on c.course_id = a.course_id
where time(in_datetime) <= '19:00:00' and time(out_datetime) >= '19:00:00'
group by c.course_id, c.course_name
order by c.course_id;
*/
mysql> SELECT course_tb.course_id, course_name, COUNT(1) AS online_num
    -> FROM course_tb JOIN attend_tb ON course_tb.course_id = attend_tb.course_id
    -> WHERE TIME(in_datetime) <= '19:00:00' AND TIME(out_datetime) >= '19:00:00'
    -> GROUP BY course_tb.course_id, course_name
    -> ORDER BY course_tb.course_id;
+-----------+-------------+------------+
| course_id | course_name | online_num |
+-----------+-------------+------------+
|         1 | Python      |          8 |
|         2 | SQL         |          4 |
|         3 | R           |          2 |
+-----------+-------------+------------+
3 rows in set (0.03 sec)

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

    188、连接查询与分组统计（3）

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

    /*
drop table if exists course_tb;
CREATE TABLE course_tb(
course_id int(10) NOT NULL, 
course_name char(10) NOT NULL,
course_datetime char(30) NOT NULL);

INSERT INTO course_tb VALUES(1, 'Python', '2021-12-1 19:00-21:00');
INSERT INTO course_tb VALUES(2, 'SQL', '2021-12-2 19:00-21:00');
INSERT INTO course_tb VALUES(3, 'R', '2021-12-3 19:00-21:00');

drop table if exists attend_tb;
CREATE TABLE attend_tb(
user_id int(10) NOT NULL, 
course_id int(10) NOT NULL,
in_datetime datetime NOT NULL,
out_datetime datetime NOT NULL
);
INSERT INTO attend_tb VALUES(100, 1, '2021-12-1 19:00:00', '2021-12-1 19:28:00');
INSERT INTO attend_tb VALUES(100, 1, '2021-12-1 19:30:00', '2021-12-1 19:53:00');
INSERT INTO attend_tb VALUES(101, 1, '2021-12-1 19:00:00', '2021-12-1 20:55:00');
INSERT INTO attend_tb VALUES(102, 1, '2021-12-1 19:00:00', '2021-12-1 19:05:00');
INSERT INTO attend_tb VALUES(104, 1, '2021-12-1 19:00:00', '2021-12-1 20:59:00');
INSERT INTO attend_tb VALUES(101, 2, '2021-12-2 19:05:00', '2021-12-2 20:58:00');
INSERT INTO attend_tb VALUES(102, 2, '2021-12-2 18:55:00', '2021-12-2 21:00:00');
INSERT INTO attend_tb VALUES(104, 2, '2021-12-2 18:57:00', '2021-12-2 20:56:00');
INSERT INTO attend_tb VALUES(107, 2, '2021-12-2 19:10:00', '2021-12-2 19:18:00');
INSERT INTO attend_tb VALUES(100, 3, '2021-12-3 19:01:00', '2021-12-3 21:00:00');
INSERT INTO attend_tb VALUES(102, 3, '2021-12-3 18:58:00', '2021-12-3 19:05:00');
INSERT INTO attend_tb VALUES(108, 3, '2021-12-3 19:01:00', '2021-12-3 19:56:00');
*/

    • 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

    课程表：course_tb（course_id：课程编号，course_name：课程名称，course_datetime：上课时间），表中数据如下：

    mysql> select * from course_tb;
+-----------+-------------+-----------------------+
| course_id | course_name | course_datetime       |
+-----------+-------------+-----------------------+
|         1 | Python      | 2021-12-1 19:00-21:00 |
|         2 | SQL         | 2021-12-2 19:00-21:00 |
|         3 | R           | 2021-12-3 19:00-21:00 |
+-----------+-------------+-----------------------+
3 rows in set (0.00 sec)

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9

    上课情况表：attend_tb（user_id：用户编号，course_id：课程编号，in_datetime：进入直播间的时间，out_datetime：离开直播间的时间），表中数据如下：

    mysql> select * from attend_tb;
+---------+-----------+---------------------+---------------------+
| user_id | course_id | in_datetime         | out_datetime        |
+---------+-----------+---------------------+---------------------+
|     100 |         1 | 2021-12-01 19:00:00 | 2021-12-01 19:28:00 |
|     100 |         1 | 2021-12-01 19:30:00 | 2021-12-01 19:53:00 |
|     101 |         1 | 2021-12-01 19:00:00 | 2021-12-01 20:55:00 |
|     102 |         1 | 2021-12-01 19:00:00 | 2021-12-01 19:05:00 |
|     104 |         1 | 2021-12-01 19:00:00 | 2021-12-01 20:59:00 |
|     101 |         2 | 2021-12-02 19:05:00 | 2021-12-02 20:58:00 |
|     102 |         2 | 2021-12-02 18:55:00 | 2021-12-02 21:00:00 |
|     104 |         2 | 2021-12-02 18:57:00 | 2021-12-02 20:56:00 |
|     107 |         2 | 2021-12-02 19:10:00 | 2021-12-02 19:18:00 |
|     100 |         3 | 2021-12-03 19:01:00 | 2021-12-03 21:00:00 |
|     102 |         3 | 2021-12-03 18:58:00 | 2021-12-03 19:05:00 |
|     108 |         3 | 2021-12-03 19:01:00 | 2021-12-03 19:56:00 |
+---------+-----------+---------------------+---------------------+
12 rows in set (0.00 sec)

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

    【问题】请统计每个科目的平均观看时长（观看时长定义为离开直播间的时间与进入直播间的时间之差，单位是分钟），输出结果按平均观看时长降序排序，结果保留两位小数。查询结果如下：

    course_nameavg_Len
    SQL91.25
    R60.33
    Python58.00

    解答：

    /*
select c.course_name,
       round(avg(timestampdiff(second, a.in_datetime, a.out_datetime)/60), 2) avg_Len
from course_tb c join attend_tb a
on c.course_id = a.course_id
group by c.course_name
order by avg_Len desc;
*/
mysql> select c.course_name,
    ->        round(avg(timestampdiff(second, a.in_datetime, a.out_datetime)/60), 2) avg_Len
    -> from course_tb c join attend_tb a
    -> on c.course_id = a.course_id
    -> group by c.course_name
    -> order by avg_Len desc;
+-------------+---------+
| course_name | avg_Len |
+-------------+---------+
| SQL         |   91.25 |
| R           |   60.33 |
| Python      |   58.00 |
+-------------+---------+
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

    189、连接查询与分组统计（4）

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

    /*
drop table if exists course_tb;
CREATE TABLE course_tb(
course_id int(10) NOT NULL, 
course_name char(10) NOT NULL,
course_datetime char(30) NOT NULL);

INSERT INTO course_tb VALUES(1, 'Python', '2021-12-1 19:00-21:00');
INSERT INTO course_tb VALUES(2, 'SQL', '2021-12-2 19:00-21:00');
INSERT INTO course_tb VALUES(3, 'R', '2021-12-3 19:00-21:00');

drop table if exists behavior_tb;
CREATE TABLE behavior_tb(
user_id int(10) NOT NULL, 
if_vw int(10) NOT NULL,
if_fav int(10) NOT NULL,
if_sign int(10) NOT NULL,
course_id int(10) NOT NULL);

INSERT INTO behavior_tb VALUES(100, 1, 1, 1, 1);
INSERT INTO behavior_tb VALUES(100, 1, 1, 1, 2);
INSERT INTO behavior_tb VALUES(100, 1, 1, 1, 3);
INSERT INTO behavior_tb VALUES(101, 1, 1, 1, 1);
INSERT INTO behavior_tb VALUES(101, 1, 1, 1, 2);
INSERT INTO behavior_tb VALUES(101, 1, 0, 0, 3);
INSERT INTO behavior_tb VALUES(102, 1, 1, 1, 1);
INSERT INTO behavior_tb VALUES(102, 1, 1, 1, 2);
INSERT INTO behavior_tb VALUES(102, 1, 1, 1, 3);
INSERT INTO behavior_tb VALUES(103, 1, 1, 0, 1);
INSERT INTO behavior_tb VALUES(103, 1, 0, 0, 2);
INSERT INTO behavior_tb VALUES(103, 1, 0, 0, 3);
INSERT INTO behavior_tb VALUES(104, 1, 1, 1, 1);
INSERT INTO behavior_tb VALUES(104, 1, 1, 1, 2);
INSERT INTO behavior_tb VALUES(104, 1, 1, 0, 3);
INSERT INTO behavior_tb VALUES(105, 1, 0, 0, 1);
INSERT INTO behavior_tb VALUES(106, 1, 0, 0, 1);
INSERT INTO behavior_tb VALUES(107, 1, 0, 0, 1);
INSERT INTO behavior_tb VALUES(107, 1, 1, 1, 2);
INSERT INTO behavior_tb VALUES(108, 1, 1, 1, 3);

drop table if exists attend_tb;
CREATE TABLE attend_tb(
user_id int(10) NOT NULL, 
course_id int(10) NOT NULL,
in_datetime datetime NOT NULL,
out_datetime datetime NOT NULL
);
INSERT INTO attend_tb VALUES(100, 1, '2021-12-1 19:00:00', '2021-12-1 19:28:00');
INSERT INTO attend_tb VALUES(100, 1, '2021-12-1 19:30:00', '2021-12-1 19:53:00');
INSERT INTO attend_tb VALUES(101, 1, '2021-12-1 19:00:00', '2021-12-1 20:55:00');
INSERT INTO attend_tb VALUES(102, 1, '2021-12-1 19:00:00', '2021-12-1 19:05:00');
INSERT INTO attend_tb VALUES(104, 1, '2021-12-1 19:00:00', '2021-12-1 20:59:00');
INSERT INTO attend_tb VALUES(101, 2, '2021-12-2 19:05:00', '2021-12-2 20:58:00');
INSERT INTO attend_tb VALUES(102, 2, '2021-12-2 18:55:00', '2021-12-2 21:00:00');
INSERT INTO attend_tb VALUES(104, 2, '2021-12-2 18:57:00', '2021-12-2 20:56:00');
INSERT INTO attend_tb VALUES(107, 2, '2021-12-2 19:10:00', '2021-12-2 19:18:00');
INSERT INTO attend_tb VALUES(100, 3, '2021-12-3 19:01:00', '2021-12-3 21:00:00');
INSERT INTO attend_tb VALUES(102, 3, '2021-12-3 18:58:00', '2021-12-3 19:05:00');
INSERT INTO attend_tb VALUES(108, 3, '2021-12-3 19:01:00', '2021-12-3 19:56:00');
*/

    • 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

    课程表：course_tb（course_id：课程编号，course_name：课程名称，course_datetime：上课时间），表中数据如下：

    mysql> select * from course_tb;
+-----------+-------------+-----------------------+
| course_id | course_name | course_datetime       |
+-----------+-------------+-----------------------+
|         1 | Python      | 2021-12-1 19:00-21:00 |
|         2 | SQL         | 2021-12-2 19:00-21:00 |
|         3 | R           | 2021-12-3 19:00-21:00 |
+-----------+-------------+-----------------------+
3 rows in set (0.00 sec)

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9

    用户行为表：behavior_tb（user_id：用户编号，if_vw：是否浏览，if_fav：是否收藏，if_sign：是否报名，course_id：课程编号），表中数据如下：

    mysql> select * from behavior_tb;
+---------+-------+--------+---------+-----------+
| user_id | if_vw | if_fav | if_sign | course_id |
+---------+-------+--------+---------+-----------+
|     100 |     1 |      1 |       1 |         1 |
|     100 |     1 |      1 |       1 |         2 |
|     100 |     1 |      1 |       1 |         3 |
|     101 |     1 |      1 |       1 |         1 |
|     101 |     1 |      1 |       1 |         2 |
|     101 |     1 |      0 |       0 |         3 |
|     102 |     1 |      1 |       1 |         1 |
|     102 |     1 |      1 |       1 |         2 |
|     102 |     1 |      1 |       1 |         3 |
|     103 |     1 |      1 |       0 |         1 |
|     103 |     1 |      0 |       0 |         2 |
|     103 |     1 |      0 |       0 |         3 |
|     104 |     1 |      1 |       1 |         1 |
|     104 |     1 |      1 |       1 |         2 |
|     104 |     1 |      1 |       0 |         3 |
|     105 |     1 |      0 |       0 |         1 |
|     106 |     1 |      0 |       0 |         1 |
|     107 |     1 |      0 |       0 |         1 |
|     107 |     1 |      1 |       1 |         2 |
|     108 |     1 |      1 |       1 |         3 |
+---------+-------+--------+---------+-----------+
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

    上课情况表：attend_tb（user_id：用户编号，course_id：课程编号，in_datetime：进入直播间的时间，out_datetime：离开直播间的时间），表中数据如下：

    mysql> select * from attend_tb;
+---------+-----------+---------------------+---------------------+
| user_id | course_id | in_datetime         | out_datetime        |
+---------+-----------+---------------------+---------------------+
|     100 |         1 | 2021-12-01 19:00:00 | 2021-12-01 19:28:00 |
|     100 |         1 | 2021-12-01 19:30:00 | 2021-12-01 19:53:00 |
|     101 |         1 | 2021-12-01 19:00:00 | 2021-12-01 20:55:00 |
|     102 |         1 | 2021-12-01 19:00:00 | 2021-12-01 19:05:00 |
|     104 |         1 | 2021-12-01 19:00:00 | 2021-12-01 20:59:00 |
|     101 |         2 | 2021-12-02 19:05:00 | 2021-12-02 20:58:00 |
|     102 |         2 | 2021-12-02 18:55:00 | 2021-12-02 21:00:00 |
|     104 |         2 | 2021-12-02 18:57:00 | 2021-12-02 20:56:00 |
|     107 |         2 | 2021-12-02 19:10:00 | 2021-12-02 19:18:00 |
|     100 |         3 | 2021-12-03 19:01:00 | 2021-12-03 21:00:00 |
|     102 |         3 | 2021-12-03 18:58:00 | 2021-12-03 19:05:00 |
|     108 |         3 | 2021-12-03 19:01:00 | 2021-12-03 19:56:00 |
+---------+-----------+---------------------+---------------------+
12 rows in set (0.00 sec)

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

    【问题】请统计每个科目的出勤率（attend_rate(%)，结果保留两位小数），出勤率 = 出勤（在线时长10分钟及以上）人数 / 报名人数，输出结果按 course_id 升序排序，以上数据的输出结果如下：

    course_idcourse_nameattend_rate(%)
    1Python75.00
    2SQL60.00
    3R66.67

    解答：

    /*
select aa.course_id, aa.course_name,
       round(aa.attend_cnt / bb.cnt * 100, 2) `attend_rate(%)`
from
(select c.course_id, c.course_name,count(distinct a.user_id) attend_cnt
from course_tb c join attend_tb a
on c.course_id = a.course_id
where timestampdiff(second, a.in_datetime, a.out_datetime)/60 >= 10
group by c.course_id, c.course_name) aa
join 
(select c.course_id, c.course_name, sum(if_sign) cnt
from course_tb c join behavior_tb b
on c.course_id = b.course_id
group by c.course_id, c.course_name) bb
on aa.course_id = bb.course_id;
*/
mysql> select aa.course_id, aa.course_name,
    ->        round(aa.attend_cnt / bb.cnt * 100, 2) `attend_rate(%)`
    -> from
    -> (select c.course_id, c.course_name,count(distinct a.user_id) attend_cnt
    -> from course_tb c join attend_tb a
    -> on c.course_id = a.course_id
    -> where timestampdiff(second, a.in_datetime, a.out_datetime)/60 >= 10
    -> group by c.course_id, c.course_name) aa
    -> join 
    -> (select c.course_id, c.course_name, sum(if_sign) cnt
    -> from course_tb c join behavior_tb b
    -> on c.course_id = b.course_id
    -> group by c.course_id, c.course_name) bb
    -> on aa.course_id = bb.course_id;
+-----------+-------------+----------------+
| course_id | course_name | attend_rate(%) |
+-----------+-------------+----------------+
|         1 | Python      |          75.00 |
|         2 | SQL         |          60.00 |
|         3 | R           |          66.67 |
+-----------+-------------+----------------+
3 rows in set (0.04 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

    190、分组统计（1）

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

    /*
drop table if exists answer_tb;
CREATE TABLE answer_tb(
answer_date date NOT NULL, 
author_id int(10) NOT NULL,
issue_id char(10) NOT NULL,
char_len int(10) NOT NULL);
INSERT INTO answer_tb VALUES('2021-11-1', 101, 'E001' ,150);
INSERT INTO answer_tb VALUES('2021-11-1', 101, 'E002', 200);
INSERT INTO answer_tb VALUES('2021-11-1',102, 'C003' ,50);
INSERT INTO answer_tb VALUES('2021-11-1' ,103, 'P001', 35);
INSERT INTO answer_tb VALUES('2021-11-1', 104, 'C003', 120);
INSERT INTO answer_tb VALUES('2021-11-1' ,105, 'P001', 125);
INSERT INTO answer_tb VALUES('2021-11-1' , 102, 'P002', 105);
INSERT INTO answer_tb VALUES('2021-11-2',  101, 'P001' ,201);
INSERT INTO answer_tb VALUES('2021-11-2',  110, 'C002', 200);
INSERT INTO answer_tb VALUES('2021-11-2',  110, 'C001', 225);
INSERT INTO answer_tb VALUES('2021-11-2' , 110, 'C002', 220);
INSERT INTO answer_tb VALUES('2021-11-3', 101, 'C002', 180);
INSERT INTO answer_tb VALUES('2021-11-4' ,109, 'E003', 130);
INSERT INTO answer_tb VALUES('2021-11-4', 109, 'E001',123);
INSERT INTO answer_tb VALUES('2021-11-5', 108, 'C001',160);
INSERT INTO answer_tb VALUES('2021-11-5', 108, 'C002', 120);
INSERT INTO answer_tb VALUES('2021-11-5', 110, 'P001', 180);
INSERT INTO answer_tb VALUES('2021-11-5' , 106, 'P002' , 45);
INSERT INTO answer_tb VALUES('2021-11-5' , 107, 'E003', 56);
*/

    • 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

    回答情况表：answer_tb（answer_date：创作日期，author_id：创作者编号，issue_id：问题id，char_len：回答字数），表中数据如下：

    mysql> select * from answer_tb;
+-------------+-----------+----------+----------+
| answer_date | author_id | issue_id | char_len |
+-------------+-----------+----------+----------+
| 2021-11-01  |       101 | E001     |      150 |
| 2021-11-01  |       101 | E002     |      200 |
| 2021-11-01  |       102 | C003     |       50 |
| 2021-11-01  |       103 | P001     |       35 |
| 2021-11-01  |       104 | C003     |      120 |
| 2021-11-01  |       105 | P001     |      125 |
| 2021-11-01  |       102 | P002     |      105 |
| 2021-11-02  |       101 | P001     |      201 |
| 2021-11-02  |       110 | C002     |      200 |
| 2021-11-02  |       110 | C001     |      225 |
| 2021-11-02  |       110 | C002     |      220 |
| 2021-11-03  |       101 | C002     |      180 |
| 2021-11-04  |       109 | E003     |      130 |
| 2021-11-04  |       109 | E001     |      123 |
| 2021-11-05  |       108 | C001     |      160 |
| 2021-11-05  |       108 | C002     |      120 |
| 2021-11-05  |       110 | P001     |      180 |
| 2021-11-05  |       106 | P002     |       45 |
| 2021-11-05  |       107 | E003     |       56 |
+-------------+-----------+----------+----------+
19 rows in set (0.03 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

    【问题】请统计 11 月份日人均回答量（回答问题数量/答题人数），按回答日期排序，结果保留两位小数，查询结果如下：

    answer_dateper_num
    2021-11-011.40
    2021-11-022.00
    2021-11-031.00
    2021-11-042.00
    2021-11-051.25
    /*
select answer_date, 
       round(count(*)/count(distinct author_id), 2) per_num
from answer_tb
group by answer_date
order by answer_date;
*/
mysql> select answer_date, 
    ->        round(count(*)/count(distinct author_id), 2) per_num
    -> from answer_tb
    -> group by answer_date
    -> order by answer_date;
+-------------+---------+
| answer_date | per_num |
+-------------+---------+
| 2021-11-01  |    1.40 |
| 2021-11-02  |    2.00 |
| 2021-11-03  |    1.00 |
| 2021-11-04  |    2.00 |
| 2021-11-05  |    1.25 |
+-------------+---------+
5 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

    191、分组统计（2）

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

    /*
drop table if exists author_tb;
CREATE TABLE author_tb(
author_id int(10) NOT NULL, 
author_level int(10) NOT NULL,
sex char(10) NOT NULL);
INSERT INTO author_tb VALUES(101 , 6, 'm');
INSERT INTO author_tb VALUES(102 , 1, 'f');
INSERT INTO author_tb VALUES(103 , 1, 'm');
INSERT INTO author_tb VALUES(104 , 3, 'm');
INSERT INTO author_tb VALUES(105 , 4, 'f');
INSERT INTO author_tb VALUES(106 , 2, 'f');
INSERT INTO author_tb VALUES(107 , 2, 'm');
INSERT INTO author_tb VALUES(108 , 5, 'f');
INSERT INTO author_tb VALUES(109 , 6, 'f');
INSERT INTO author_tb VALUES(110 , 5, 'm');

drop table if exists answer_tb;
CREATE TABLE answer_tb(
answer_date date NOT NULL, 
author_id int(10) NOT NULL,
issue_id char(10) NOT NULL,
char_len int(10) NOT NULL);
INSERT INTO answer_tb VALUES('2021-11-1', 101, 'E001' ,150);
INSERT INTO answer_tb VALUES('2021-11-1', 101, 'E002', 200);
INSERT INTO answer_tb VALUES('2021-11-1',102, 'C003' ,50);
INSERT INTO answer_tb VALUES('2021-11-1' ,103, 'P001', 35);
INSERT INTO answer_tb VALUES('2021-11-1', 104, 'C003', 120);
INSERT INTO answer_tb VALUES('2021-11-1' ,105, 'P001', 125);
INSERT INTO answer_tb VALUES('2021-11-1' , 102, 'P002', 105);
INSERT INTO answer_tb VALUES('2021-11-2',  101, 'P001' ,201);
INSERT INTO answer_tb VALUES('2021-11-2',  110, 'C002', 200);
INSERT INTO answer_tb VALUES('2021-11-2',  110, 'C001', 225);
INSERT INTO answer_tb VALUES('2021-11-2' , 110, 'C002', 220);
INSERT INTO answer_tb VALUES('2021-11-3', 101, 'C002', 180);
INSERT INTO answer_tb VALUES('2021-11-4' ,109, 'E003', 130);
INSERT INTO answer_tb VALUES('2021-11-4', 109, 'E001',123);
INSERT INTO answer_tb VALUES('2021-11-5', 108, 'C001',160);
INSERT INTO answer_tb VALUES('2021-11-5', 108, 'C002', 120);
INSERT INTO answer_tb VALUES('2021-11-5', 110, 'P001', 180);
INSERT INTO answer_tb VALUES('2021-11-5' , 106, 'P002' , 45);
INSERT INTO answer_tb VALUES('2021-11-5' , 107, 'E003', 56);
*/

    • 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

    创作者信息表：author_tb（author_id：创作者编号，author_level：创作者级别，共 1-6 六个级别，sex：创作者性别），表中数据如下：

    mysql> select * from author_tb;
+-----------+--------------+-----+
| author_id | author_level | sex |
+-----------+--------------+-----+
|       101 |            6 | m   |
|       102 |            1 | f   |
|       103 |            1 | m   |
|       104 |            3 | m   |
|       105 |            4 | f   |
|       106 |            2 | f   |
|       107 |            2 | m   |
|       108 |            5 | f   |
|       109 |            6 | f   |
|       110 |            5 | m   |
+-----------+--------------+-----+
10 rows in set (0.00 sec)

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

    创作者回答情况表：answer_tb（answer_date：创作日期，author_id：创作者编号，issue_id：问题编号，char_len：回答字数），表中数据如下：

    mysql> select * from answer_tb;
+-------------+-----------+----------+----------+
| answer_date | author_id | issue_id | char_len |
+-------------+-----------+----------+----------+
| 2021-11-01  |       101 | E001     |      150 |
| 2021-11-01  |       101 | E002     |      200 |
| 2021-11-01  |       102 | C003     |       50 |
| 2021-11-01  |       103 | P001     |       35 |
| 2021-11-01  |       104 | C003     |      120 |
| 2021-11-01  |       105 | P001     |      125 |
| 2021-11-01  |       102 | P002     |      105 |
| 2021-11-02  |       101 | P001     |      201 |
| 2021-11-02  |       110 | C002     |      200 |
| 2021-11-02  |       110 | C001     |      225 |
| 2021-11-02  |       110 | C002     |      220 |
| 2021-11-03  |       101 | C002     |      180 |
| 2021-11-04  |       109 | E003     |      130 |
| 2021-11-04  |       109 | E001     |      123 |
| 2021-11-05  |       108 | C001     |      160 |
| 2021-11-05  |       108 | C002     |      120 |
| 2021-11-05  |       110 | P001     |      180 |
| 2021-11-05  |       106 | P002     |       45 |
| 2021-11-05  |       107 | E003     |       56 |
+-------------+-----------+----------+----------+
19 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

    【问题】请统计高质量回答（回答字数大于等于 100 字的认为是高质量回答）中用户属于 1-2 级、3-4 级、5-6 级的数量分别是多少，按数量降序排列，查询结果如下：

    level_cutnum
    5-6 级12
    3-4 级2
    1-2 级1

    解答：

    /*
select (case when au.author_level in (1, 2) then '1-2级'
             when au.author_level in (3, 4) then '3-4级'
             when au.author_level in (5, 6) then '5-6级'
       end) level_cut, 
       count(*) num
from author_tb au join answer_tb an
on au.author_id = an.author_id
where char_len >= 100
group by level_cut
order by num desc;
*/
mysql> select (case when au.author_level in (1, 2) then '1-2级'
    ->              when au.author_level in (3, 4) then '3-4级'
    ->              when au.author_level in (5, 6) then '5-6级'
    ->        end) level_cut, 
    ->        count(*) num
    -> from author_tb au join answer_tb an
    -> on au.author_id = an.author_id
    -> where char_len >= 100
    -> group by level_cut
    -> order by num desc;
+-----------+-----+
| level_cut | num |
+-----------+-----+
| 5-6级     |  12 |
| 3-4级     |   2 |
| 1-2级     |   1 |
+-----------+-----+
3 rows in set (0.04 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

    192、分组统计（3）

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

    /*
drop table if exists answer_tb;
CREATE TABLE answer_tb(
answer_date date NOT NULL, 
author_id int(10) NOT NULL,
issue_id char(10) NOT NULL,
char_len int(10) NOT NULL);
INSERT INTO answer_tb VALUES('2021-11-1', 101, 'E001' ,150);
INSERT INTO answer_tb VALUES('2021-11-1', 101, 'E002', 200);
INSERT INTO answer_tb VALUES('2021-11-1',102, 'C003' ,50);
INSERT INTO answer_tb VALUES('2021-11-1' ,103, 'P001', 35);
INSERT INTO answer_tb VALUES('2021-11-1', 104, 'C003', 120);
INSERT INTO answer_tb VALUES('2021-11-1' ,105, 'P001', 125);
INSERT INTO answer_tb VALUES('2021-11-1' , 102, 'P002', 105);
INSERT INTO answer_tb VALUES('2021-11-2',  101, 'P001' ,201);
INSERT INTO answer_tb VALUES('2021-11-2',  110, 'C002', 200);
INSERT INTO answer_tb VALUES('2021-11-2',  110, 'C001', 225);
INSERT INTO answer_tb VALUES('2021-11-2' , 110, 'C002', 220);
INSERT INTO answer_tb VALUES('2021-11-3', 101, 'C002', 180);
INSERT INTO answer_tb VALUES('2021-11-4' ,109, 'E003', 130);
INSERT INTO answer_tb VALUES('2021-11-4', 109, 'E001',123);
INSERT INTO answer_tb VALUES('2021-11-5', 108, 'C001',160);
INSERT INTO answer_tb VALUES('2021-11-5', 108, 'C002', 120);
INSERT INTO answer_tb VALUES('2021-11-5', 110, 'P001', 180);
INSERT INTO answer_tb VALUES('2021-11-5' , 106, 'P002' , 45);
INSERT INTO answer_tb VALUES('2021-11-5' , 107, 'E003', 56);
*/

    • 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

    创作者回答情况表：answer_tb（answer_date：创作日期，author_id：创作者编号，issue_id：问题编号，char_len：回答字数），表中数据如下：

    mysql> select * from answer_tb;
+-------------+-----------+----------+----------+
| answer_date | author_id | issue_id | char_len |
+-------------+-----------+----------+----------+
| 2021-11-01  |       101 | E001     |      150 |
| 2021-11-01  |       101 | E002     |      200 |
| 2021-11-01  |       102 | C003     |       50 |
| 2021-11-01  |       103 | P001     |       35 |
| 2021-11-01  |       104 | C003     |      120 |
| 2021-11-01  |       105 | P001     |      125 |
| 2021-11-01  |       102 | P002     |      105 |
| 2021-11-02  |       101 | P001     |      201 |
| 2021-11-02  |       110 | C002     |      200 |
| 2021-11-02  |       110 | C001     |      225 |
| 2021-11-02  |       110 | C002     |      220 |
| 2021-11-03  |       101 | C002     |      180 |
| 2021-11-04  |       109 | E003     |      130 |
| 2021-11-04  |       109 | E001     |      123 |
| 2021-11-05  |       108 | C001     |      160 |
| 2021-11-05  |       108 | C002     |      120 |
| 2021-11-05  |       110 | P001     |      180 |
| 2021-11-05  |       106 | P002     |       45 |
| 2021-11-05  |       107 | E003     |       56 |
+-------------+-----------+----------+----------+
19 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

    【问题】请统计 11 月份单日回答问题数大于等于 3 个的所有用户信息（author_date：回答日期，author_id：创作者id，answer_cnt：回答问题个数），若有多条数据符合条件，按 answer_date、author_id 升序排序。查询结果如下：

    answer_dateauthor_idanswer_cnt
    2021-11-021103

    解答：

    /*
select answer_date, author_id,  
       count(*) answer_cnt
from answer_tb
where answer_date between '2021-11-01' and '2021-11-30'
group by answer_date, author_id
having answer_cnt >= 3
order by answer_date, author_id;
*/
mysql> select answer_date, author_id,  
    ->        count(*) answer_cnt
    -> from answer_tb
    -> where answer_date between '2021-11-01' and '2021-11-30'
    -> group by answer_date, author_id
    -> having answer_cnt >= 3
    -> order by answer_date, author_id;
+-------------+-----------+------------+
| answer_date | author_id | answer_cnt |
+-------------+-----------+------------+
| 2021-11-02  |       110 |          3 |
+-------------+-----------+------------+
1 row in set (0.04 sec)

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

    193、分组统计（4）

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

    /*
drop table if exists issue_tb;
CREATE TABLE issue_tb(
issue_id char(10) NOT NULL, 
issue_type char(10) NOT NULL);
INSERT INTO issue_tb VALUES('E001' ,'Education');
INSERT INTO issue_tb VALUES('E002' ,'Education');
INSERT INTO issue_tb VALUES('E003' ,'Education');
INSERT INTO issue_tb VALUES('C001', 'Career');
INSERT INTO issue_tb VALUES('C002', 'Career');
INSERT INTO issue_tb VALUES('C003', 'Career');
INSERT INTO issue_tb VALUES('C004', 'Career');
INSERT INTO issue_tb VALUES('P001' ,'Psychology');
INSERT INTO issue_tb VALUES('P002' ,'Psychology');

drop table if exists answer_tb;
CREATE TABLE answer_tb(
answer_date date NOT NULL, 
author_id int(10) NOT NULL,
issue_id char(10) NOT NULL,
char_len int(10) NOT NULL);
INSERT INTO answer_tb VALUES('2021-11-1', 101, 'E001' ,150);
INSERT INTO answer_tb VALUES('2021-11-1', 101, 'E002', 200);
INSERT INTO answer_tb VALUES('2021-11-1',102, 'C003' ,50);
INSERT INTO answer_tb VALUES('2021-11-1' ,103, 'P001', 35);
INSERT INTO answer_tb VALUES('2021-11-1', 104, 'C003', 120);
INSERT INTO answer_tb VALUES('2021-11-1' ,105, 'P001', 125);
INSERT INTO answer_tb VALUES('2021-11-1' , 102, 'P002', 105);
INSERT INTO answer_tb VALUES('2021-11-2',  101, 'P001' ,201);
INSERT INTO answer_tb VALUES('2021-11-2',  110, 'C002', 200);
INSERT INTO answer_tb VALUES('2021-11-2',  110, 'C001', 225);
INSERT INTO answer_tb VALUES('2021-11-2' , 110, 'C002', 220);
INSERT INTO answer_tb VALUES('2021-11-3', 101, 'C002', 180);
INSERT INTO answer_tb VALUES('2021-11-4' ,109, 'E003', 130);
INSERT INTO answer_tb VALUES('2021-11-4', 109, 'E001',123);
INSERT INTO answer_tb VALUES('2021-11-5', 108, 'C001',160);
INSERT INTO answer_tb VALUES('2021-11-5', 108, 'C002', 120);
INSERT INTO answer_tb VALUES('2021-11-5', 110, 'P001', 180);
INSERT INTO answer_tb VALUES('2021-11-5' , 106, 'P002' , 45);
INSERT INTO answer_tb VALUES('2021-11-5' , 107, 'E003', 56);
*/

    • 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

    问答题目信息表：issue_tb（issue_id：问题编号，issue_type：问题类型），表中数据如下：

    mysql> select * from issue_tb;
+----------+------------+
| issue_id | issue_type |
+----------+------------+
| E001     | Education  |
| E002     | Education  |
| E003     | Education  |
| C001     | Career     |
| C002     | Career     |
| C003     | Career     |
| C004     | Career     |
| P001     | Psychology |
| P002     | Psychology |
+----------+------------+
9 rows in set (0.01 sec)

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

    创作者回答情况表：answer_tb（answer_date：创作日期，author_id：创作者编号，issue_id：回答问题编号，char_len：回答字数），表中数据如下：

    mysql> select * from answer_tb;
+-------------+-----------+----------+----------+
| answer_date | author_id | issue_id | char_len |
+-------------+-----------+----------+----------+
| 2021-11-01  |       101 | E001     |      150 |
| 2021-11-01  |       101 | E002     |      200 |
| 2021-11-01  |       102 | C003     |       50 |
| 2021-11-01  |       103 | P001     |       35 |
| 2021-11-01  |       104 | C003     |      120 |
| 2021-11-01  |       105 | P001     |      125 |
| 2021-11-01  |       102 | P002     |      105 |
| 2021-11-02  |       101 | P001     |      201 |
| 2021-11-02  |       110 | C002     |      200 |
| 2021-11-02  |       110 | C001     |      225 |
| 2021-11-02  |       110 | C002     |      220 |
| 2021-11-03  |       101 | C002     |      180 |
| 2021-11-04  |       109 | E003     |      130 |
| 2021-11-04  |       109 | E001     |      123 |
| 2021-11-05  |       108 | C001     |      160 |
| 2021-11-05  |       108 | C002     |      120 |
| 2021-11-05  |       110 | P001     |      180 |
| 2021-11-05  |       106 | P002     |       45 |
| 2021-11-05  |       107 | E003     |       56 |
+-------------+-----------+----------+----------+
19 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

    【问题】请统计回答过教育类问题的用户里有多少用户回答过职场类问题，查询结果如下：

    num
    1

    解答：

    /*
select count(*) num
from answer_tb
where author_id in
      (select author_id from answer_tb where issue_id in
       (select issue_id from issue_tb where issue_type = 'Education'))
      and issue_id in
       (select issue_id from issue_tb where issue_type = 'Career');
*/
mysql> select count(*) num
    -> from answer_tb
    -> where author_id in
    ->       (select author_id from answer_tb where issue_id in
    ->        (select issue_id from issue_tb where issue_type = 'Education'))
    ->       and issue_id in
    ->        (select issue_id from issue_tb where issue_type = 'Career');
+-----+
| num |
+-----+
|   1 |
+-----+
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
    • 21
    • 22
  • 相关阅读:
    Logstash、sharding-proxy组件高级配置
    java基于springboot校园餐厅订餐管理系统附源码
    kubekey 离线部署 kubesphere v3.3.0
    Python计时库——Time库的使用详解
    小功能⭐️Unity快捷键、路径及常用特性
    EAP-TLS实验之Ubuntu20.04环境搭建配置（FreeRADIUS3.0）(四)
    SpringBoot的error用全局异常去处理
    Mysql数据库
    【自然语言处理（NLP）】基于注意力机制的中-英机器翻译
    Docker从入门到精通｜2022版
  • 原文地址：https://blog.csdn.net/weixin_44377973/article/details/126206821