SQL> @select_with.sql
DNAME DEPT_TOTAL
-------------- ----------
RESEARCH 10875
SQL> l
1 WITH
2 dept_cost AS
3 (
4 SELECT d.dname, SUM(e.sal) dept_total
5 FROM emp e, dept d
6 WHERE e.deptno = d.deptno
7 GROUP BY d.dname
8 ),
9 avg_cost AS
10 (
11 SELECT SUM(dept_total)/COUNT(*) avg
12 FROM dept_cost
13 )
14 SELECT dname, dept_total FROM dept_cost
15 WHERE dept_total > (SELECT avg FROM avg_cost)
16* ORDER BY dname
|
Subquery Factoring: Example The following statement creates the query names dept_costs and avg_cost for the initial query block containing a join, and then uses the query names in the body of the main query.
Learn MOre:
阅读(2428) | 评论(0) | 转发(0) |
