WITH emp_CTE AS (
SELECT ROW_NUMBER() OVER(ORDER BY (SELECT 1)) AS IdentityId,*
FROM dbo.employee )
SELECT * FROM emp_CTE
@H_403_4@这很好用
@H_403_4@如果相同的查询是这样写的.
WITH emp_CTE AS (
SELECT ROW_NUMBER() OVER(ORDER BY (SELECT 1)) AS IdentityId,*
FROM dbo.employee )
SELECT * FROM EMPLOYEES
SELECT * FROM emp_CTE
@H_403_4@它给出了一条告诉emp_CTE不存在的消息.
@H_403_4@我们有什么方法可以解决这个问题吗？
@H_403_4@谢谢王子
解决方法
CTE仅是后续声明的一部分.
@H_403_4@后续语句可以是单个SELECT / INSERT / UPDATE / DELETE,也可以是复合(带UNION,INTERSECT等)
@H_403_4@例如：
;WITH cte1 AS
(
select ...
),cte2 AS
(
select ...
)
SELECT ...
UNION
SELECT ...;
@H_403_4@经验法则是范围直到下一个;将会.分号终止任何语句但不幸的是可选.
@H_403_4@你上面的失败代码实际上就是这个
...;
WITH emp_CTE AS (
SELECT ROW_NUMBER() OVER(ORDER BY (SELECT 1)) AS IdentityId,*
FROM dbo.employee )
SELECT * FROM EMPLOYEES;
SELECT * FROM emp_CTE;
@H_403_4@所以CTE只是在……范围之内……员工;
