Mysql中表student_table(id,name,birth,sex),score_table(stu_id,subject_name,score),查询每个学生的分数最高的学科以及对应分数、学生明细记录,如下SQL正确的是()?
select t3.*,t2.subject_name,t2.score
from (
select stu_id,max(score) as c1 from score_table group by stu_id order by c1 desc limit 1
) t1
inner join
(
select * from score_table
)t2 on t1.stu_id = t2.stu_id and t1.c1 = t2.score
inner join student_table t3
on t1.stu_id = t3.id;
select t3.*,t2.subject_name,t2.score
from (
select stu_id,max(score) as c1 from score_table group by stu_id
) t1
inner join
(
select * from score_table
)t2 on t1.stu_id = t2.stu_id
inner join student_table t3
on t1.stu_id = t3.id;
select t3.*,t2.subject_name,t2.score
from (
select stu_id,max(score) as c1 from score_table group by stu_id
) t1
inner join
(
select * from score_table
)t2 on t1.c1 = t2.score
inner join student_table t3
on t1.stu_id = t3.id;
select t3.*,t2.subject_name,t2.score
from (
select stu_id,max(score) as c1 from score_table group by stu_id
) t1
inner join
(
select * from score_table
)t2 on t1.stu_id = t2.stu_id and t1.c1 = t2.score
inner join student_table t3
on t1.stu_id = t3.id;