数据库系统原理与应用教程（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)