T-SQL：CTE别名的多种用法-不仅在外部查询中

Question

我在一个脚本中使用WITH子句时会发生一个问题。问题很容易指出，我想多次使用CTE别名，而不是仅在外部查询中使用，并且存在症结。

例如：

-- Define the CTE expression
WITH cte_test (domain1, domain2, [...])
AS
-- CTE query
(
    SELECT domain1, domain2, [...]
    FROM table
)
-- Outer query
SELECT * FROM cte_test
-- Now I wanna use the CTE expression another time
INSERT INTO sometable ([...]) SELECT [...] FROM cte_test

最后一行将导致以下错误，因为它在外部查询之外：

消息208，级别16，状态1，第12行无效的对象名称’cte_test’。

有没有办法多次使用CTE。使它持久吗？我当前的解决方案是创建一个临时表，在其中存储CTE的结果，并将此临时表用于任何其他语句。

-- CTE
[...]
-- Create a temp table after the CTE block
DECLARE  @tmp TABLE (domain1 DATATYPE, domain2 DATATYPE, [...])
INSERT INTO @tmp (domain1, domain2, [...]) SELECT domain1, domain2, [...] FROM cte_test
-- Any further DML statements
SELECT * FROM @tmp
INSERT INTO sometable ([...]) SELECT [...] FROM @tmp
[...]

坦白说，我不喜欢这种解决方案。还有其他人对此问题有最佳实践吗？

提前致谢！

Answer 1

CommonTableExpression不会以任何方式持久化数据。基本上，这只是在主查询本身之前创建子查询的一种方法。

与普通子查询相比，它更像是嵌入式视图。因为您可以在一个查询中重复引用它，而不必一次又一次地键入它。

但是它仍然只是一个视图，扩展为引用它的查询，就像宏一样。完全没有数据持久性。

不幸的是，这意味着您必须自己进行持久化。

• 如果您希望CTE的逻辑得以保留，则您不需要嵌入式视图，而只需要一个视图。

• 如果要保留CTE的结果集，则需要一种临时表类型的解决方案，例如您不喜欢的解决方案。