请教下!这个视图怎么建立?
s(sno,sname)
c(cno,cname)
sc(sno,cno,grade)
创建一个视图:查询没过学生的学号、姓名、选课课程数目、平均成绩。
以下是我建立是视图:
create view se3(sno,sname,c_num,s_avg_grade)
as select sno,sname,count(cno),avg(grade)
from sc
where sno in(select sno from s where sc.sno=s.sno)
group by sno
请会的帮忙解决下!非常感谢
[解决办法]
from sc 表中没有这个字段啊。
[解决办法]
create view se3(sno,sname,c_num,s_avg_grade)
as select A.sno,B.sname,count(cno),avg(grade)
from sc A.s B
where A.sno in(select sno from s where sc.sno=s.sno) and A.sno=B.sno
group by A.sno
[解决办法]
create view Vsc
as
select s.sno,s.sname,count(c.cno),avg(isnull(sc.grade,0))
from s inner join sc on s.sno = sc.sno
inner join c on sc.cno = c.cno
inner join(select sno from sc where isnull(grade,0) < 60 group by sno) d on s.sno = d.sno and sc.sno = d.sno
group by s.sno,s.sname
这样试下