почему я не могу получить доступ к своему CTE после того, как я использовал его однажды?
Около "материала" изменений дорожки существует также опция сравнить документы, я верю.
4 ответа
В вашем примере кода CTE сохраняется только для UPDATE. Если вам нужно, чтобы он прослужил дольше, рассмотрите возможность заполнения им #tempTable или @tableVariable, а затем UPDATE и DELETE из них.
Вы также можете дополнить UPDATE, чтобы использовать предложение OUTPUT , например следующие, чтобы вы могли захватить затронутые строки. И используйте их в УДАЛЕНИИ, как здесь:
set nocount on
DECLARE @Table table (PK int, col1 varchar(5))
DECLARE @SavedPks table (PK int)
INSERT INTO @Table VALUES (1,'g')
INSERT INTO @Table VALUES (2,'g')
INSERT INTO @Table VALUES (3,'g')
INSERT INTO @Table VALUES (4,'g')
INSERT INTO @Table VALUES (5,'x')
INSERT INTO @Table VALUES (6,'x')
set nocount off
;WITH MYCTE
AS
(
SELECT PK, col1 FROM @Table
)
UPDATE MYCTE
SET col1='xyz'
OUTPUT INSERTED.PK
INTO @SavedPks
WHERE col1='g'
SELECT 'A',* FROM @Table
DELETE @Table
WHERE PK IN (SELECT PK from @SavedPks)
SELECT 'B',* FROM @Table
ВЫХОД:
(4 row(s) affected)
PK col1
---- ----------- -----
A 1 xyz
A 2 xyz
A 3 xyz
A 4 xyz
A 5 x
A 6 x
(6 row(s) affected)
(4 row(s) affected)
PK col1
---- ----------- -----
B 5 x
B 6 x
(2 row(s) affected)
Да, предложение WITH MYCTE является не создавать постоянный объект для последующего использования в нескольких запросах: он изменяет только один запрос, к которому вы добавляете это предложение! Если вам нужна совершенно другая функциональность, рассмотрите возможность использования представлений ...
Выражение CTE допустимо только в его теле. Если вы хотите использовать его в других местах, вам следует также повторить предложение WITH .
WITH MYCTE(....) AS ( ... )
UPDATE ... (using my CTE);
-- a semicolon is necessary for statements followed by a CTE declaration
WITH MYCTE(....) AS ( ... )
DELETE ( using my CTE);
CTE don't create anything 'real'. They are merely a language element, a way to express a table expression that will be used, possible repeatedly, in a statement. When you say
WITH cteFoo AS (select ... from table where ...)
select ... from cteFoo where ...
is just another way of saying
select ... from (select ... from table where ....) as cteFoo where ...
CTE and derived tables are very similar, any query using derived tables can be rewriten as a CTE, and any non-recursive CTE can be rewritten as a query using derived tables. Personally, I much more preffer the CTE form as is more concise and easy to read.
CTEs allow for a table expression used multiple times to be declare only once:
WITH cte AS (select ... from table where ...)
select ...
from cte a join cte b on ...
where ...
Compare this with the semantically similar derived table form:
select ...
from (
select ... from table where ...) as a
join (
select ... from table where ...) as b
on ...
where ...
The CTE is clearly more readable. But you must understand that the two forms are producing the same query. The CTE form might suggest that an intermediate result is created then the join is run on the intermediate result, but this is not true. The CTE form is compiled into exactly the same form as the derived table one, which makes clear the fact that the CTE's table expresion is run twice.