CTE

通用表表达式（CTE）是一个可以由定义语句引用的临时表命名的结果集。在他们的简单形式中，您可以将CTE视为类似于视图和派生表混合功能的改进版本。在查询的FROM子句中引用CTE的方式类似于引用派生表和视图的方式。只须定义CTE一次，即可在查询中多次引用它。在CTE的定义中，可以引用在同一批处理中定义的变量。但是CTE的真正威力在于它们的递归功能，即CTE可以包含对它们自身的引用。

USE AdventureWorks
GO
WITH SalesCTE(ProductID, SalesOrderID)
AS
(
SELECT ProductID, COUNT(SalesOrderID)
FROM Sales.SalesOrderDetail
GROUP BY ProductID
)
SELECT * FROM SalesCTE
Recursive CTEs 递归的通用表表达式

 

 

递归的CTE是根据至少两个查询（或者称为两个成员）构建的，一个是非递归查询，也成为固定成员，只能调用一次，另外一个是递归查询，也成为递归成员（RM），可以反复调用，直到查询不再返回行。查询由UNION ALL运算符连接为一个单独的CTE。

---T-SQL增强, CTE递归演示
USE demo
CREATE TABLE Employees
(
empid int NOT NULL,
mgrid int NULL,
empname varchar(25) NOT NULL,
salary money NOT NULL,
CONSTRAINT PK_Employees PRIMARY KEY(empid),
CONSTRAINT FK_Employees_mgrid_empid
FOREIGN KEY(mgrid)
REFERENCES Employees(empid)
)
CREATE INDEX idx_nci_mgrid ON Employees(mgrid)
SET NOCOUNT ON
INSERT INTO Employees VALUES(1 , NULL, 'Nancy' , $10000.00)
INSERT INTO Employees VALUES(2 , 1 , 'Andrew' , $5000.00)
INSERT INTO Employees VALUES(3 , 1 , 'Janet' , $5000.00)
INSERT INTO Employees VALUES(4 , 1 , 'Margaret', $5000.00)
INSERT INTO Employees VALUES(5 , 2 , 'Steven' , $2500.00)
INSERT INTO Employees VALUES(6 , 2 , 'Michael' , $2500.00)
INSERT INTO Employees VALUES(7 , 3 , 'Robert' , $2500.00)
INSERT INTO Employees VALUES(8 , 3 , 'Laura' , $2500.00)
INSERT INTO Employees VALUES(9 , 3 , 'Ann' , $2500.00)
INSERT INTO Employees VALUES(10, 4 , 'Ina' , $2500.00)
INSERT INTO Employees VALUES(11, 7 , 'David' , $2000.00)
INSERT INTO Employees VALUES(12, 7 , 'Ron' , $2000.00)
INSERT INTO Employees VALUES(13, 7 , 'Dan' , $2000.00)
INSERT INTO Employees VALUES(14, 11 , 'James' , $1500.00)

--请求empid=7 的 Robert及其所有级别的下属
WITH EmpCTE(empid, empname, mgrid, lvl)
AS
(

-- Anchor Member (AM)
SELECT empid, empname, mgrid, 0
FROM Employees
WHERE empid = 7
UNION ALL
-- Recursive Member (RM)
SELECT E.empid, E.empname, E.mgrid, M.lvl+1
FROM Employees AS E
JOIN EmpCTE AS M
ON E.mgrid = M.empid
)
SELECT * FROM EmpCTE

 

--使用CTE和ROW_NUMBER()进行数据库分页
--定义CTE

USE AdventureWorks;
GO
WITH c AS
(
SELECT *,ROW_NUMBER() OVER (ORDER BY CustomerID) AS RowID
FROM Sales.vIndividualCustomer
WHERE CountryRegionName='United States'
)
SELECT *
FROM c --使用CTE
WHERE RowID>50 AND RowID<=60

--使用CTE递归查询
USE AdventureWorks;
GO
WITH DirectReports (ManagerID, EmployeeID, Title, DeptID, Level) --定义CTE中的查询和输出列
AS
(
SELECT e.ManagerID, e.EmployeeID, e.Title, edh.DepartmentID, --CTE中的查询
0 AS Level
FROM HumanResources.Employee AS e
INNER JOIN HumanResources.EmployeeDepartmentHistory AS edh
ON e.EmployeeID = edh.EmployeeID AND edh.EndDate IS NULL
WHERE ManagerID IS NULL
UNION ALL --进行联合操作
SELECT e.ManagerID, e.EmployeeID, e.Title, edh.DepartmentID,
Level + 1
FROM HumanResources.Employee AS e
INNER JOIN HumanResources.EmployeeDepartmentHistory AS edh
ON e.EmployeeID = edh.EmployeeID AND edh.EndDate IS NULL
INNER JOIN DirectReports AS d --这里递归调用
ON e.ManagerID = d.EmployeeID
)
SELECT ManagerID, EmployeeID, Title, Level
FROM DirectReports --查询CTE
INNER JOIN HumanResources.Department AS dp
ON DirectReports.DeptID = dp.DepartmentID
WHERE dp.GroupName = N'Research and Development' OR Level = 0;