数据库系统原理与应用教程（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);
*/成绩表：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 |
解答：
/*
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)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);
*/成绩表：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 |
解答：
/*
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)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');
*/分数表：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 |
解答：
/*
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)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');
*/订单信息表：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 |
解答：
/*
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)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');
*/订单信息表：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 |
解答：
/*
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)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');
*/订单信息表：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 |
解答：
/*
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)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');
*/订单信息表：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 |
解答：
/*
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)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');
*/订单信息表：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 |
解答：
/*
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)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);
*/班级成绩表：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 |
解答：
/*
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)
本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
