Oracle 关于视图查询问题

Question

CREATE OR REPLACE VIEW V_HZY_SALARY
AS
SELECT D.DEPTNO DNO,D.DNAME DN,
MAX(E.SAL)MAX_SAL,
MIN(E.SAL)MIN_SAL,
AVG(E.SAL)AVG_SAL,
SUM(E.SAL)SUM_SAL
FROM EMP_HZY E ,DEPT D WHERE E.DEPTNO=D.DEPTNO GROUP BY D.DEPTNO,D.DNAME

SELECT E.ENAME,E.SAL,E.DEPTNO
FROM EMP_HZY E
WHERE E.SAL/2>(SELECT AVG_SAL FROM V_HZY_SALARY WHERE DEPTNO=30)
出现单行子查询返回多个行！！新手求高人指导，谢谢。

Answer 1

 WHERE E.SAL/2>(SELECT AVG_SAL FROM V_HZY_SALARY WHERE DEPTNO=30)
 下面(SELECT AVG_SAL FROM V_HZY_SALARY WHERE DEPTNO=30)这个子句可能是多条，你视图中是根据 D.DEPTNO,D.DNAME两个条件分组的，这里select只有一个deptno条件
 可以加(SELECT top 1 AVG_SAL FROM V_HZY_SALARY WHERE DEPTNO=30)