Почему я не могу получить доступ к моему CTE после того, как однажды использовал его?

Ответ 1

В вашем примере кода CTE сохраняется только для UPDATE. Если вам это нужно дольше, подумайте о том, чтобы с ним заполнить #tempTable или @tableVariable, а затем UPDATE и DELETE из них.

Вы также можете увеличить свой UPDATE, чтобы использовать OUTPUT, как показано ниже, чтобы вы могли захватить затронутые строки. И используйте их в DELETE, как здесь:

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)

Ответ 2

CTE не создает ничего "реального". Это всего лишь элемент language, способ выражения выражения таблицы, которое будет использоваться, возможно, неоднократно в заявлении. Когда вы скажете

WITH cteFoo AS (select ... from table where ...)
select ... from cteFoo where ...

- это еще один способ сказать

select ... from (select ... from table where ....) as cteFoo where ...

CTE и производные таблицы очень похожи, любой запрос с использованием производных таблиц может быть перезаписан как CTE, и любой нерекурсивный CTE может быть переписан как запрос с использованием производных таблиц. Лично я гораздо более предпочитаю форму CTE, более сжатую и легко читаемую.

CTE позволяют использовать выражение таблицы, используемое несколько раз, для объявления только один раз:

  WITH cte AS (select ... from table where ...)
  select ... 
    from cte a join cte b on ...
  where ...

Сравните это с семантически подобной производной табличной формой:

select ... 
from (
   select ... from table where ...) as a
join (
   select ... from table where ...) as b
   on ...
where ...

CTE явно читаем. Но вы должны понимать, что две формы создают один и тот же запрос. Форма CTE может предполагать, что создается промежуточный результат, тогда соединение выполняется на промежуточном результате, но это неверно. Форма CTE скомпонована в ту же форму, что и производная таблица, которая ясно показывает, что выражение CTE table выполняется дважды.

Ответ 3

Выражение 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); 

Ответ 4

Yep, предложение WITH MYCTE не создает постоянный объект для использования в нескольких запросах впоследствии: он только модифицирует один запрос, который вы добавляете в это предложение! Если вам нужна очень различная функциональность, рассмотрите вместо этого использование представлений...