用思想、知识和音乐去影响和改变

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


阅读更多
版权声明:本文为博主原创文章,未经博主允许不得转载。 https://blog.csdn.net/xiewenbo/article/details/7405753
文章标签: sql join math
个人分类: 数据库
上一篇javascript的prototype继承
下一篇webservice-jws
想对作者说点什么? 我来说一句

没有更多推荐了,返回首页

关闭
关闭