关闭

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

531人阅读 评论(0) 收藏 举报
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


0
0

查看评论
* 以上用户言论只代表其个人观点,不代表CSDN网站的观点或立场
    个人资料
    • 访问:427310次
    • 积分:5558
    • 等级:
    • 排名:第4753名
    • 原创:54篇
    • 转载:549篇
    • 译文:1篇
    • 评论:23条
    最新评论