假设有表如下
Grade(CourseID,StudentID,Score)
Course(CourseID,CourseName)
Student(StudentID,StudentName)
现创建一个视图用于统计每个课程不及格的人数,输出位课程名称
,课程编号
,不及格人数
代码如下
create view View_fail as select A.courseid as "课程编号",
co.coursename as "课程名字",A.不及格人数
from course as co join
(select courseid,count(score) as 不及格人数 from Grade where score<60 group by courseid) as A
on A.courseid=co.courseid;
主要是构建了表A
用于存放不及格人数
注意:需要有group by才行