单个select语句中的多个cte，其中cte可以互相引用 [英] multiple cte in single select statement where ctes can refer to each other

问题描述

扩展以下问题（多重选择语句）我想知道是否可以执行以下操作：

Expanding on following question (Multiple Select Statement) I would like to know if I can do following:

WITH 
    cte1 as (
      SELECT * from cdr.Location
    ),
    cte2 as (
      SELECT * from cdr.Location
        WHERE cdr.Location.someField = cte1.SomeField
    )
select * from cte1 union select * from cte2

在这里强调在以下行上：

So accent here is on following line:

 WHERE cdr.Location.someField = cte1.SomeField

在cte2中是引用cte1吗？

where within cte2 I'm referencing cte1 ?

推荐答案

是的，您可以在后续的CTE中引用先前声明的CTE：

Yes, you can reference previously declared CTEs in subsequent CTEs:

WITH cte1 as (
  SELECT t.* 
    FROM cdr.Location t),
     cte2 as (
  SELECT t.* 
    FROM cdr.Location t
    JOIN cte1 c1 ON c1.somefield = t.someField)
SELECT * 
  FROM cte1 
UNION 
SELECT * 
  FROM cte2

注释

1. 在cte1声明中加入cte2是行不通的，因为该语句是从上至下执行的。


2. 您像其他任何内联视图一样引用CTE（

BTW：尝试在表中列出一个更好的示例未来-对您和其他试图帮助您的SO社区其他人都是有益的。

BTW: Try to formulate a better example in the future - it's good for you and the rest of the SO community who are trying to help you.

这篇关于单个select语句中的多个cte，其中cte可以互相引用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！