blocks|key|1240247|text|虽然不是严格嵌套，但您可以使用公用表表达式在后续查询中重用以前的查询。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1240248|要执行此操作，您要查找的语句的格式为|1240249|WITH+x+AS+
(
++++SELECT+*+FROM+MyTable
),+
y+AS+
(
++++SELECT+*+FROM+x
)
SELECT+*+FROM+y|code-block|syntax|javascript|1240250|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|L|8|@]|9|@]|A|$]]|$1|D|3|E|5|F|7|M|8|@]|9|@]|A|$G|H]]|$1|I|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|J|$]]

While not strictly nested, you can use common table expressions to reuse previous queries in subsequent ones. 

To do this, the form of the statement you are looking for would be

<pre><code>WITH x AS 
(
 SELECT * FROM MyTable
), 
y AS 
(
 SELECT * FROM x
)
SELECT * FROM y
</code></pre>

blocks|key|1455214|text|您可以执行以下操作，这称为递归查询：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1455215|WITH+y
AS
(
++SELECT+x,+y,+z
++FROM+MyTable
++WHERE+[base_condition]

++UNION+ALL

++SELECT+x,+y,+z
++FROM+MyTable+M
++INNER+JOIN+y+ON+M.[some_other_condition]+=+y.[some_other_condition]
)
SELECT+*
FROM+y|code-block|syntax|javascript|1455216|您可能不需要此功能。为了更好地组织我的查询，我做了以下工作：|1455217|WITH+y+
AS
(
++SELECT+*+
++FROM+MyTable
++WHERE+[base_condition]
),
x
AS
(
++SELECT+*+
++FROM+y
++WHERE+[something_else]
)
SELECT+*+
FROM+x|1455218|entityMap^0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|N|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|O|8|@]|9|@]|A|$]]|$1|I|3|J|5|D|7|P|8|@]|9|@]|A|$E|F]]|$1|K|3|-4|5|6|7|Q|8|@]|9|@]|A|$]]]|L|$]]

You can do the following, which is referred to as a recursive query:

<pre><code>WITH y
AS
(
 SELECT x, y, z
 FROM MyTable
 WHERE [base_condition]

 UNION ALL

 SELECT x, y, z
 FROM MyTable M
 INNER JOIN y ON M.[some_other_condition] = y.[some_other_condition]
)
SELECT *
FROM y
</code></pre>

You may not need this functionality. I've done the following just to organize my queries better:

<pre><code>WITH y 
AS
(
 SELECT * 
 FROM MyTable
 WHERE [base_condition]
),
x
AS
(
 SELECT * 
 FROM y
 WHERE [something_else]
)
SELECT * 
FROM x
</code></pre>

blocks|key|1455294|text|这些答案都很好，但就正确订购商品而言，您最好阅读本文http://dataeducation.com/dr-output-or-how-i-learned-to-stop-worrying-and-love-the-merge|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|1455295|以下是他的查询的一个示例。|1455296|WITH+paths+AS+(+
++++SELECT+
++++++++EmployeeID,+
++++++++CONVERT(VARCHAR(900),+CONCAT('.',+EmployeeID,+'.'))+AS+FullPath+
++++FROM+EmployeeHierarchyWide+
++++WHERE+ManagerID+IS+NULL

++++UNION+ALL

++++SELECT+
++++++++ehw.EmployeeID,+
++++++++CONVERT(VARCHAR(900),+CONCAT(p.FullPath,+ehw.EmployeeID,+'.'))+AS+FullPath+
++++FROM+paths+AS+p+
++++++++JOIN+EmployeeHierarchyWide+AS+ehw+ON+ehw.ManagerID+=+p.EmployeeID+
)+
SELECT+*+FROM+paths+order+by+FullPath|code-block|syntax|javascript|1455297|entityMap|0|LINK|mutability|MUTABLE|url|http://dataeducation.com/dr-output-or-how-i-learned-to-stop-worrying-and-love-the-merge^0|Q|2F|0|0|0|0^^$0|@$1|2|3|4|5|6|7|S|8|@]|9|@$A|T|B|U|1|V]]|C|$]]|$1|D|3|E|5|6|7|W|8|@]|9|@]|C|$]]|$1|F|3|G|5|H|7|X|8|@]|9|@]|C|$I|J]]|$1|K|3|-4|5|6|7|Y|8|@]|9|@]|C|$]]]|L|$M|$5|N|O|P|C|$Q|R]]]]

These answers are pretty good, but as far as getting the items to order properly, you'd be better off looking at this article
<a href="http://dataeducation.com/dr-output-or-how-i-learned-to-stop-worrying-and-love-the-merge" rel="nofollow noreferrer">http://dataeducation.com/dr-output-or-how-i-learned-to-stop-worrying-and-love-the-merge</a>

Here's an example of his query.

<pre><code>WITH paths AS ( 
 SELECT 
 EmployeeID, 
 CONVERT(VARCHAR(900), CONCAT('.', EmployeeID, '.')) AS FullPath 
 FROM EmployeeHierarchyWide 
 WHERE ManagerID IS NULL

 UNION ALL

 SELECT 
 ehw.EmployeeID, 
 CONVERT(VARCHAR(900), CONCAT(p.FullPath, ehw.EmployeeID, '.')) AS FullPath 
 FROM paths AS p 
 JOIN EmployeeHierarchyWide AS ehw ON ehw.ManagerID = p.EmployeeID 
) 
SELECT * FROM paths order by FullPath
</code></pre>

blocks|key|1455341|text|我们可以创建嵌套的cte.please参见下面的cte示例|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1455342|;with+cte_data+as+
(
Select+*+from+[HumanResources].[Department]
),cte_data1+as
(
Select+*+from+[HumanResources].[Department]
)

select+*+from+cte_data,cte_data1|code-block|syntax|javascript|1455343|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

we can create nested cte.please see the below cte in example

<pre><code>;with cte_data as 
(
Select * from [HumanResources].[Department]
),cte_data1 as
(
Select * from [HumanResources].[Department]
)

select * from cte_data,cte_data1
</code></pre>

blocks|key|1240542|text|我试图测量事件之间的时间，除了在开始和结束之间有多个进程的条目。我需要在其他单行进程的上下文中使用它。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1240543|我使用带有inner的select语句作为第N个cte中的select语句。第二个cte，我需要提取X上的开始日期和Y上的结束日期，并使用1作为左连接的id值，以便将它们放在一行上。|1240544|对我很有用，希望这能帮上忙。|1240545|cte_extract
as+
(
++++select+ps.Process+as+ProcessEvent
++++++++,+ps.ProcessStartDate+
++++++++,+ps.ProcessEndDate+
++++++++--+select+strt.*
++++from+dbo.tbl_some_table+ps+
++++inner+join+(select+max(ProcessStatusId)+ProcessStatusId+
++++++++++++++++++++from+dbo.tbl_some_table+
++++++++++++++++where+Process+=+'some_extract_tbl'+
++++++++++++++++and+convert(varchar(10),+ProcessStartDate,+112)+<+'29991231'
++++++++++++++++)+strt+on+strt.ProcessStatusId+=+ps.ProcessStatusID
),+
cte_rls
as+
(
++++select+'Sample'+as+ProcessEvent,+
+++++x.ProcessStartDate,+y.ProcessEndDate++from+(
++++select+1+as+Id,+ps.Process+as+ProcessEvent
++++++++,+ps.ProcessStartDate+
++++++++,+ps.ProcessEndDate
++++++++--+select+strt.*
++++from+dbo.tbl_some_table+ps+
++++inner+join+(select+max(ProcessStatusId)+ProcessStatusId+
++++++++++++++++++++from+dbo.tbl_some_table+
++++++++++++++++where+Process+=+'XX+Prcss'+
++++++++++++++++and+convert(varchar(10),+ProcessStartDate,+112)+<+'29991231'
++++++++++++++++)+strt+on+strt.ProcessStatusId+=+ps.ProcessStatusID
++++)+x
++++left+join+(
++++++++select+1+as+Id,+ps.Process+as+ProcessEvent
++++++++++++,+ps.ProcessStartDate+
++++++++++++,+ps.ProcessEndDate
++++++++++++--+select+strt.*
++++++++from+dbo.tbl_some_table+ps+
++++++++inner+join+(select+max(ProcessStatusId)+ProcessStatusId
++++++++++++++++++++from+dbo.tbl_some_table+
++++++++++++++++++++where+Process+=+'YY+Prcss+Cmpltd'+
++++++++++++++++++++and+convert(varchar(10),+ProcessEndDate,+112)+<+'29991231'
++++++++++++++++++++)+enddt+on+enddt.ProcessStatusId+=+ps.ProcessStatusID
++++++++++++)+y+on+y.Id+=+x.Id+
),|code-block|syntax|javascript|1240546|……其他ctes|1240547|entityMap^0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|O|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|P|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|Q|8|@]|9|@]|A|$]]|$1|F|3|G|5|H|7|R|8|@]|9|@]|A|$I|J]]|$1|K|3|L|5|6|7|S|8|@]|9|@]|A|$]]|$1|M|3|-4|5|6|7|T|8|@]|9|@]|A|$]]]|N|$]]

I was trying to measure the time between events with the exception of what one entry that has multiple processes between the start and end. I needed this in the context of other single line processes.

I used a select with an inner join as my select statement within the Nth cte. The second cte I needed to extract the start date on X and end date on Y and used 1 as an id value to left join to put them on a single line. 

Works for me, hope this helps. 

<pre><code>cte_extract
as 
(
 select ps.Process as ProcessEvent
 , ps.ProcessStartDate 
 , ps.ProcessEndDate 
 -- select strt.*
 from dbo.tbl_some_table ps 
 inner join (select max(ProcessStatusId) ProcessStatusId 
 from dbo.tbl_some_table 
 where Process = 'some_extract_tbl' 
 and convert(varchar(10), ProcessStartDate, 112) &lt; '29991231'
 ) strt on strt.ProcessStatusId = ps.ProcessStatusID
), 
cte_rls
as 
(
 select 'Sample' as ProcessEvent, 
 x.ProcessStartDate, y.ProcessEndDate from (
 select 1 as Id, ps.Process as ProcessEvent
 , ps.ProcessStartDate 
 , ps.ProcessEndDate
 -- select strt.*
 from dbo.tbl_some_table ps 
 inner join (select max(ProcessStatusId) ProcessStatusId 
 from dbo.tbl_some_table 
 where Process = 'XX Prcss' 
 and convert(varchar(10), ProcessStartDate, 112) &lt; '29991231'
 ) strt on strt.ProcessStatusId = ps.ProcessStatusID
 ) x
 left join (
 select 1 as Id, ps.Process as ProcessEvent
 , ps.ProcessStartDate 
 , ps.ProcessEndDate
 -- select strt.*
 from dbo.tbl_some_table ps 
 inner join (select max(ProcessStatusId) ProcessStatusId
 from dbo.tbl_some_table 
 where Process = 'YY Prcss Cmpltd' 
 and convert(varchar(10), ProcessEndDate, 112) &lt; '29991231'
 ) enddt on enddt.ProcessStatusId = ps.ProcessStatusID
 ) y on y.Id = x.Id 
),
</code></pre>

.... other ctes

blocks|key|1240596|text|不支持嵌套的'+With‘，但您可以始终使用第二个With作为子查询，例如：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1240597|WITH+A+AS+(
++++++++++++++++--WITH+B+AS+(+SELECT+COUNT(1)+AS+_CT+FROM+C+)+SELECT+CASE+_CT+WHEN+1+THEN+1+ELSE+0+END+FROM+B+--doesn't+work
++++++++++++++++SELECT+CASE+WHEN+count+=+1+THEN+1+ELSE+0+END+AS+CT+FROM+(SELECT+COUNT(1)+AS+count+FROM+dual)
++++++++++++++++union+all
++++++++++++++++select+100+AS+CT+from+dual
+++++++++++)
++++++++++++++select+CT+FROM+A|code-block|syntax|javascript|1240598|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

Nested 'With' is not supported, but you can always use the second With as a subquery, for example:

<pre><code>WITH A AS (
 --WITH B AS ( SELECT COUNT(1) AS _CT FROM C ) SELECT CASE _CT WHEN 1 THEN 1 ELSE 0 END FROM B --doesn't work
 SELECT CASE WHEN count = 1 THEN 1 ELSE 0 END AS CT FROM (SELECT COUNT(1) AS count FROM dual)
 union all
 select 100 AS CT from dual
 )
 select CT FROM A
</code></pre>

<pre><code>WITH y AS (
 WITH x AS (
 SELECT * FROM MyTable
 )
 SELECT * FROM x
)
SELECT * FROM y
</code></pre>

Does something like this work? I tried it earlier but I couldn't get it to work.

Can you create nested WITH clauses for Common Table Expressions?

WITH y AS (    WITH x AS (        SELECT * FROM MyTable    )    SELECT * FROM x)SELECT * FROM y像这样的东西能工作吗？我早些时候试过了，但我不能让它工作。

问是否可以为公用表表达式创建嵌套的WITH子句？
EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问是否可以为公用表表达式创建嵌套的WITH子句？EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问是否可以为公用表表达式创建嵌套的WITH子句？
EN