SQL回炉之三：子查询示范，视图

一：视图

--创建视图（要权限）CREATE VIEW v$_dept_avg_sal_info AS (SELECT avg(sal) a_s,deptno s_t FROM emp GROUP BY deptno);--查询视图SELECT * FROM v$_dept_avg_sal_info;

二：子查询示例

--部门平均薪水的等级SELECT a.a_s ,a.deptno,s.gradeFROM salgrade sJOIN (SELECT avg(sal) a_s,deptno FROM emp GROUP BY deptno)aON a.a_s BETWEEN s.losal AND s.hisal;

--薪水等级的平均值（按部门分组）,这题没什么意义……SELECT a.e_d ,avg(a.s_g)FROM (SELECT emp.deptno e_d,emp.ename , emp.sal , salgrade.grade s_gFROM empJOIN salgrade ON sal BETWEEN salgrade.losal AND salgrade.hisal)aGROUP BY a.e_d;

--选出所有经理SELECT ename FROM emp --没想到加DISTINCTWHERE empno IN (SELECT DISTINCT mgr FROM emp);

--平均薪水最高的部门名称SELECT st3.s_t,st2.m_sFROM (SELECT max(st1.a_s) m_sFROM (SELECT avg(sal) a_s,deptno s_t FROM emp GROUP BY deptno)st1)st2JOIN (SELECT avg(sal) a_s,deptno s_t FROM emp GROUP BY deptno)st3ON st2.m_s = st3.a_s;

--比普通员工最高薪水还高的经理--注意空值处理SELECT enameFROM empJOIN (SELECT max(sal) m_sFROM emp WHERE empno NOT IN (SELECT DISTINCT mgr FROM emp WHERE mgr IS NOT null))st1ON sal>st1.m_sWHERE empno IN (SELECT DISTINCT mgr FROM emp);

--不用租函数求最大值--子表中通过自连接找出e1中比e2小的值，去除重复--主表中NOT IN，不在这个范围内SELECT DISTINCT sal FROM empWHERE sal NOT IN(SELECT DISTINCT e1.sal FROM emp e1 JOIN emp e2 ON (e1.sal<e2.sal));