As title, run results tested and output sample is consistent, but the test point has been living does not show the answer wrong, please bosses look at what point is there a problem?
title
Search out the lowest score in each class of student id, name, grade, class name
Table structure:
The create table tb_student (
Id int the not null primary key,
The name varchar (32)
);
The create table tb_score (
Stu_id int,
Score int
);
The create table tb_class (
Id int the not null,
The name varchar (32)
);
The create table tb_student_class
(
Id int null,
Class_id int null,
Stu_id int null
);
Table sample
Tb_student table:
Id name
30 DDD
49 CCC
51 aaa
52 BBB
Tb_score table:
Stu_id score
30 99
49 79
51, 80
52 59
Tb_class table:
Id name
Class 1-1
Class 2-2
3 class 1
Class 4-2
Tb_student_class table
Id stu_id class_id
30 1
49 2
3 51 1
4 52 2
The output sample:
Stu_id stu_name class_name score
51 aaa class 1 80
52 BBB class 59-2
Select
S.i d as stu_id,
S.n ame as stu_name,
C.n ame as class_name,
Sc. Score as score
The from tb_student as s
Inner join tb_student_class as tc
On s.i d=tc stu_id
Inner join tb_class as c
On c.i d=tc class_id
Inner join tb_score as sc
On sc. Stu_id=s.i d
And score=(
The select min (e.s core)
The from tb_score as e, tb_class as f, tb_student_class as g
Where c.n ame=motor ame and f.i d=g.i d and e.s tu_id in (
The select stu_id
The from tb_student_class as h
Where h.c lass_id=right lass_id
)
)