个人认为CTE最大的做点是可以处理树状存储的数据了

例如类似这样设计的数据表,ID,ParentID这样的设计使用CTE就非常方便,原因就是CTE可以自引用,达到类似递归的效果

那么问题来了,如何使用呢?

 

想深入学习CTE的看这里

 

想直接使用COPY下边的代码,F5看一眼即知,

 

#1 使用到的测试数据

DECLARE @CTEEXAMPLE TABLE

(

  EmployeeID int NOT NULL PRIMARY KEY,

  FirstName varchar(50) NOT NULL,

  LastName varchar(50) NOT NULL,

  ManagerID int NULL

)

INSERT INTO @CTEEXAMPLE VALUES (101, 'Ken', 'Sánchez', NULL)

INSERT INTO @CTEEXAMPLE VALUES (102, 'Terri', 'Duffy', 101)

INSERT INTO @CTEEXAMPLE VALUES (103, 'Roberto', 'Tamburello', 101)

INSERT INTO @CTEEXAMPLE VALUES (104, 'Rob', 'Walters', 102)

INSERT INTO @CTEEXAMPLE VALUES (105, 'Gail', 'Erickson', 102)

INSERT INTO @CTEEXAMPLE VALUES (106, 'Jossef', 'Goldberg', 103)

INSERT INTO @CTEEXAMPLE VALUES (107, 'Dylan', 'Miller', 103)

INSERT INTO @CTEEXAMPLE VALUES (108, 'Diane', 'Margheim', 105)

INSERT INTO @CTEEXAMPLE VALUES (109, 'Gigi', 'Matthew', 105)

INSERT INTO @CTEEXAMPLE VALUES (110, 'Michael', 'Raheem', 106);

 

 

 

#2 CTE递归查询

DECLARE @CTEEXAMPLE TABLE

(

  EmployeeID int NOT NULL PRIMARY KEY,

  FirstName varchar(50) NOT NULL,

  LastName varchar(50) NOT NULL,

  ManagerID int NULL

)

INSERT INTO @CTEEXAMPLE VALUES (101, 'Ken', 'Sánchez', NULL)

INSERT INTO @CTEEXAMPLE VALUES (102, 'Terri', 'Duffy', 101)

INSERT INTO @CTEEXAMPLE VALUES (103, 'Roberto', 'Tamburello', 101)

INSERT INTO @CTEEXAMPLE VALUES (104, 'Rob', 'Walters', 102)

INSERT INTO @CTEEXAMPLE VALUES (105, 'Gail', 'Erickson', 102)

INSERT INTO @CTEEXAMPLE VALUES (106, 'Jossef', 'Goldberg', 103)

INSERT INTO @CTEEXAMPLE VALUES (107, 'Dylan', 'Miller', 103)

INSERT INTO @CTEEXAMPLE VALUES (108, 'Diane', 'Margheim', 105)

INSERT INTO @CTEEXAMPLE VALUES (109, 'Gigi', 'Matthew', 105)

INSERT INTO @CTEEXAMPLE VALUES (110, 'Michael', 'Raheem', 106);

WITH cteReports (EmpID, FirstName, LastName, MgrID, EmpLevel)

AS

  (

    SELECT EmployeeID, FirstName, LastName, ManagerID, 1

    FROM @CTEEXAMPLE

    WHERE ManagerID IS NULL

    UNION ALL

    SELECT e.EmployeeID, e.FirstName, e.LastName, e.ManagerID,

      r.EmpLevel + 1

    FROM @CTEEXAMPLE e

      INNER JOIN cteReports r ON e.ManagerID = r.EmpID

  )

SELECT

    R.EmpID,R.FirstName + R.LastName 'fullName',R.MgrID,R.EmpLevel,

    'Employee<----|---->Manager',

    C.FirstName + C.LastName 'fullName'

FROM cteReports R

    LEFT JOIN @CTEEXAMPLE C ON R.MgrID = C.EmployeeID

ORDER BY EmpLevel, MgrID