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:
阅读(2464) | 评论(0) | 转发(0) |
