T-SQL：CTE别名的多种用法-不仅在外部查询中 [英] T-SQL: multiple usage of CTE alias - not only in outer query

问题描述

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

例如：

 -使用cte_test（domain1，domain2，[...]）定义CTE表达式
 
 AS 
 -CTE查询
（
从表
中选择domain1，domain2，[...] 
）
-外部查询
 SELECT * FROM cte_test 
-现在我想再次使用CTE表达式
插入某表（[...]）从cte_test 
 中选择[...] pre> 
 
 
最后一行将导致以下错误，因为它在外部查询之外：
 
 
消息208，级别16，状态1，第12行无效的对象名称'cte_test'。
 
 
有没有办法多次使用CTE。使它持久吗？我当前的解决方案是创建一个临时表，在其中存储CTE的结果，并将此临时表用于任何其他语句。 
 -CTE 
 [...] 
-在CTE块之后创建一个临时表
 DECLARE @tmp表（域1数据类型，域2数据类型，[...]）
插入@tmp（域1，域2，[...]）选择域1，域2，[...] FROM cte_test 
-任何其他DML语句
 SELECT * FROM @tmp 
插入到某个表（[...]）SELECT [...] FROM @tmp 
 [。 ..] 
  
坦白说，我不喜欢这种解决方案。 
 
 
 
预先感谢！
解决方案 div> 
 CommonTableExpression不会以任何方式持久化数据。基本上，这只是在主查询本身之前创建子查询的一种方法。
 
 
 
与普通子查询相比，它更像是嵌入式视图。查询将。因为您可以在一个查询中重复引用它，而不必一次又一次地键入它。
 
 
 
但是它仍被视为视图，扩展为查询引用它，宏之类的。 
 
 
 
 
 
 
 
 
不幸的是，这对您来说意味着您必须自己进行持久化。
 
 
 
 
 
 
如果您希望CTE的逻辑得以持久，那么您就不需要内联视图，您只需要一个视图。
 
 
 
 
如果要保留CTE的结果集，则需要临时表类型的解决方案，例如一个你不喜欢。
 
 
 
 
I've got a question which occurs when I was using the WITH-clause in one of my script. The question is easy to pointed out I wanna use the CTE alias multiple times instead of only in outer query and there is crux.

For instance:
-- 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
The last row will lead to the following error because it's outside the outer query:

  
Msg 208, Level 16, State 1, Line 12 Invalid object name 'cte_test'.
Is there a way to use the CTE multiple times resp. make it persistent? My current solution is to create a temp table where I store the result of the CTE and use this temp table for any further statements. 
-- 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
[...]
Frankly, I don't like this solution. Does anyone else have a best practice for this problem?

Thanks in advance!
解决方案
A CommonTableExpression doesn't persist data in any way.  It's basically just a way of creating a sub-query in advance of the main query itself.

This makes it much more like an in-line view than a normal sub-query would be.  Because you can reference it repeatedly in one query, rather than having to type it again and again.

But it is still just treated as a view, expanded into the queries that reference it, macro like.  No persisting of data at all.




This, unfortunately for you, means that you must do the persistance yourself.


If you want the CTE's logic to be persisted, you don't want an in-line view, you just want a view.
If you want the CTE's result set to be persisted, you need a temp table type of solution, such as the one you do not like.


                        
这篇关于T-SQL：CTE别名的多种用法-不仅在外部查询中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！