Sql最佳化(十二)避免資料重複掃描(2) 使用with as子句提高效能

上篇介紹了幾種避免對資料重複掃描的sql寫法，本篇介紹利用複雜子查詢with as來提高sql效能。先看個例子：WITH dept_costs AS (SELECT d.department_name, SUM(e.salary) AS dept_total FROM employees e JOIN departments dON e.department_id = d.department_id GROUP BY d.department_name),avg_cost AS (SELECT SUM(dept_total)/COUNT(*) AS dept_avg FROM dept_costs)SELECT * FROM dept_costs WHERE dept_total >(SELECT dept_avg FROM avg_cost)ORDER BY department_name;在這個例子中，dept_costs相當於一個view，與view不同的是，...

Link URL: http://luw.itpub.net/post/43737/531119