数据库应用技术 10-1 sql-sample (15 分)

本题目要求编写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对应才是正确答案。