• 数据库系统原理与应用教程（075）—— MySQL 练习题：操作题 151-159（十九）：综合练习

    数据库系统原理与应用教程（075）—— MySQL 练习题：操作题 151-159（十九）：综合练习

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

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

    /*
drop table if exists grade;
CREATE TABLE  grade(
`id` int(4) NOT NULL,
`job` varchar(32) NOT NULL,
`score` int(10) NOT NULL,
PRIMARY KEY (`id`));

INSERT INTO grade VALUES
(1,'C++',11001),
(2,'C++',10000),
(3,'C++',9000),
(4,'Java',12000),
(5,'Java',13000),
(6,'JS',12000),
(7,'JS',11000),
(8,'JS',9999);
*/

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

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

    mysql> select * from grade;
+----+------+-------+
| id | job  | score |
+----+------+-------+
|  1 | C++  | 11001 |
|  2 | C++  | 10000 |
|  3 | C++  |  9000 |
|  4 | Java | 12000 |
|  5 | Java | 13000 |
|  6 | JS   | 12000 |
|  7 | JS   | 11000 |
|  8 | JS   |  9999 |
+----+------+-------+
8 rows in set (0.02 sec)

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

    【问题】请编写一个 SQL 语句，查询各个岗位分数的平均数，按照分数降序排序，结果保留小数点后面3位（3位之后四舍五入），查询结果如下：

    jobavg
    Java12500.000
    JS10999.667
    C++10000.333

    解答：

    /*
select job, round(avg(score), 3) avg
from grade
group by job
order by avg desc;
*/
mysql> select job, round(avg(score), 3) avg
    -> from grade
    -> group by job
    -> order by avg desc;
+------+-----------+
| job  | avg       |
+------+-----------+
| Java | 12500.000 |
| JS   | 10999.667 |
| C++  | 10000.333 |
+------+-----------+
3 rows in set (0.02 sec)

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

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

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

    /*
drop table if exists grade;
CREATE TABLE  grade(
`id` int(4) NOT NULL,
`job` varchar(32) NOT NULL,
`score` int(10) NOT NULL,
PRIMARY KEY (`id`));

INSERT INTO grade VALUES
(1,'C++',11001),
(2,'C++',10000),
(3,'C++',9000),
(4,'Java',12000),
(5,'Java',13000),
(6,'JS',12000),
(7,'JS',11000),
(8,'JS',9999),
(9,'Java',12500);
*/

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

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

    mysql> select * from grade;
+----+------+-------+
| id | job  | score |
+----+------+-------+
|  1 | C++  | 11001 |
|  2 | C++  | 10000 |
|  3 | C++  |  9000 |
|  4 | Java | 12000 |
|  5 | Java | 13000 |
|  6 | JS   | 12000 |
|  7 | JS   | 11000 |
|  8 | JS   |  9999 |
|  9 | Java | 12500 |
+----+------+-------+
9 rows in set (0.00 sec)

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

    【问题】请编写一个 SQL 语句，查询用户分数大于其所在工作（job）分数的平均分的所有 grade 属性，以 id 升序排序，查询结果如下：

    idjobscore
    1C++11001
    5Java13000
    6JS12000
    7JS11000

    解答：

    /*
select g.*
from grade g join 
     (select job, avg(score) avg from grade group by job) a
     on g.job = a.job
where g.score > a.avg
order by g.id;
*/
mysql> select g.*
    -> from grade g join 
    ->      (select job, avg(score) avg from grade group by job) a
    ->      on g.job = a.job
    -> where g.score > a.avg;
+----+------+-------+
| id | job  | score |
+----+------+-------+
|  1 | C++  | 11001 |
|  5 | Java | 13000 |
|  6 | JS   | 12000 |
|  7 | JS   | 11000 |
+----+------+-------+
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

    153、分组查询与统计（3）

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

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

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

INSERT INTO grade VALUES
(1,1,12000),
(2,1,13000),
(3,2,11000),
(4,2,10000),
(5,3,11000),
(6,1,11000),
(7,2,11000);

INSERT INTO language VALUES
(1,'C++'),
(2,'JAVA'),
(3,'Python');
*/

    • 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

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

    mysql> select * from grade;
+----+-------------+-------+
| id | language_id | score |
+----+-------------+-------+
|  1 |           1 | 12000 |
|  2 |           1 | 13000 |
|  3 |           2 | 11000 |
|  4 |           2 | 10000 |
|  5 |           3 | 11000 |
|  6 |           1 | 11000 |
|  7 |           2 | 11000 |
+----+-------------+-------+
7 rows in set (0.00 sec)

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

    语言岗位表：language，表中数据如下：

    mysql> select * from language;
+----+--------+
| id | name   |
+----+--------+
|  1 | C++    |
|  2 | JAVA   |
|  3 | Python |
+----+--------+
3 rows in set (0.00 sec)

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

    【问题】查询每个岗位分数排名前 2 名的用户，查询结果先按照 language 的 name 升序排序，再按照积分降序排序，最后按照 grade 的 id 升序排序，查询结果如下：

    idnamescore
    2C++13000
    1C++12000
    3JAVA11000
    7JAVA11000
    4JAVA10000
    5Python11000

    解答：

    /*
select a.id, l.name, a.score
from language l join grade a on l.id = a.language_id
     left join grade b on a.language_id = b.language_id and a.score < b.score
group by a.id, l.name, a.score
having count(distinct b.score)  < 2
order by l.name, score desc, a.id;
*/
mysql> select a.id, l.name, a.score
    -> from language l join grade a on l.id = a.language_id
    ->      left join grade b on a.language_id = b.language_id and a.score < b.score
    -> group by a.id, l.name, a.score
    -> having count(distinct b.score)  < 2
    -> order by l.name, score desc, a.id;
+----+--------+-------+
| id | name   | score |
+----+--------+-------+
|  2 | C++    | 13000 |
|  1 | C++    | 12000 |
|  3 | JAVA   | 11000 |
|  7 | JAVA   | 11000 |
|  4 | JAVA   | 10000 |
|  5 | Python | 11000 |
+----+--------+-------+
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

    154、构造复杂查询条件

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

    /*
drop table if exists order_info;
CREATE TABLE order_info (
id int(4) NOT NULL,
user_id int(11) NOT NULL,
product_name varchar(256) NOT NULL,
status varchar(32) NOT NULL,
client_id int(4) NOT NULL,
date date NOT NULL,
PRIMARY KEY (id));

INSERT INTO order_info VALUES
(1,557336,'C++','no_completed',1,'2025-10-10'),
(2,230173543,'Python','completed',2,'2025-10-12'),
(3,57,'JS','completed',3,'2025-10-23'),
(4,57,'C++','completed',3,'2025-10-23'),
(5,557336,'Java','completed',1,'2025-10-23'),
(6,557336,'Python','no_completed',1,'2025-10-24');
*/

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

    订单信息表：order_info，表中数据如下：

    mysql> select * from order_info;
+----+-----------+--------------+--------------+-----------+------------+
| id | user_id   | product_name | status       | client_id | date       |
+----+-----------+--------------+--------------+-----------+------------+
|  1 |    557336 | C++          | no_completed |         1 | 2025-10-10 |
|  2 | 230173543 | Python       | completed    |         2 | 2025-10-12 |
|  3 |        57 | JS           | completed    |         3 | 2025-10-23 |
|  4 |        57 | C++          | completed    |         3 | 2025-10-23 |
|  5 |    557336 | Java         | completed    |         1 | 2025-10-23 |
|  6 |    557336 | Python       | no_completed |         1 | 2025-10-24 |
+----+-----------+--------------+--------------+-----------+------------+
6 rows in set (0.00 sec)

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

    【问题】请编写一个 SQL 语句，查询在 2025-10-15 以后状态为购买成功的 C++ 课程或者 Java 课程或者 Python 的订单，并且按照 id 升序排序，查询结果如下：

    iduser_idproduct_namestatusclient_iddate
    457C++completed32025-10-23
    5557336Javacompleted12025-10-23

    解答：

    /*
select * 
from order_info
where date > '2025-10-15' and status = 'completed' and 
      product_name in ('C++', 'Java', 'Python')
order by id;
*/
mysql> select * 
    -> from order_info
    -> where date > '2025-10-15' and status = 'completed' and 
    ->       product_name in ('C++', 'Java', 'Python')
    -> order by id;
+----+---------+--------------+-----------+-----------+------------+
| id | user_id | product_name | status    | client_id | date       |
+----+---------+--------------+-----------+-----------+------------+
|  4 |      57 | C++          | completed |         3 | 2025-10-23 |
|  5 |  557336 | Java         | completed |         1 | 2025-10-23 |
+----+---------+--------------+-----------+-----------+------------+
2 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

    155、分组查询

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

    /*
drop table if exists order_info;
CREATE TABLE order_info (
id int(4) NOT NULL,
user_id int(11) NOT NULL,
product_name varchar(256) NOT NULL,
status varchar(32) NOT NULL,
client_id int(4) NOT NULL,
date date NOT NULL,
PRIMARY KEY (id));

INSERT INTO order_info VALUES
(1,557336,'C++','no_completed',1,'2025-10-10'),
(2,230173543,'Python','completed',2,'2025-10-12'),
(3,57,'JS','completed',3,'2025-10-23'),
(4,57,'C++','completed',3,'2025-10-23'),
(5,557336,'Java','completed',1,'2025-10-23'),
(6,57,'Java','completed',1,'2025-10-24'),
(7,557336,'C++','completed',1,'2025-10-25');
*/

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

    订单信息表：order_info，表中数据如下：

    mysql> select * from order_info;
+----+-----------+--------------+--------------+-----------+------------+
| id | user_id   | product_name | status       | client_id | date       |
+----+-----------+--------------+--------------+-----------+------------+
|  1 |    557336 | C++          | no_completed |         1 | 2025-10-10 |
|  2 | 230173543 | Python       | completed    |         2 | 2025-10-12 |
|  3 |        57 | JS           | completed    |         3 | 2025-10-23 |
|  4 |        57 | C++          | completed    |         3 | 2025-10-23 |
|  5 |    557336 | Java         | completed    |         1 | 2025-10-23 |
|  6 |        57 | Java         | completed    |         1 | 2025-10-24 |
|  7 |    557336 | C++          | completed    |         1 | 2025-10-25 |
+----+-----------+--------------+--------------+-----------+------------+
7 rows in set (0.00 sec)

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

    【问题】请编写一个 SQL 语句，查询在 2025-10-15 以后，同一个用户下单 2 个以及 2 个以上，状态为购买成功的 C++ 课程或 Java 课程或 Python 课程的 user_id，并且按照 user_id 升序排序，查询结果如下：

    user_id
    57
    557336

    解答：

    /*
select user_id
from order_info
where date > '2025-10-15' and status = 'completed' and 
      product_name in ('C++', 'Java', 'Python')
group by user_id
having count(*) >= 2
order by user_id;
*/
mysql> select user_id
    -> from order_info
    -> where date > '2025-10-15' and status = 'completed' and 
    ->       product_name in ('C++', 'Java', 'Python')
    -> group by user_id
    -> having count(*) >= 2
    -> order by user_id;
+---------+
| user_id |
+---------+
|      57 |
|  557336 |
+---------+
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

    156、构造复杂查询条件

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

    /*
drop table if exists order_info;
CREATE TABLE order_info (
id int(4) NOT NULL,
user_id int(11) NOT NULL,
product_name varchar(256) NOT NULL,
status varchar(32) NOT NULL,
client_id int(4) NOT NULL,
date date NOT NULL,
PRIMARY KEY (id));

INSERT INTO order_info VALUES
(1,557336,'C++','no_completed',1,'2025-10-10'),
(2,230173543,'Python','completed',2,'2025-10-12'),
(3,57,'JS','completed',3,'2025-10-23'),
(4,57,'C++','completed',3,'2025-10-23'),
(5,557336,'Java','completed',1,'2025-10-23'),
(6,57,'Java','completed',1,'2025-10-24'),
(7,557336,'C++','completed',1,'2025-10-25');
*/

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

    订单信息表：order_info，表中数据如下：

    mysql> select * from order_info;
+----+-----------+--------------+--------------+-----------+------------+
| id | user_id   | product_name | status       | client_id | date       |
+----+-----------+--------------+--------------+-----------+------------+
|  1 |    557336 | C++          | no_completed |         1 | 2025-10-10 |
|  2 | 230173543 | Python       | completed    |         2 | 2025-10-12 |
|  3 |        57 | JS           | completed    |         3 | 2025-10-23 |
|  4 |        57 | C++          | completed    |         3 | 2025-10-23 |
|  5 |    557336 | Java         | completed    |         1 | 2025-10-23 |
|  6 |        57 | Java         | completed    |         1 | 2025-10-24 |
|  7 |    557336 | C++          | completed    |         1 | 2025-10-25 |
+----+-----------+--------------+--------------+-----------+------------+
7 rows in set (0.01 sec)

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

    【问题】请编写一个 SQL 语句，查询在 2025-10-15 以后，同一个用户下单 2 个以及 2 个以上，状态为购买成功的 C++ 课程或 Java 课程或 Python 课程的订单信息，按照 id 升序排序，查询结果如下：

    iduser_idproduct_namestatusclient_iddate
    457C++completed32025-10-23
    5557336Javacompleted12025-10-23
    657Javacompleted12025-10-24
    7557336C++completed12025-10-25

    解答：

    /*
select * from order_info
where user_id in
    (select user_id from order_info
     where date > '2025-10-15' and status = 'completed' and 
           product_name in ('C++', 'Java', 'Python')
     group by user_id having count(*) >= 2) and
     date > '2025-10-15' and status = 'completed' and 
     product_name in ('C++', 'Java', 'Python')
order by id;
*/
mysql> select * from order_info
    -> where user_id in
    ->     (select user_id from order_info
    ->      where date > '2025-10-15' and status = 'completed' and 
    ->            product_name in ('C++', 'Java', 'Python')
    ->      group by user_id having count(*) >= 2) and
    ->      date > '2025-10-15' and status = 'completed' and 
    ->      product_name in ('C++', 'Java', 'Python')
    -> order by id;
+----+---------+--------------+-----------+-----------+------------+
| id | user_id | product_name | status    | client_id | date       |
+----+---------+--------------+-----------+-----------+------------+
|  4 |      57 | C++          | completed |         3 | 2025-10-23 |
|  5 |  557336 | Java         | completed |         1 | 2025-10-23 |
|  6 |      57 | Java         | completed |         1 | 2025-10-24 |
|  7 |  557336 | C++          | completed |         1 | 2025-10-25 |
+----+---------+--------------+-----------+-----------+------------+
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
    • 24
    • 25
    • 26
    • 27
    • 28
    • 29

    157、构造复杂查询条件

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

    /*
drop table if exists order_info;
drop table if exists client;
CREATE TABLE order_info (
id int(4) NOT NULL,
user_id int(11) NOT NULL,
product_name varchar(256) NOT NULL,
status varchar(32) NOT NULL,
client_id int(4) NOT NULL,
date date NOT NULL,
is_group_buy 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 order_info VALUES
(1,557336,'C++','no_completed',1,'2025-10-10','No'),
(2,230173543,'Python','completed',2,'2025-10-12','No'),
(3,57,'JS','completed',0,'2025-10-23','Yes'),
(4,57,'C++','completed',3,'2025-10-23','No'),
(5,557336,'Java','completed',0,'2025-10-23','Yes'),
(6,57,'Java','completed',1,'2025-10-24','No'),
(7,557336,'C++','completed',0,'2025-10-25','Yes');

INSERT INTO client VALUES
(1,'PC'),
(2,'Android'),
(3,'IOS'),
(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

    订单信息表：order_info，表中数据如下：

    mysql> select * from order_info;
+----+-----------+--------------+--------------+-----------+------------+--------------+
| id | user_id   | product_name | status       | client_id | date       | is_group_buy |
+----+-----------+--------------+--------------+-----------+------------+--------------+
|  1 |    557336 | C++          | no_completed |         1 | 2025-10-10 | No           |
|  2 | 230173543 | Python       | completed    |         2 | 2025-10-12 | No           |
|  3 |        57 | JS           | completed    |         0 | 2025-10-23 | Yes          |
|  4 |        57 | C++          | completed    |         3 | 2025-10-23 | No           |
|  5 |    557336 | Java         | completed    |         0 | 2025-10-23 | Yes          |
|  6 |        57 | Java         | completed    |         1 | 2025-10-24 | No           |
|  7 |    557336 | C++          | completed    |         0 | 2025-10-25 | Yes          |
+----+-----------+--------------+--------------+-----------+------------+--------------+
7 rows in set (0.00 sec)

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

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

    mysql> select * from client;
+----+---------+
| id | name    |
+----+---------+
|  1 | PC      |
|  2 | Android |
|  3 | IOS     |
|  4 | H5      |
+----+---------+
4 rows in set (0.00 sec)

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

    000

    【问题】请编写一个 SQL 语句，查询在 2025-10-15 以后，同一个用户下单 2 个以及 2 个以上，状态为购买成功的 C++ 课程或 Java 课程或 Python 课程的订单 id，是否拼团以及客户端名字信息，并且按照 order_info 的 id 升序排序，查询结果如下：

    idis_group_buyclient_name
    4NoIOS
    5YesNULL
    6NoPC
    7YesNULL

    解答：

    /*
select oi.id, oi.is_group_buy, c.name client_name 
from order_info oi left join client c on oi.client_id = c.id
     join (select user_id from order_info
          where date > '2025-10-15' and status = 'completed' and 
          product_name in ('C++', 'Java', 'Python')
          group by user_id having count(*) >= 2) a
    on oi.user_id = a.user_id
where date > '2025-10-15' and status = 'completed' and 
          product_name in ('C++', 'Java', 'Python')
order by oi.id;
*/
mysql> select oi.id, oi.is_group_buy, c.name client_name 
    -> from order_info oi left join client c on oi.client_id = c.id
    ->      join (select user_id from order_info
    ->           where date > '2025-10-15' and status = 'completed' and 
    ->           product_name in ('C++', 'Java', 'Python')
    ->           group by user_id having count(*) >= 2) a
    ->     on oi.user_id = a.user_id
    -> where date > '2025-10-15' and status = 'completed' and 
    ->           product_name in ('C++', 'Java', 'Python')
    -> order by oi.id;
+----+--------------+-------------+
| id | is_group_buy | client_name |
+----+--------------+-------------+
|  4 | No           | IOS         |
|  5 | Yes          | NULL        |
|  6 | No           | PC          |
|  7 | Yes          | NULL        |
+----+--------------+-------------+
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
    • 24
    • 25
    • 26
    • 27
    • 28
    • 29
    • 30
    • 31

    158、联合查询（union）

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

    /*
drop table if exists order_info;
drop table if exists client;
CREATE TABLE order_info (
id int(4) NOT NULL,
user_id int(11) NOT NULL,
product_name varchar(256) NOT NULL,
status varchar(32) NOT NULL,
client_id int(4) NOT NULL,
date date NOT NULL,
is_group_buy 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 order_info VALUES
(1,557336,'C++','no_completed',1,'2025-10-10','No'),
(2,230173543,'Python','completed',2,'2025-10-12','No'),
(3,57,'JS','completed',0,'2025-10-23','Yes'),
(4,57,'C++','completed',3,'2025-10-23','No'),
(5,557336,'Java','completed',0,'2025-10-23','Yes'),
(6,57,'Java','completed',1,'2025-10-24','No'),
(7,557336,'C++','completed',0,'2025-10-25','Yes');

INSERT INTO client VALUES
(1,'PC'),
(2,'Android'),
(3,'IOS'),
(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

    订单信息表：order_info，表中数据如下：

    mysql> select * from order_info;
+----+-----------+--------------+--------------+-----------+------------+--------------+
| id | user_id   | product_name | status       | client_id | date       | is_group_buy |
+----+-----------+--------------+--------------+-----------+------------+--------------+
|  1 |    557336 | C++          | no_completed |         1 | 2025-10-10 | No           |
|  2 | 230173543 | Python       | completed    |         2 | 2025-10-12 | No           |
|  3 |        57 | JS           | completed    |         0 | 2025-10-23 | Yes          |
|  4 |        57 | C++          | completed    |         3 | 2025-10-23 | No           |
|  5 |    557336 | Java         | completed    |         0 | 2025-10-23 | Yes          |
|  6 |        57 | Java         | completed    |         1 | 2025-10-24 | No           |
|  7 |    557336 | C++          | completed    |         0 | 2025-10-25 | Yes          |
+----+-----------+--------------+--------------+-----------+------------+--------------+
7 rows in set (0.01 sec)

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

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

    mysql> select * from client;
+----+---------+
| id | name    |
+----+---------+
|  1 | PC      |
|  2 | Android |
|  3 | IOS     |
|  4 | H5      |
+----+---------+
4 rows in set (0.00 sec)

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

    【问题】请编写一个 SQL 语句，查询在 2025-10-15 以后，同一个用户下单 2 个以及 2 个以上、状态为购买成功的 C++ 课程或 Java 课程或 Python 课程的来源信息，第一列显示的是客户端名字，如果是拼团订单则显示 GroupBuy，第二列显示这个客户端（或者是拼团订单）有多少订单，最后结果按照第一列（source）升序排序，查询结果如下：

    sourcecnt
    GroupBuy2
    IOS1
    PC1

    解答：

    /*
select 'GroupBuy' source, count(*) cnt 
from order_info
where user_id in (select user_id from order_info
                  where date > '2025-10-15' and status = 'completed' and 
                        product_name in ('C++', 'Java', 'Python')
                  group by user_id having count(*) >= 2 ) and
                  date > '2025-10-15' and status = 'completed' and 
                        product_name in ('C++', 'Java', 'Python') and 
                  is_group_buy = 'YES'
union
select c.name source, count(*) cnt 
from order_info oi join client c on oi.client_id = c.id
where oi.user_id in (select user_id from order_info
                  where date > '2025-10-15' and status = 'completed' and 
                        product_name in ('C++', 'Java', 'Python')
                  group by user_id having count(*) >= 2 ) and
                  date > '2025-10-15' and status = 'completed' and 
                        product_name in ('C++', 'Java', 'Python') and 
                  is_group_buy = 'NO'
group by c.name
order by source;
*/
mysql> select 'GroupBuy' source, count(*) cnt 
    -> from order_info
    -> where user_id in (select user_id from order_info
    ->                   where date > '2025-10-15' and status = 'completed' and 
    ->                         product_name in ('C++', 'Java', 'Python')
    ->                   group by user_id having count(*) >= 2 ) and
    ->                   date > '2025-10-15' and status = 'completed' and 
    ->                         product_name in ('C++', 'Java', 'Python') and 
    ->                   is_group_buy = 'YES'
    -> union
    -> select c.name source, count(*) cnt 
    -> from order_info oi join client c on oi.client_id = c.id
    -> where oi.user_id in (select user_id from order_info
    ->                   where date > '2025-10-15' and status = 'completed' and 
    ->                         product_name in ('C++', 'Java', 'Python')
    ->                   group by user_id having count(*) >= 2 ) and
    ->                   date > '2025-10-15' and status = 'completed' and 
    ->                         product_name in ('C++', 'Java', 'Python') and 
    ->                   is_group_buy = 'NO'
    -> group by c.name
    -> order by source;
+----------+-----+
| source   | cnt |
+----------+-----+
| GroupBuy |   2 |
| IOS      |   1 |
| PC       |   1 |
+----------+-----+
3 rows in set (0.12 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
    • 52

    159、名次问题

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

    /*
drop table if exists class_grade;
CREATE TABLE class_grade (
grade varchar(32) NOT NULL,
number int(4) NOT NULL
);

INSERT INTO class_grade VALUES
('A',2),
('D',1),
('C',2),
('B',2);
*/

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

    班级成绩表：class_grade，表中数据如下：

    说明：每个人的综合成绩用 A、B、C、D、E表示，假设每个名次最多 1 个人，比如有 2 个A，那么必定有 1 个A是第 1 名，有 1 个A是第 2 名。

    mysql> select * from class_grade;
+-------+--------+
| grade | number |
+-------+--------+
| A     |      2 |
| D     |      1 |
| C     |      2 |
| B     |      2 |
+-------+--------+
4 rows in set (0.00 sec)

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

    【问题】请编写一个 SQL 查询，如果一个学生知道了自己综合成绩以后，最差是排第几名? 结果按照 grade 升序排序，查询结果如下：

    gradet_rank
    A2
    B4
    C6
    D7

    解答：

    /*
select grade, (select @sum := @sum + number) t_rank
from class_grade, (select @sum := 0) a
order by grade;
*/
mysql> select grade, (select @sum := @sum + number) t_rank
    -> from class_grade, (select @sum := 0) a
    -> order by grade;
+-------+--------+
| grade | t_rank |
+-------+--------+
| A     |      2 |
| B     |      4 |
| C     |      6 |
| D     |      7 |
+-------+--------+
4 rows in set (0.01 sec)

/*
select  a.grade, sum(b.number) t_rank
from class_grade a join class_grade b on a.grade >= b.grade
group by a.grade
order by a.grade;
*/
mysql> select  a.grade, sum(b.number) t_rank
    -> from class_grade a join class_grade b on a.grade >= b.grade
    -> group by a.grade
    -> order by a.grade;
+-------+--------+
| grade | t_rank |
+-------+--------+
| A     |      2 |
| B     |      4 |
| C     |      6 |
| D     |      7 |
+-------+--------+
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
    • 24
    • 25
    • 26
    • 27
    • 28
    • 29
    • 30
    • 31
    • 32
    • 33
    • 34
    • 35
    • 36
    • 37
  • 相关阅读:
    【Verilog】时序逻辑电路 -- 有限同步状态机[补充]
    STP协议
    Docker安装Redis
    TCP/IP四层模型对比OSI七层网络模型的区别是啥？数据传输过程原来是这样的
    Covert Communication 与选择波束（毫米波，大规模MIMO，可重构全息表面）
    dpdk-16.11 virtio 驱动初始化卡住问题定位
    SQL interview Questions
    【第2次JavaWeb上机练习】
    Axios、SASS学习笔记
    关于GIT的万年坑你还在踩吗？答应我别傻了
  • 原文地址：https://blog.csdn.net/weixin_44377973/article/details/126129941