SQL 与第一名是老乡的学生的信息

select * from student where student.privince in 
(select privince from student_course sc right join student std on std.id = sc.studentid  where sc.score =
(select max(score) from student_course where student_course.courseid=(select id from course where name='math'))

)

select classid , count(case when gender='m' then 1 end) as 男 ,count(case when gender = 'f' then 1 end) as 女 ,school.name from student  left join school on student.schoolid = school.id group by student.classid

select count(*) as studentcount,classroom.id as claaid from student left join classroom on classroom.classnumber = student.classid left join school on student.schoolid = school.id group by classid

select school.name, classnumber,count(*)  ,gender from student left join classroom on classroom.classnumber = student.classid left join school on student.schoolid = school.id where gender = 'm' group by classid
union
select school.name, classnumber,count(*)  ,gender from student left join classroom on classroom.classnumber = student.classid left join school on student.schoolid = school.id where gender = 'f' group by classid