[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 ) ```