151、分组查询与统计（1）
该题目使用的表和数据如下：
成绩表：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)
【问题】请编写一个 SQL 语句，查询各个岗位分数的平均数，按照分数降序排序，结果保留小数点后面3位（3位之后四舍五入），查询结果如下：
| job | avg |
|---|---|
| Java | 12500.000 |
| JS | 10999.667 |
| C++ | 10000.333 |
解答：
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)
152、分组查询与统计（2）
该题目使用的表和数据如下：
成绩表：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)
【问题】请编写一个 SQL 语句，查询用户分数大于其所在工作（job）分数的平均分的所有 grade 属性，以 id 升序排序，查询结果如下：
| id | job | score |
|---|---|---|
| 1 | C++ | 11001 |
| 5 | Java | 13000 |
| 6 | JS | 12000 |
| 7 | JS | 11000 |
解答：
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)
153、分组查询与统计（3）
该题目使用的表和数据如下：
分数表：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)
语言岗位表：language，表中数据如下：
mysql> select * from language; +----+--------+ | id | name | +----+--------+ | 1 | C++ | | 2 | JAVA | | 3 | Python | +----+--------+ 3 rows in set (0.00 sec)
【问题】查询每个岗位分数排名前 2 名的用户，查询结果先按照 language 的 name 升序排序，再按照积分降序排序，最后按照 grade 的 id 升序排序，查询结果如下：
| id | name | score |
|---|---|---|
| 2 | C++ | 13000 |
| 1 | C++ | 12000 |
| 3 | JAVA | 11000 |
| 7 | JAVA | 11000 |
| 4 | JAVA | 10000 |
| 5 | Python | 11000 |
解答：
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)
154、构造复杂查询条件
该题目使用的表和数据如下：
订单信息表：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)
【问题】请编写一个 SQL 语句，查询在 2025-10-15 以后状态为购买成功的 C++ 课程或者 Java 课程或者 Python 的订单，并且按照 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 |
解答：
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)
155、分组查询
该题目使用的表和数据如下：
订单信息表：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)
【问题】请编写一个 SQL 语句，查询在 2025-10-15 以后，同一个用户下单 2 个以及 2 个以上，状态为购买成功的 C++ 课程或 Java 课程或 Python 课程的 user_id，并且按照 user_id 升序排序，查询结果如下：
| user_id |
|---|
| 57 |
| 557336 |
解答：
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)
156、构造复杂查询条件
该题目使用的表和数据如下：
订单信息表：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)
【问题】请编写一个 SQL 语句，查询在 2025-10-15 以后，同一个用户下单 2 个以及 2 个以上，状态为购买成功的 C++ 课程或 Java 课程或 Python 课程的订单信息，按照 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 |
解答：
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)
157、构造复杂查询条件
该题目使用的表和数据如下：
订单信息表：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)
客户端表：client，表中数据如下：
mysql> select * from client; +----+---------+ | id | name | +----+---------+ | 1 | PC | | 2 | Android | | 3 | IOS | | 4 | H5 | +----+---------+ 4 rows in set (0.00 sec)
000
【问题】请编写一个 SQL 语句，查询在 2025-10-15 以后，同一个用户下单 2 个以及 2 个以上，状态为购买成功的 C++ 课程或 Java 课程或 Python 课程的订单 id，是否拼团以及客户端名字信息，并且按照 order_info 的 id 升序排序，查询结果如下：
| id | is_group_buy | client_name |
|---|---|---|
| 4 | No | IOS |
| 5 | Yes | NULL |
| 6 | No | PC |
| 7 | Yes | NULL |
解答：
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)
158、联合查询（union）
该题目使用的表和数据如下：
订单信息表：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)
客户端表：client，表中数据如下：
mysql> select * from client; +----+---------+ | id | name | +----+---------+ | 1 | PC | | 2 | Android | | 3 | IOS | | 4 | H5 | +----+---------+ 4 rows in set (0.00 sec)
【问题】请编写一个 SQL 语句，查询在 2025-10-15 以后，同一个用户下单 2 个以及 2 个以上、状态为购买成功的 C++ 课程或 Java 课程或 Python 课程的来源信息，第一列显示的是客户端名字，如果是拼团订单则显示 GroupBuy，第二列显示这个客户端（或者是拼团订单）有多少订单，最后结果按照第一列（source）升序排序，查询结果如下：
| source | cnt |
|---|---|
| GroupBuy | 2 |
| IOS | 1 |
| PC | 1 |
解答：
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)
159、名次问题
该题目使用的表和数据如下：
班级成绩表：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)
【问题】请编写一个 SQL 查询，如果一个学生知道了自己综合成绩以后，最差是排第几名? 结果按照 grade 升序排序，查询结果如下：
| grade | t_rank |
|---|---|
| A | 2 |
| B | 4 |
| C | 6 |
| D | 7 |
解答：
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)
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)
