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不存在的消息.
谢谢
王子
解决方法
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;
所以CTE只是在……范围之内……员工;
