• 数据库系统原理与应用教程（074）—— MySQL 练习题：操作题 141-150（十八）：综合练习

    数据库系统原理与应用教程（074）—— MySQL 练习题：操作题 141-150（十八）：综合练习

    141、求名次（1）

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

    /*
drop table if exists  `salaries` ; 
CREATE TABLE `salaries` (
`emp_no` int(11) NOT NULL,
`salary` int(11) NOT NULL,
`from_date` date NOT NULL,
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`from_date`));
INSERT INTO salaries VALUES(10001,60117,'1986-06-26','1987-06-26');
INSERT INTO salaries VALUES(10001,62102,'1987-06-26','1988-06-25');
INSERT INTO salaries VALUES(10001,66074,'1988-06-25','1989-06-25');
INSERT INTO salaries VALUES(10001,66596,'1989-06-25','1990-06-25');
INSERT INTO salaries VALUES(10001,66961,'1990-06-25','1991-06-25');
INSERT INTO salaries VALUES(10001,71046,'1991-06-25','1992-06-24');
INSERT INTO salaries VALUES(10001,74333,'1992-06-24','1993-06-24');
INSERT INTO salaries VALUES(10001,75286,'1993-06-24','1994-06-24');
INSERT INTO salaries VALUES(10001,75994,'1994-06-24','1995-06-24');
INSERT INTO salaries VALUES(10001,76884,'1995-06-24','1996-06-23');
INSERT INTO salaries VALUES(10001,80013,'1996-06-23','1997-06-23');
INSERT INTO salaries VALUES(10001,81025,'1997-06-23','1998-06-23');
INSERT INTO salaries VALUES(10001,81097,'1998-06-23','1999-06-23');
INSERT INTO salaries VALUES(10001,84917,'1999-06-23','2000-06-22');
INSERT INTO salaries VALUES(10001,85112,'2000-06-22','2001-06-22');
INSERT INTO salaries VALUES(10001,85097,'2001-06-22','2002-06-22');
INSERT INTO salaries VALUES(10001,88958,'2002-06-22','9999-01-01');
INSERT INTO salaries VALUES(10002,72527,'1996-08-03','1997-08-03');
INSERT INTO salaries VALUES(10002,72527,'1997-08-03','1998-08-03');
INSERT INTO salaries VALUES(10002,72527,'1998-08-03','1999-08-03');
INSERT INTO salaries VALUES(10002,72527,'1999-08-03','2000-08-02');
INSERT INTO salaries VALUES(10002,72527,'2000-08-02','2001-08-02');
INSERT INTO salaries VALUES(10002,72527,'2001-08-02','9999-01-01');
INSERT INTO salaries VALUES(10003,40006,'1995-12-03','1996-12-02');
INSERT INTO salaries VALUES(10003,43616,'1996-12-02','1997-12-02');
INSERT INTO salaries VALUES(10003,43466,'1997-12-02','1998-12-02');
INSERT INTO salaries VALUES(10003,43636,'1998-12-02','1999-12-02');
INSERT INTO salaries VALUES(10003,43478,'1999-12-02','2000-12-01');
INSERT INTO salaries VALUES(10003,43699,'2000-12-01','2001-12-01');
INSERT INTO salaries VALUES(10003,43311,'2001-12-01','9999-01-01');
*/

    • 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

    数据表：salaries，表中数据如下：

    mysql> select * from salaries;
+--------+--------+------------+------------+
| emp_no | salary | from_date  | to_date    |
+--------+--------+------------+------------+
|  10001 |  60117 | 1986-06-26 | 1987-06-26 |
|  10001 |  62102 | 1987-06-26 | 1988-06-25 |
|  10001 |  66074 | 1988-06-25 | 1989-06-25 |
|  10001 |  66596 | 1989-06-25 | 1990-06-25 |
|  10001 |  66961 | 1990-06-25 | 1991-06-25 |
|  10001 |  71046 | 1991-06-25 | 1992-06-24 |
|  10001 |  74333 | 1992-06-24 | 1993-06-24 |
|  10001 |  75286 | 1993-06-24 | 1994-06-24 |
|  10001 |  75994 | 1994-06-24 | 1995-06-24 |
|  10001 |  76884 | 1995-06-24 | 1996-06-23 |
|  10001 |  80013 | 1996-06-23 | 1997-06-23 |
|  10001 |  81025 | 1997-06-23 | 1998-06-23 |
|  10001 |  81097 | 1998-06-23 | 1999-06-23 |
|  10001 |  84917 | 1999-06-23 | 2000-06-22 |
|  10001 |  85112 | 2000-06-22 | 2001-06-22 |
|  10001 |  85097 | 2001-06-22 | 2002-06-22 |
|  10001 |  88958 | 2002-06-22 | 9999-01-01 |
|  10002 |  72527 | 1996-08-03 | 1997-08-03 |
|  10002 |  72527 | 1997-08-03 | 1998-08-03 |
|  10002 |  72527 | 1998-08-03 | 1999-08-03 |
|  10002 |  72527 | 1999-08-03 | 2000-08-02 |
|  10002 |  72527 | 2000-08-02 | 2001-08-02 |
|  10002 |  72527 | 2001-08-02 | 9999-01-01 |
|  10003 |  40006 | 1995-12-03 | 1996-12-02 |
|  10003 |  43616 | 1996-12-02 | 1997-12-02 |
|  10003 |  43466 | 1997-12-02 | 1998-12-02 |
|  10003 |  43636 | 1998-12-02 | 1999-12-02 |
|  10003 |  43478 | 1999-12-02 | 2000-12-01 |
|  10003 |  43699 | 2000-12-01 | 2001-12-01 |
|  10003 |  43311 | 2001-12-01 | 9999-01-01 |
+--------+--------+------------+------------+
30 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
    • 32
    • 33
    • 34
    • 35
    • 36

    【问题】查询当前员工（to_date = ‘9999-01-01’）的 emp_no，salary，salary 的累计和 running_total（running_total 为前 N 个的 salary 累计和），查询结果如下：

    emp_nosalaryrunning_total
    100018895888958
    1000272527161485
    1000343311204796

    解答：

    /*
select emp_no, salary, 
       @sum_salary := @sum_salary + salary running_total
from salaries, (select @sum_salary := 0) a
where to_date = '9999-01-01';
*/
mysql> select emp_no, salary, 
    ->        @sum_salary := @sum_salary + salary running_total
    -> from salaries, (select @sum_salary := 0) a
    -> where to_date = '9999-01-01';
+--------+--------+---------------+
| emp_no | salary | running_total |
+--------+--------+---------------+
|  10001 |  88958 |         88958 |
|  10002 |  72527 |        161485 |
|  10003 |  43311 |        204796 |
+--------+--------+---------------+
3 rows in set (0.00 sec)

/*
select a.emp_no, a.salary, sum(b.salary) running_total
from salaries a join salaries b on a.emp_no >= b.emp_no
where a.to_date = '9999-01-01' and b.to_date = '9999-01-01'
group by a.emp_no, a.salary;
*/
mysql> select a.emp_no, a.salary, sum(b.salary) running_total
    -> from salaries a join salaries b on a.emp_no >= b.emp_no
    -> where a.to_date = '9999-01-01' and b.to_date = '9999-01-01'
    -> group by a.emp_no, a.salary;
+--------+--------+---------------+
| emp_no | salary | running_total |
+--------+--------+---------------+
|  10001 |  88958 |         88958 |
|  10002 |  72527 |        161485 |
|  10003 |  43311 |        204796 |
+--------+--------+---------------+
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
    • 27
    • 28
    • 29
    • 30
    • 31
    • 32
    • 33
    • 34
    • 35
    • 36
    • 37

    142、求名次（2）

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

    /*
drop table if exists  `employees` ; 
CREATE TABLE `employees` (
  `emp_no` int(11) NOT NULL,
  `birth_date` date NOT NULL,
  `first_name` varchar(14) NOT NULL,
  `last_name` varchar(16) NOT NULL,
  `gender` char(1) NOT NULL,
  `hire_date` date NOT NULL,
  PRIMARY KEY (`emp_no`));
INSERT INTO employees VALUES(10001,'1953-09-02','Georgi','Facello','M','1986-06-26');
INSERT INTO employees VALUES(10002,'1964-06-02','Bezalel','Simmel','F','1985-11-21');
INSERT INTO employees VALUES(10005,'1955-01-21','Kyoichi','Maliniak','M','1989-09-12');
INSERT INTO employees VALUES(10006,'1953-04-20','Anneke','Preusig','F','1989-06-02');
*/

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

    数据表：employees，表中数据如下：

    mysql> select * from employees;
+--------+------------+------------+-----------+--------+------------+
| emp_no | birth_date | first_name | last_name | gender | hire_date  |
+--------+------------+------------+-----------+--------+------------+
|  10001 | 1953-09-02 | Georgi     | Facello   | M      | 1986-06-26 |
|  10002 | 1964-06-02 | Bezalel    | Simmel    | F      | 1985-11-21 |
|  10005 | 1955-01-21 | Kyoichi    | Maliniak  | M      | 1989-09-12 |
|  10006 | 1953-04-20 | Anneke     | Preusig   | F      | 1989-06-02 |
+--------+------------+------------+-----------+--------+------------+
4 rows in set (0.00 sec)

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

    【问题】查询 first_name 排名（按 first_name 升序排序）为奇数的 first_name，查询结果如下：

    first
    Anneke
    Georgi

    解答：

    /*
select a.first_name first 
from employees a join employees b on a.first_name >= b.first_name
group by a.first_name
having mod(count(*),2) = 1
order by a.first_name;
*/
mysql> select a.first_name first 
    -> from employees a join employees b on a.first_name >= b.first_name
    -> group by a.first_name
    -> having mod(count(*),2) = 1
    -> order by a.first_name;
+--------+
| first  |
+--------+
| Anneke |
| Georgi |
+--------+
2 rows in set (0.00 sec)

/*
select first from
    (select first_name first, (select @rank := @rank + 1) rank 
     from employees, (select @rank := 0) a
     order by first_name) b
where mod(rank, 2) = 1
order by first;
*/
mysql> select first from
    ->     (select first_name first, (select @rank := @rank + 1) rank 
    ->      from employees, (select @rank := 0) a
    ->      order by first_name) b
    -> where mod(rank, 2) = 1
    -> order by first;
+--------+
| first  |
+--------+
| Anneke |
| Georgi |
+--------+
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
    • 25
    • 26
    • 27
    • 28
    • 29
    • 30
    • 31
    • 32
    • 33
    • 34
    • 35
    • 36
    • 37
    • 38
    • 39
    • 40
    • 41

    143、分组查询

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

    /*
drop table if exists grade;
CREATE TABLE `grade` (
`id` int(4) NOT NULL,
`number` int(4) NOT NULL,
PRIMARY KEY (`id`));

INSERT INTO grade VALUES
(1,111),
(2,333),
(3,111),
(4,111),
(5,333);
*/

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

    数据表：grade，表中数据如下：

    mysql> select * from grade;
+----+--------+
| id | number |
+----+--------+
|  1 |    111 |
|  2 |    333 |
|  3 |    111 |
|  4 |    111 |
|  5 |    333 |
+----+--------+
5 rows in set (0.00 sec)

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

    【问题】查询积分表中出现三次以及三次以上的积分，查询结果如下：

    number_cnt
    111

    解答：

    mysql> select number from grade group by number having count(*) >= 3;
+--------+
| number |
+--------+
|    111 |
+--------+
1 row in set (0.00 sec)

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

    144、名次问题

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

    /*
drop table if exists passing_number;
CREATE TABLE `passing_number` (
`id` int(4) NOT NULL,
`number` int(4) NOT NULL,
PRIMARY KEY (`id`));

INSERT INTO passing_number VALUES
(1,4),
(2,3),
(3,3),
(4,2),
(6,4),
(5,5);
*/

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

    数据表：passing_number，表中数据如下：

    mysql> select * from passing_number;
+----+--------+
| id | number |
+----+--------+
|  1 |      4 |
|  2 |      3 |
|  3 |      3 |
|  4 |      2 |
|  5 |      5 |
|  6 |      4 |
+----+--------+
6 rows in set (0.02 sec)

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

    【问题】请根据上表，输出通过的题目的排名，通过题目个数相同的，排名相同，此时按照 id 升序排列，查询结果如下：

    idnumbert_rank
    551
    142
    642
    233
    333
    424

    解答：

    /*
select p.id, p.number, 
       (select count(distinct number)+1 from passing_number where number > p.number) t_rank
from passing_number p
order by t_rank, p.id;
*/
mysql> select p.id, p.number, 
    ->        (select count(distinct number)+1 from passing_number where number > p.number) t_rank
    -> from passing_number p
    -> order by t_rank, p.id;
+----+--------+--------+
| id | number | t_rank |
+----+--------+--------+
|  5 |      5 |      1 |
|  1 |      4 |      2 |
|  6 |      4 |      2 |
|  2 |      3 |      3 |
|  3 |      3 |      3 |
|  4 |      2 |      4 |
+----+--------+--------+
6 rows in set (0.00 sec)

/*
select p.id, p.number, 
       (select count(number)+1 from passing_number where number > p.number) t_rank
from passing_number p
order by t_rank, p.id;
*/
mysql> select p.id, p.number, 
    ->        (select count(number)+1 from passing_number where number > p.number) t_rank
    -> from passing_number p
    -> order by t_rank, p.id;
+----+--------+--------+
| id | number | t_rank |
+----+--------+--------+
|  5 |      5 |      1 |
|  1 |      4 |      2 |
|  6 |      4 |      2 |
|  2 |      3 |      4 |
|  3 |      3 |      4 |
|  4 |      2 |      6 |
+----+--------+--------+
6 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
    • 32
    • 33
    • 34
    • 35
    • 36
    • 37
    • 38
    • 39
    • 40
    • 41
    • 42
    • 43

    145、外连接

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

    /*
drop table if exists person;
drop table if exists task;
CREATE TABLE `person` (
`id` int(4) NOT NULL,
`name` varchar(32) NOT NULL,
PRIMARY KEY (`id`));

CREATE TABLE `task` (
`id` int(4) NOT NULL,
`person_id` int(4) NOT NULL,
`content` varchar(32) NOT NULL,
PRIMARY KEY (`id`));

INSERT INTO person VALUES
(1,'fh'),
(2,'tm');

INSERT INTO task VALUES
(1,2,'tm works well'),
(2,2,'tm works well');
*/

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

    person 表，主键为 id，表中数据如下：

    mysql> select * from person;
+----+------+
| id | name |
+----+------+
|  1 | fh   |
|  2 | tm   |
+----+------+
2 rows in set (0.00 sec)

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

    任务表：task，主键为id，表中数据如下：

    mysql> select * from task;
+----+-----------+---------------+
| id | person_id | content       |
+----+-----------+---------------+
|  1 |         2 | tm works well |
|  2 |         2 | tm works well |
+----+-----------+---------------+
2 rows in set (0.00 sec)

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

    【问题】请查找每个人的任务情况并输出，没有任务的也要输出，输出结果按照 person 的 id 升序排序，查询结果如下：

    idnamecontent
    1fhNULL
    2tmtm1 works well
    2tmtm2 works well

    解答：

    /*
select p.id, p.name, t.content
from person p left join task t on p.id = t.person_id
order by p.id;
*/
mysql> select p.id, p.name, t.content
    -> from person p left join task t on p.id = t.person_id
    -> order by p.id;
+----+------+---------------+
| id | name | content       |
+----+------+---------------+
|  1 | fh   | NULL          |
|  2 | tm   | tm works well |
|  2 | tm   | tm works well |
+----+------+---------------+
3 rows in set (0.03 sec)

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

    146、子连接

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

    /*
drop table if exists email;
drop table if exists user;
CREATE TABLE `email` (
`id` int(4) NOT NULL,
`send_id` int(4) NOT NULL,
`receive_id` int(4) NOT NULL,
`type` varchar(32) NOT NULL,
`date` date NOT NULL,
PRIMARY KEY (`id`));

CREATE TABLE `user` (
`id` int(4) NOT NULL,
`is_blacklist` int(4) NOT NULL,
PRIMARY KEY (`id`));

INSERT INTO email VALUES
(1,2,3,'completed','2020-01-11'),
(2,1,3,'completed','2020-01-11'),
(3,1,4,'no_completed','2020-01-11'),
(4,3,1,'completed','2020-01-12'),
(5,3,4,'completed','2020-01-12'),
(6,4,1,'completed','2020-01-12');

INSERT INTO user VALUES
(1,0),
(2,1),
(3,0),
(4,0);
*/

    • 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

    邮件表：email，id 为主键， type 是枚举类型，枚举成员为：completed，no_completed，completed 代表邮件发送是成功的，no_completed 代表邮件是发送失败的。表中数据如下：

    mysql> select * from email;
+----+---------+------------+--------------+------------+
| id | send_id | receive_id | type         | date       |
+----+---------+------------+--------------+------------+
|  1 |       2 |          3 | completed    | 2020-01-11 |
|  2 |       1 |          3 | completed    | 2020-01-11 |
|  3 |       1 |          4 | no_completed | 2020-01-11 |
|  4 |       3 |          1 | completed    | 2020-01-12 |
|  5 |       3 |          4 | completed    | 2020-01-12 |
|  6 |       4 |          1 | completed    | 2020-01-12 |
+----+---------+------------+--------------+------------+
6 rows in set (0.00 sec)

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

    用户表：user，id 为主键（id 代表用户编号），is_blacklist 为 0 代表为正常用户，is_blacklist 为 1 代表为黑名单用户，表中数据如下:：

    ysql> select * from user;
+----+--------------+
| id | is_blacklist |
+----+--------------+
|  1 |            0 |
|  2 |            1 |
|  3 |            0 |
|  4 |            0 |
+----+--------------+
4 rows in set (0.00 sec)

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

    【问题】请编写一个 SQL 查询，每一个日期里面，正常用户发送给正常用户邮件失败的概率是多少，结果保留到小数点后面 3 位（3 位之后的四舍五入），按照日期升序排序，查询结果如下：

    datep
    2020-01-110.333
    2020-01-120.000

    解答：

    /*
select e.date, 
       round(sum(if(send_id in (select id from user where is_blacklist = 0) 
           and receive_id in (select id from user where is_blacklist = 0)
           and type = 'no_completed', 1, 0)) / count(*),3) p
from email e
group by e.date
order by e.date;
*/
mysql> select e.date, 
    ->        round(sum(if(send_id in (select id from user where is_blacklist = 0) 
    ->            and receive_id in (select id from user where is_blacklist = 0)
    ->            and type = 'no_completed', 1, 0)) / count(*),3) p
    -> from email e
    -> group by e.date
    -> order by e.date;
+------------+-------+
| date       | p     |
+------------+-------+
| 2020-01-11 | 0.333 |
| 2020-01-12 | 0.000 |
+------------+-------+
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

    147、分组查询（1）

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

    /*
drop table if exists login;

CREATE TABLE login (
id int(4) NOT NULL,
user_id int(4) NOT NULL,
client_id int(4) NOT NULL,
date date NOT NULL,
PRIMARY KEY (id));

INSERT INTO login VALUES
(1,2,1,'2020-10-12'),
(2,3,2,'2020-10-12'),
(3,2,2,'2020-10-13'),
(4,3,2,'2020-10-13');
*/

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

    数据表：login，表中数据如下：

    mysql> select * from login;
+----+---------+-----------+------------+
| id | user_id | client_id | date       |
+----+---------+-----------+------------+
|  1 |       2 |         1 | 2020-10-12 |
|  2 |       3 |         2 | 2020-10-12 |
|  3 |       2 |         2 | 2020-10-13 |
|  4 |       3 |         2 | 2020-10-13 |
+----+---------+-----------+------------+
4 rows in set (0.00 sec)

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

    【问题】请编写 SQL 语句查询每个用户最近一天登录的日子，按照 user_id 升序排序，查询结果如下：

    user_idid
    22020-10-13
    32020-10-13

    解答：

    /*
select user_id,max(date) id
from login
group by user_id
order by user_id;
*/
mysql> select user_id,min(date) id
    -> from login
    -> group by user_id
    -> order by user_id;
+---------+------------+
| user_id | id         |
+---------+------------+
|       2 | 2020-10-12 |
|       3 | 2020-10-12 |
+---------+------------+
2 rows in set (0.00 sec)

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

    148、分组查询（2）

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

    /*
drop table if exists login;
drop table if exists user;
drop table if exists client;
CREATE TABLE `login` (
`id` int(4) NOT NULL,
`user_id` int(4) NOT NULL,
`client_id` int(4) NOT NULL,
`date` date NOT NULL,
PRIMARY KEY (`id`));

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

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

INSERT INTO login VALUES
(1,2,1,'2020-10-12'),
(2,3,2,'2020-10-12'),
(3,2,2,'2020-10-13'),
(4,3,2,'2020-10-13');

INSERT INTO user VALUES
(1,'tm'),
(2,'fh'),
(3,'wangchao');

INSERT INTO client VALUES
(1,'pc'),
(2,'ios'),
(3,'anroid'),
(4,'h5');
*/

    • 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

    登录记录表：login，表中数据如下：

    mysql> select * from login;
+----+---------+-----------+------------+
| id | user_id | client_id | date       |
+----+---------+-----------+------------+
|  1 |       2 |         1 | 2020-10-12 |
|  2 |       3 |         2 | 2020-10-12 |
|  3 |       2 |         2 | 2020-10-13 |
|  4 |       3 |         2 | 2020-10-13 |
+----+---------+-----------+------------+
4 rows in set (0.00 sec)

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

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

    mysql> select * from user;
+----+----------+
| id | name     |
+----+----------+
|  1 | tm       |
|  2 | fh       |
|  3 | wangchao |
+----+----------+
3 rows in set (0.00 sec)

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

    客户端表：client，表中数据如下：

    mysql> select * from client;
+----+--------+
| id | name   |
+----+--------+
|  1 | pc     |
|  2 | ios    |
|  3 | anroid |
|  4 | h5     |
+----+--------+
4 rows in set (0.00 sec)

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

    【问题】请编写一个 SQL 语句查询每个用户最近一天登录的日子，用户的名字，以及用户使用的设备的名字，查询结果按照 user 的 name 升序排序，查询结果如下：

    u_nc_ndate
    fhios2020-10-13
    wangchaoios2020-10-13

    解答：

    /*
select u_n, c.name c_n, a.date
from (select u.id, u.name u_n, max(date) date
      from login l join user u on l.user_id = u.id
      group by u.id, u_n) a join login l on a.id = l.user_id and a.date = l.date
      join client c on l.client_id = c.id
order by u_n;
*/
mysql> select u_n, c.name c_n, a.date
    -> from (select u.id, u.name u_n, max(date) date
    ->       from login l join user u on l.user_id = u.id
    ->       group by u.id, u_n) a join login l on a.id = l.user_id and a.date = l.date
    ->       join client c on l.client_id = c.id
    -> order by c_n;
+----------+-----+------------+
| u_n      | c_n | date       |
+----------+-----+------------+
| wangchao | ios | 2020-10-13 |
| fh       | ios | 2020-10-13 |
+----------+-----+------------+
2 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

    149、自连接查询（1）

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

    /*
drop table if exists login;
CREATE TABLE `login` (
`id` int(4) NOT NULL,
`user_id` int(4) NOT NULL,
`client_id` int(4) NOT NULL,
`date` date NOT NULL,
PRIMARY KEY (`id`));

INSERT INTO login VALUES
(1,2,1,'2020-10-12'),
(2,3,2,'2020-10-12'),
(3,1,2,'2020-10-12'),
(4,2,2,'2020-10-13'),
(5,4,1,'2020-10-13'),
(6,1,2,'2020-10-13'),
(7,1,2,'2020-10-14');
*/

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

    登录记录表：login，表中数据如下：

    mysql> select * from login;
+----+---------+-----------+------------+
| id | user_id | client_id | date       |
+----+---------+-----------+------------+
|  1 |       2 |         1 | 2020-10-12 |
|  2 |       3 |         2 | 2020-10-12 |
|  3 |       1 |         2 | 2020-10-12 |
|  4 |       2 |         2 | 2020-10-13 |
|  5 |       4 |         1 | 2020-10-13 |
|  6 |       1 |         2 | 2020-10-13 |
|  7 |       1 |         2 | 2020-10-14 |
+----+---------+-----------+------------+
7 rows in set (0.00 sec)

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

    【问题】请统计新登录用户的次日成功的留存率，查询结果如下：

    p
    0.500

    说明：

    user_id 为 1 的用户在 2020-10-12 第一次新登录，在 2020-10-13 又登录了，为成功的留存。
    user_id 为 2 的用户在 2020-10-12 第一次新登录，在 2020-10-13 又登录了，为成功的留存。
    user_id 为 3 的用户在 2020-10-12 第一次新登录，在 2020-10-13 没登录了，为失败的留存。
    user_id 为 4 的用户在 2020-10-13 第一次新登录，在 2020-10-14 没登录了，为失败的留存。

    解答：

    /*
select round((select count(*)
        from login a join
        (select user_id, min(date) first_login_date from login group by user_id) b
        on a.user_id = b.user_id and adddate(a.date, -1) = first_login_date) / 
        (select count(distinct user_id) total_login from login),3) p;
*/
mysql> select round((select count(*)
    ->         from login a join
    ->         (select user_id, min(date) first_login_date from login group by user_id) b
    ->         on a.user_id = b.user_id and adddate(a.date, -1) = first_login_date) / 
    ->         (select count(distinct user_id) total_login from login),3) p;
+-------+
| p     |
+-------+
| 0.500 |
+-------+
1 row in set (0.03 sec)

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

    150、自连接查询（2）

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

    /*
drop table if exists login;
CREATE TABLE `login` (
`id` int(4) NOT NULL,
`user_id` int(4) NOT NULL,
`client_id` int(4) NOT NULL,
`date` date NOT NULL,
PRIMARY KEY (`id`));

INSERT INTO login VALUES
(1,2,1,'2020-10-12'),
(2,3,2,'2020-10-12'),
(3,1,2,'2020-10-12'),
(4,2,2,'2020-10-13'),
(5,1,2,'2020-10-13'),
(6,3,1,'2020-10-14'),
(7,4,1,'2020-10-14'),
(8,4,1,'2020-10-15');
*/

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

    登录记录表：login，表中数据如下：

    mysql> select * from login;
+----+---------+-----------+------------+
| id | user_id | client_id | date       |
+----+---------+-----------+------------+
|  1 |       2 |         1 | 2020-10-12 |
|  2 |       3 |         2 | 2020-10-12 |
|  3 |       1 |         2 | 2020-10-12 |
|  4 |       2 |         2 | 2020-10-13 |
|  5 |       1 |         2 | 2020-10-13 |
|  6 |       3 |         1 | 2020-10-14 |
|  7 |       4 |         1 | 2020-10-14 |
|  8 |       4 |         1 | 2020-10-15 |
+----+---------+-----------+------------+
8 rows in set (0.00 sec)

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

    【问题】请编写一个 SQL 语句，查询每个日期登录新用户个数，查询结果按照日期升序排序，查询结果如下：

    datenew
    2020-10-123
    2020-10-130
    2020-10-141
    2020-10-150

    解答：

    /*
select date, count(b.user_id) new
from login a left join
    (select user_id, min(date) first_login_date from login group by user_id) b
    on a.user_id = b.user_id and a.date = b.first_login_date
group by date
order by date;
*/
mysql> select date, count(b.user_id) new
    -> from login a left join
    ->     (select user_id, min(date) first_login_date from login group by user_id) b
    ->     on a.user_id = b.user_id and a.date = b.first_login_date
    -> group by date
    -> order by date;
+------------+-----+
| date       | new |
+------------+-----+
| 2020-10-12 |   3 |
| 2020-10-13 |   0 |
| 2020-10-14 |   1 |
| 2020-10-15 |   0 |
+------------+-----+
4 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
  • 相关阅读:
    mybatis-plus多数据源配置
    vue2中的插槽
    Spring Boot入门(23)：【实战】通过AOP拦截Spring Boot日志并将其存入数据库
    Web前端开发技术课程大作业，期末考试HTML+CSS+JavaScript电竞游戏介绍网站
    OpenCore引导都支持哪些.efi文件
    合并kubeconfig配置文件
    DNAT转换与tcpdump抓包
    4.3寸串口屏在智能炒菜机上应用分享
    向毕业妥协系列之机器学习笔记:神经网络（四）Tensorflow实现（中）多分类问题
    健身房戴什么耳机比较好、最适合健身运动佩戴的耳机推荐
  • 原文地址：https://blog.csdn.net/weixin_44377973/article/details/126122479