视图中有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,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;
因为这两个项目不是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;
那么必须同时也出现在唉GROUP BY后面
--不太了解你的具体需求和表结构 你试试下面的看行不行
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,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;
试试看!