以例子學習with: 1.with 2.–查詢部門和部門的總薪水 3. dept_costs as ( 4. select d.department_name,sum(e.salary) dept_total 5. from departments d,employees e 6. where d.department_id=e.department_id 7. group by d.department_name 8. ), 9.–利
以例子學習with:
1.with??
2.–查詢部門和部門的總薪水???
3.? dept_costs as (??
4.???????????????? select d.department_name,sum(e.salary) dept_total??
5.?????????????????? from departments d,employees e??
6.????????????????? where d.department_id=e.department_id??
7.????????????????? group by d.department_name??
8.???????????????? ),??
9.–利用上一個with查詢的結果,計算部門的平均總薪水???
10.? avg_costs as (??
11.??????????????? select avg(dept_total) dept_avg??
12.????????????????? from dept_costs??
13.??????????????? )??
14.–從兩個with查詢中比較并且輸出查詢結果???
15.? select *??
16.??? from dept_costs??
17.?? where dept_total > (select dept_avg from avg_costs)??
18.? order by department_name?
注釋:
① 子查詢可重用相同或者前一個with查詢塊,通過select調用(with子句也只能被select調用)
② with子句的查詢輸出存儲到用戶臨時表空間,一次查詢,到處使用
③ 同級select前有多個查詢定義,第一個用with,后面的不用with,并且用逗號分割
④ 最后一個with查詢塊與下面的select調用之間不能用逗號分割,只通過右括號分離,with子句的查詢必須括號括起
⑤ 如果定義了with子句,而在查詢中不使用,則會報ora-32035錯誤,只要后面有引用的即可,不一定在select調用,在后with查詢塊引用也是可以的
⑥ 前面的with子句定義的查詢在后面的with子句中可以使用,但是一個with子句內部不能嵌套with子句
⑦ with查詢的結果列有別名,引用時候必須使用別名或者*
再來看with的語法
with子句的優點
① with子句有可能會改變執行計劃
② with子查詢只執行一次,將結果存儲在用戶的臨時表空間,可多次引用,增加性能
③ sql的可讀性較強
案例:
–
Ⅰ一般使用方式
1.with??
2.–查詢銷售部門員工的姓名???
3.? saler_name as (??
4.???????????????? select department_id from departments? where department_name=’SALES’ order by department_id??
5.???????????????? )??
6.select last_name,first_name??
7.? from employees e??
8. where department_id in (select * from saler_name)? 注釋:使用with子句,可以在復雜的查詢中預先定義好一個結果集,然后在查詢中反復使用,不使用會報錯。而且with子句獲得的是一個臨時表,必須采用select from (with查詢名)
Ⅱ 在多數子查詢中引用,同級可見
1.select last_name??
2.? from (with??
3.–查詢銷售部門員工的姓名???
4.? saler_name as (??
5.???????????????? select department_id from departments? where department_name=’SALES’ order by department_id??
6.???????????????? )??
7.select last_name,first_name??
8.? from employees e??
9. where department_id in (select * from