公共表表达式（CTE）回顾

SQL Server 中的分层与递归查询

Jasmin Ludolf

Content Developer

课程概览

什么是递归，如何使用？
如何用公共表表达式（CTE）进行分层和递归查询？
如何用 SQL 表示层级并进行查询？
真实案例（如公司组织、物料清单、航班计划）

俄罗斯套娃及其递归特性的图片。

什么是 CTE？

CTE 的定义

指定一个临时的命名结果集，即公共表表达式（CTE）。它源自一个简单查询，并在单个语句的执行范围内定义

CTE 的结构

WITH CTEtable as 
(
    -- a query --
)

SELECT *
    FROM CTEtable

CTE 的用途

管理复杂查询
可用于 SELECT、INSERT、UPDATE 或 DELETE 语句
可在一个 WITH 中定义多个 CTE
用 UNION 或 JOIN 组合多个 CTE
可替代视图
自引用表
支持递归查询

为 IT 组织定义一个 CTE

使用 CTE 选择经理：

WITH JOBS (id, SurName, JobTitle) AS

(
    SELECT 
          ID, 
          Name,
          Position
    FROM employee
    WHERE Position like '%Manager%'
),

用 WITH AS 定义 CTE
对 Employee 表执行 SELECT

为 IT 组织定义一个 CTE

选择薪资高于 10,000 的员工

SALARIES (ID,Salary) AS (
   SELECT
        ID,
        Salary
   FROM ITSalary
   WHERE Salary > 10000)

为 IT 组织定义一个 CTE

完整查询：

WITH JOBS (id, SurName, JobTitle) AS 
(    SELECT ID, Name, Position
        FROM employee 
        WHERE Position like '%Manager%'),

SALARIES (ID,Salary) AS
(    SELECT ID, Salary
        FROM ITSalary 
        WHERE Salary > 10000)

SELECT JOBS.Name, JOBS.Position, SALARIES.Salary
    FROM JOBS
    INNER JOIN SALARIES
    on JOBS.ID = SALARIES.ID;

SurNmames     | JobTitle          | Salary
Paul Smith    | IT Manager        | 15,000
Adam Peterson | Sourcing Manager  | 12,500
Anna Nilson   | Portfolio Manager | 10,500

¡Vamos a practicar!

SQL Server 中的分层与递归查询