本题目要求编写SQL语句，检索出每个班级中分数最低的同学学号，姓名，分数，班级名称

[耶~！]

这是一道PTA平台上的题，完整题目如下

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),
    stu_id int
);

下面是我提交过的，显示“结果错误”,请问错在哪里？正确的应该怎么写呢？
我的思路是用相关子查询，传入一个“tb_class .name”里面求出此“tb_class .name”的最小成绩，然后与外层的分数比较如果相等就说明是最低成绩的学生。

select tb_student.id as stu_id,tb_student.name as stu_name,b.name as class_name ,a.score as score 
from tb_student  ,tb_score as a,tb_class as b
where tb_student.id=a.stu_id  and a.stu_id=b.stu_id  and  a.score=(
    select min(d.score)
     from tb_score as d , tb_class as c 
     where d.stu_id=c.stu_id and b.name=c.name 
     group by c.name
    );

[yes_no57111510]

使用开窗函数 Rank，ROW_NUMBER，可以去搜一下

[qq_24683975]

解决了么，今天我也做到了这个题，想看看答案

[morliz子轩]

引用 楼主 耶~！ 的回复:

这是一道PTA平台上的题，完整题目如下

Demo:

--学生表
declare @student table (
	id int identity(1,1) not null,
	name varchar(12)
)

--成绩表
declare @score table (
	stu_id int,
	score decimal(10,1)
)

--班级表
declare @class table(
	id int identity(1,1) not null,
	class_name varchar(10),
	stu_id int)

insert into @student(name)
select 'ddd' union all
select 'ccc' union all
select 'aaa' union all
select 'bbb'

insert into @score(stu_id,score)
select 1,99 union all
select 2,70 union all
select 3,80 union all
select 4,59

insert into @class(class_name,stu_id)
select 'class-1',1 union all
select 'class-2',2 union all
select 'class-3',3 union all
select 'class-4',4 

--输出结果
select a.stu_id
	,b.name as stu_name
	,c.class_name
	,min(a.score) as score

from @score a
inner join @student b on a.stu_id = b.id
inner join @class as c on a.stu_id = c.id and b.id =c.id
group by a.stu_id,b.name,c.class_name

执行结果：

[morliz子轩]

另一种写法：

--输出结果
select a.id
	,a.name
	,c.class_name
	,b.score

from @student a
inner join (select stu_id,min(score) as score from @score group by stu_id) as b on a.id = b.stu_id
inner join @class c on a.id = c.stu_id