统计各门课程的总人数、及格人数和不及格人数(课程编号、课程名、总人数、及格人数、

最新推荐文章于 2022-05-14 20:01:03 发布

z51909318

最新推荐文章于 2022-05-14 20:01:03 发布

阅读量6.2k

点赞数

文章标签： join

本文链接：https://blog.csdn.net/z51909318/article/details/6508424

版权

select t_course.f_id as 课程编号, t_course.f_name as 课程名称, tb1.tall,tb2.pass,tb3.npass
from t_course
full join
(select t_course.f_id ,f_name ,count(f_stuid) as tall
from t_grade right join t_course on t_grade.f_courseid=t_course.f_id
group by t_course.f_id,t_course.f_name) tb1
on (t_course.f_id=tb1.f_id)

full join
(select t_course.f_id ,f_name ,count(f_stuid) as pass
from t_grade , t_course
where t_grade.f_courseid=t_course.f_id and f_grade>=60
group by t_course.f_id,t_course.f_name) tb2
on(t_course.f_id=tb2.f_id)

full join
(select t_course.f_id ,f_name ,count(f_stuid)as npass
from t_grade , t_course
where t_grade.f_courseid=t_course.f_id and f_grade<60
group by t_course.f_id,t_course.f_name) tb3
on(tb3.f_id=t_course.f_id);