oracle创建视图

视图中有collegeid（学院编号）、stuid（考生编号）、考生总成绩；请问这个视图怎么写？下面是我自己写的，报 不是 group by 表达式的错，请问怎么改正？

create or replace view view_will as
  select college.collegeid,mat.stuid,sum(score.chinese+score.math+score.english+score.complex)
    from college,mat,score
  where college.collegeid=mat.first_will and mat.stuid=score.stuid
  group by score.stuid having sum(score.chinese+score.math+score.english+score.complex)>500;

---

不能在select后写college.collegeid,mat.stuid
因为这两个项目不是group by的项目

---

改为以下试试
create or replace view view_will as
  select college.collegeid,score.stuid,sum(score.chinese+score.math+score.english+score.complex)
    from college,mat,score
  where college.collegeid=mat.first_will and mat.stuid=score.stuid
  group by college.collegeid,score.stuid having sum(score.chinese+score.math+score.english+score.complex)>500;

---

select后面出现的内容如果没有加聚集函数的话
那么必须同时也出现在唉GROUP BY后面

---

SQL code:

--不太了解你的具体需求和表结构 你试试下面的看行不行
create or replace view view_will as
select min(college.collegeid),min(mat.stuid),sum(score.chinese+score.math+score.english+score.complex)
from college,mat,s