SQL Server 2005中CTE的范围是什么？

Question

WITH emp_CTE AS (
    SELECT ROW_NUMBER() OVER(ORDER BY (SELECT 1)) AS IdentityId, *
    FROM dbo.employee )
SELECT * FROM emp_CTE

这个可以正常工作

如果用相同的查询写成这样。

WITH emp_CTE AS (
    SELECT ROW_NUMBER() OVER(ORDER BY (SELECT 1)) AS IdentityId, *
    FROM dbo.employee )
SELECT * FROM EMPLOYEES    
SELECT * FROM emp_CTE

它会提示一个消息，告诉我们emp_CTE不存在。

有什么办法可以解决这个问题吗？

谢谢 Prince

- happysmile

Answer 1

CTE只是随后语句的一部分。

随后语句可以是单个SELECT / INSERT / UPDATE / DELETE，或者是复合语句（带有UNION、INTERSECT等）

例如：

;WITH cte1 AS
(
   select ...
), cte2 AS
(
    select ...
)
SELECT ...
UNION 
SELECT ...;

经验法则是变量的作用域延伸到下一个分号;的位置。分号可以结束任何语句，但不幸的是，它是可选的。

在上面的示例中，您的代码实际上是这样的：

...;
WITH emp_CTE AS (
    SELECT ROW_NUMBER() OVER(ORDER BY (SELECT 1)) AS IdentityId, *
    FROM dbo.employee )
SELECT * FROM EMPLOYEES; 
SELECT * FROM emp_CTE;

因此，公共表达式仅在...EMPLOYEES;之前有效。

- gbn

---

Stack Overflow的奇迹在于我可以坐在当地时间晚上10点30分左右，喝着啤酒，想知道为什么我的代码不起作用，然后在几秒钟内找到我需要的答案。 - wootscootinboogie

Answer 2

简而言之：不行。CTE仅对单个下一条语句有效，没有办法“延长”CTE的生命周期。

来自MSDN Books Online：

常用表达式（CTE）可以被视为在执行范围内定义的临时结果集，该范围限定于单个SELECT、INSERT、UPDATE、DELETE或CREATE VIEW语句。CTE类似于派生表，因为它不作为对象存储，只在查询期间持续存在。

您可以：

• turn your CTE into a view and use that view for your queries - views are available as long as they are defined

• store the results of the CTE into a temporary table or table variable for further processing

• change your statements after the CTE so they can be executed as a single statement

  WITH emp_CTE AS (
  ......)
  SELECT (list of columns)
  FROM emp_CTE
  INNER JOIN dbo.Employees e ON ......
  

- marc_s