[TOC] ## 常見表表達式- CTE CTE:SQL CTE是指通用表達式,它可以在同一個查詢中暫存具名的結果集,CTE可以用于遞歸查詢 ，也可以用于簡化SQL語法 ``` SELECT name, salary FROM People WHERE NAME IN ( SELECT DISTINCT NAME FROM population WHERE country = "Canada" AND city = "Toronto" ) AND salary >= ( SELECT AVG( salary ) FROM salaries WHERE gender = "Female") ``` 改成 CTEs ``` with toronto_ppl as ( SELECT DISTINCT name FROM population WHERE country = "Canada" AND city = "Toronto" ) , avg_female_salary as ( SELECT AVG(salary) as avgSalary FROM salaries WHERE gender = "Female" ) SELECT name , salary FROM People WHERE name in (SELECT DISTINCT FROM toronto_ppl) AND salary >= (SELECT avgSalary FROM avg_female_salary) ``` ### 遞歸CTEs 它涉及查詢組織結構圖，文件系統，網頁之間的鏈接圖等的分層數據，尤其有用 每個員工ID的管理器ID的遞歸CTE的示例 ``` WITH org_structure AS ( SELECT id, manager_id FROM staff_members WHERE manager_id IS NULL UNION ALL SELECT sm.id, sm.manager_id FROM staff_members sm INNER JOIN org_structure os ON os.id = sm.manager_id ) ```