本题目要求编写SQL语句，检索出每个班级中分数最低的同学id，姓名，分数，班级名称a111
表结构:
create table tb_student (
id int not null primary key,
name varchar(32)
);
create table tb_score (
stu_id int,
score int
);
create table tb_class (
id int not null,
name varchar(32)
);
create table tb_student_class
(
id int null,
class_id int null,
stu_id int null
);
表样例
tb_student表：
| id | name |
|---|---|
| 30 | ddd |
| 49 | ccc |
| 51 | aaa |
| 52 | bbb |
tb_score表：
| stu_id | score |
|---|---|
| 30 | 99 |
| 49 | 79 |
| 51 | 80 |
| 52 | 59 |
tb_class表：
| id | name |
|---|---|
| 1 | class-1 |
| 2 | class-2 |
| 3 | class-1 |
| 4 | class-2 |
tb_student_class表
| id | stu_id | class_id |
|---|---|---|
| 1 | 30 | 1 |
| 2 | 49 | 2 |
| 3 | 51 | 1 |
| 4 | 52 | 2 |
输出样例:
| stu_id | stu_name | class_name | score |
|---|---|---|---|
| 51 | aaa | class-1 | 80 |
| 52 | bbb | class-2 | 59 |
sql版本高的可以使用ROW_NUMBER() over (PARTITION BY tb_class.name ORDER BY tb_score.score ASC)as rn,tb_score.score达到分组排序的目的，但是PTA上的sql应该是5.7，只能用GROUP BY和ORDER BY
SELECT
ANY_VALUE(stu_id) as stu_id , --any_value()抑制ONLY_FULL_GROUP_BY值被拒绝
ANY_VALUE(stu_name) as stu_name ,
ANY_VALUE(class_name) as class_name ,
ANY_VALUE(score) as score
FROM
(
SELECT
tb_score.stu_id,
tb_student.name AS stu_name,
tb_class.name AS class_name,
tb_score.score
FROM
tb_score
INNER JOIN tb_student_class ON tb_score.stu_id = tb_student_class.stu_id
INNER JOIN tb_class ON tb_student_class.class_id = tb_class.id
INNER JOIN tb_student ON tb_score.stu_id = tb_student.id
ORDER BY
tb_class.name ASC,
tb_score.score ASC
LIMIT 100 --不加limit的话order by后每列数据不对应，不知道为什么
)as a
GROUP BY
a.class_name这里有个坑就是tb_class表的id看起来是和tb_student_class表的id对应，但实际上只有和tb_student_class的class_id对应才是正确答案。
