oracle 赋予SELECT 数据最高、最低、平均的权力 ORA-00998: 必须使用列别名命名此表达式

Question

CREATE VIEW smoney AS
SELECT dept.dname,avg(smoney),max(smoney),min(smoney)
FROM dept,staff
WHERE staff.dno=dept.dno
GROUP BY dept.dname
GRANT SELECT ON smoney
TO yanglan;
求修改

Answer 1

建视图时，计算列需要起别名，否则视图无法处理，你改为以下语法试试

CREATE VIEW smoney AS
SELECT dept.dname,avg(smoney) as a,max(smoney) as b,min(smoney) as c
FROM dept,staff
WHERE staff.dno=dept.dno
GROUP BY dept.dname
GRANT SELECT ON smoney
TO yanglan;

Answer 2

建视图
CREATE VIEW smoney AS
SELECT dept.dname,avg(smoney),max(smoney),min(smoney)
FROM dept,staff
WHERE staff.dno=dept.dno
GROUP BY dept.dname
；
将视图select 权限给用户
GRANT SELECT ON smoney TO yanglan;

Answer 3

CREATE VIEW smoney AS
SELECT dept.dname,avg(smoney) avg_smoney ,max(smoney) max_smoney,min(smoney) min_smoney
FROM dept,staff
WHERE staff.dno=dept.dno
GROUP BY dept.dname
GRANT SELECT ON smoney
TO yanglan;

Answer 4

SELECT a.dname,avg(b.smoney),max(b.smoney),min(b.smoney)
FROM dept a,staff b
WHERE b.dno=a.dno
GROUP BY a.dname追问

x显示未选定行 怎么办