SQL Server T-SQL： CTE

概念：CTE：Common Table Expression，简称CTE，中文可以叫做，通（公）用表表达式．

用处：处理以前版本中SQL不好现实，不好理解，复杂的查询问题．比如：分页，递归查询．．．

基本语法：

 

WITH <name of your CTE>(<column names>)
AS
(
<actual query>
)

SELECT * FROM <name of your CTE>

示例一（基本用法）：

WITH MyCTE(ID, NAME)
AS
(
    SELECT EmployeeID AS ID,
           FirstName + ' ' + LastName AS NAME
    FROM   HumanResources.vEmployee
)
SELECT * FROM MyCTE

示例二（分页）：

WITH MyCTE(ID, NAME, RowID)
AS
(
    SELECT hrids AS ID,
           hr_no + ' ' + hr_name AS NAME,
           ROW_NUMBER() OVER(ORDER BY hrids) AS RowID
    FROM   hi_info_main
)
SELECT * FROM MyCTE WHERE RowID BETWEEN 11 AND 21

示例三（关联CTE）:

WITH OrderCountCTE(SalesPersonID, OrderCount)
AS
(
    SELECT 
        SalesPersonID,
        COUNT(1) AS OrderCount
    FROM   
        Sales.SalesOrderHeader
    WHERE  
        SalesPersonID IS NOT NULL
    GROUP BY
           SalesPersonID
)
SELECT sp.SalesPersonID,
       sp.SalesYTD,
       cte.OrderCount
FROM   
    OrderCountCTE cte
    INNER JOIN Sales.SalesPerson sp ON cte.SalesPersonID = sp.SalesPersonID

示例四（使用CTE的删除）：

/************************************************************
 * Code formatted by SoftTree SQL Assistant ?v4.0.34
 * Time: 2012/10/16 14:52:48
 ************************************************************/

WITH DuplicateProdCTE
AS
(
    SELECT MIN(Product_ID) AS Product_ID,
           Product_Name
    FROM   Products
    GROUP BY
           Product_Name
    HAVING COUNT(1) > 1
)
DELETE Products
FROM   Products p
       JOIN DuplicateProdCTE cte
            ON  cte.Product_Name = p.Product_Name
            AND p.Product_ID > cte.Product_ID

示例五（递归查询）：

参考文章：http://www.cnblogs.com/xfrog/archive/2010/10/10/1847462.html

 WITH lmenu(name,senior,level) as 
 (  
    SELECT NAME,SENIOR,0 level FROM MENU WHERE SENIOR IS NULL --定位成员
    UNION ALL 
    SELECT A.NAME,A.SENIOR,b.level+1 FROM MENU A,lmenu b  --递归成员
    where a.senior = b.name 
)  
 
SELECT *  from lmenu 

递归CTE具有一些限制条件(引自MSDN)：

　　至少有一个定位点成员和一个递归成员，当然，你可以定义多个定位点成员和递归成员，但所有定位点成员必须在递归成员的前面

　　定位点成员之间必须使用UNION ALL、UNION、INTERSECT、EXCEPT集合运算符，最后一个定位点成员与递归成员之间必须使用UNION ALL，递归成员之间也必须使用UNION ALL连接

　　定位点成员和递归成员中的字段数量和类型必须完全一致

　　递归成员的FROM子句只能引用一次CTE对象

　　递归成员中不允许出现下列项 

SELECT DISTINCT   
GROUP BY   
HAVING   
标量聚合   
TOP   
LEFT、RIGHT、OUTER JOIN（允许出现 INNER JOIN）   
子查询 

非递归 CTE 指南：

 　　CTE 之后必须跟随引用部分或全部 CTE 列的 SELECT、INSERT、UPDATE 或 DELETE 语句。也可以在 CREATE VIEW 语句中将 CTE 指定为视图中 SELECT 定义语句的一部分。即CTE后面必须跟随T-SQL语句，且只能在紧跟CTE后面的语句使用；

　　可以在非递归 CTE 中定义多个 CTE 查询定义。定义必须与以下集合运算符之一结合使用：UNION ALL、UNION、INTERSECT 或 EXCEPT。即如下格式：

WITH CTEA AS
(
    ....
),
CTEB AS
(
    ....
)

SELECT * FROM CTEB

　　CTE 可以引用自身，也可以引用在同一 WITH 子句中预先定义的 CTE。不允许前向引用。

　　 不允许在一个 CTE 中指定多个 WITH 子句。例如，如果 CTE_query_definition 包含一个子查询，则该子查询不能包括定义另一个 CTE 的嵌套的 WITH 子句。

　　不能在 CTE_query_definition 中使用以下子句：

    COMPUTE 或 COMPUTE BY

    ORDER BY（除非指定了 TOP 子句）

    INTO

    带有查询提示的 OPTION 子句

    FOR XML

    FOR BROWSE

　　如果将 CTE 用在属于批处理的一部分的语句中，那么在它之前的语句必须以分号结尾。即在With CTE as () 之前加分号（；）

　　可以使用引用 CTE 的查询来定义游标。

　　可以在 CTE 中引用远程服务器中的表。

递归 CTE 指南：

　　 递归 CTE 定义至少必须包含两个 CTE 查询定义，一个定位点成员和一个递归成员。可以定义多个定位点成员和递归成员；但必须将所有定位点成员查询定义置于第一个递归成员定义之前。所有 CTE 查询定义都是定位点成员，但它们引用 CTE 本身时除外。

　　定位点成员必须与以下集合运算符之一结合使用：UNION ALL、UNION、INTERSECT 或EXCEPT。在最后一个定位点成员和第一个递归成员之间，以及组合多个递归成员时，只能使用 UNION ALL 集合运算符。

　　定位点成员和递归成员中的列数必须一致。

　　递归成员中列的数据类型必须与定位点成员中相应列的数据类型一致。

　　递归成员的 FROM 子句只能引用一次 CTE expression_name。

　　在递归成员的 CTE_query_definition 中不允许出现下列项：

SELECT DISTINCT

GROUP BY

HAVING

标量聚合

TOP

LEFT、RIGHT、OUTER JOIN（允许出现 INNER JOIN）

子查询

应用于对 CTE_query_definition 中的 CTE 的递归引用的提示。

　　无论参与的 SELECT 语句返回的列的为空性如何，递归 CTE 返回的全部列都可以为空。

　　如果递归 CTE 组合不正确，可能会导致无限循环。例如，如果递归成员查询定义对父列和子列返回相同的值，则会造成无限循环。可以使用 MAXRECURSION 提示以及在INSERT、UPDATE、DELETE 或 SELECT 语句的 OPTION 子句中的一个 0 到 32,767 之间的值，来限制特定语句所允许的递归级数，以防止出现无限循环。这样就能够在解决产生循环的代码问题之前控制语句的执行。服务器范围内的默认值是 100。如果指定 0，则没有限制。每一个语句只能指定一个 MAXRECURSION 值。有关详细信息，请参阅查询提示 (Transact-SQL)。

　　不能使用包含递归公用表表达式的视图来更新数据。

　　可以使用 CTE 在查询上定义游标。递归 CTE 只允许使用快速只进游标和静态（快照）游标。如果在递归 CTE 中指定了其他游标类型，则该类型将转换为静态游标类型。

　　可以在 CTE 中引用远程服务器中的表。如果在 CTE 的递归成员中引用了远程服务器，那么将为每个远程表创建一个假脱机，这样就可以在本地反复访问这些表。

使用 MAXRECURSION 取消一条语句

　　可以使用 MAXRECURSION 来防止不合理的递归 CTE 进入无限循环。以下示例有意创建了一个无限循环，然后使用 MAXRECURSION 提示来将递归级别限制为两个。

USE AdventureWorks;
GO
--Creates an infinite loop
WITH cte (EmployeeID, ManagerID, Title) as
(
    SELECT EmployeeID, ManagerID, Title
    FROM HumanResources.Employee
    WHERE ManagerID IS NOT NULL
  UNION ALL
    SELECT cte.EmployeeID, cte.ManagerID, cte.Title
    FROM cte
    JOIN  HumanResources.Employee AS e
        ON cte.ManagerID = e.EmployeeID
)
--Uses MAXRECURSION to limit the recursive levels to 2
SELECT EmployeeID, ManagerID, Title
FROM cte
OPTION (MAXRECURSION 2);
GO

在更正代码错误之后，就不再需要 MAXRECURSION。以下示例显示了更正后的代码。

USE AdventureWorks;
GO
WITH cte (EmployeeID, ManagerID, Title)
AS
(
    SELECT EmployeeID, ManagerID, Title
    FROM HumanResources.Employee
    WHERE ManagerID IS NOT NULL
  UNION ALL
    SELECT  e.EmployeeID, e.ManagerID, e.Title
    FROM HumanResources.Employee AS e
    JOIN cte ON e.ManagerID = cte.EmployeeID
)
SELECT EmployeeID, ManagerID, Title
FROM cte;
GO

 

 

 

参考文章：

　　http://kenchell.blog.163.com/blog/static/260883092011421104513195/

　　http://database.51cto.com/art/201107/274675.htm