oracle创建视图

Question

视图中有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;

Answer 1

1段

create view V_StudInfo

as

select a.学号,a.姓名,b.课程号,b.课程名,

case when c.成绩 between 90 and 100 then '优'

when c.成绩 between 80 and 89 then '良'

when c.成绩 between 70 and 79 then '中'

when c.成绩 between 60 and 69 then '及格'

else '不及格' end 成绩等级

from 学生表 a,课程表 b, 成绩表 c where a.学号=c.学号

and b.课程号=c.课程号  

2段

create view V_Stud

as

select a.学号,a.姓名,count(*) 所修科目数,avg(成绩) 平均成绩

from 学生表 a,课程表 b, 成绩表 c where a.学号=c.学号

and b.课程号=c.课程号

and a.学号 in

(select a.学号

from 学生表 a,课程表 b, 成绩表 c where a.学号=c.学号

and b.课程号=c.课程号 and b.课程名='英语' and c.成绩>75)

扩展资料：

oracle视图总结

创建视图时在子查询中给列定义别名：在选择视图中的列时应使用别名

<span style="font-size:14px;">CREATE VIEW  salvu50

AS 

SELECT  employee_id  ID_NUMBER, 

last_name    NAME,

salary*12    ANN_SALARY

FROM    employees

WHERE   department_id = 50;

View created.</span>

查询视图：SELECT  *  FROM    salvu50;

修改视图：使用CREATE OR REPLACE VIEW 子句修改视图

<span style="font-size:14px;">CREATE OR REPLACE VIEW empvu80

(id_number, name, sal, department_id)

AS SELECT  employee_id, first_name || ' ' || last_name, 

salary, department_id

FROM    employees

WHERE   department_id = 80;

View created.</span>

CREATE VIEW 子句中各列的别名应和子查询中各列相对应

创建复杂视图举例：

<span style="font-size:14px;">CREATE VIEW dept_sum_vu

(name, minsal, maxsal, avgsal)

AS SELECT d.department_name, MIN(e.salary), 

MAX(e.salary),AVG(e.salary)

FROM      employees e, departments d

WHERE     e.department_id = d.department_id 

GROUP BY  d.department_name;

View created.</span>

删除视图：删除视图只是删除视图的定义，并不会删除基表的数据

<span style="font-size:14px;">DROP VIEW empvu80;

View dropped.</span>

Answer 2

score.chinese+score.math+score.english+score.complex
这个是number吗，最好改下
sum(to_number(score.chinese)+to_number(score.math)+to_number(score.english)+to_number(score.complex))
类型匹配

后面也是，我就不咧了

Answer 3

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,mat.stuid having sum(score.chinese+score.math+score.english+score.complex)>500;

Answer 4

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,COLLEGE.COLLEGEID,MAT.STUID having
sum(score.chinese+score.math+score.english+score.complex)>500本回答被提问者采纳