SQL 您可以为公用表表达式创建嵌套的 WITH 子句吗？

Can you create nested WITH clauses for Common Table Expressions?

提问by Joe Phillips

WITH y AS (
    WITH x AS (
        SELECT * FROM MyTable
    )
    SELECT * FROM x
)
SELECT * FROM y

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

这样的东西有用吗？我早些时候尝试过，但我无法让它工作。

回答by spender

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

为此，您要查找的语句的形式是

WITH x AS 
(
    SELECT * FROM MyTable
), 
y AS 
(
    SELECT * FROM x
)
SELECT * FROM y

回答by David Andres

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

您可以执行以下操作，这称为递归查询：

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

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

您可能不需要此功能。我做了以下只是为了更好地组织我的查询：

WITH y 
AS
(
  SELECT * 
  FROM MyTable
  WHERE [base_condition]
),
x
AS
(
  SELECT * 
  FROM y
  WHERE [something_else]
)
SELECT * 
FROM x

回答by Adriaan Stander

With does not work embedded, but it does work consecutive

With 不能嵌入，但它可以连续工作

;WITH A AS(
...
),
B AS(
...
)
SELECT *
FROM A
UNION ALL
SELECT *
FROM B

EDITFixed the syntax...

编辑修复了语法...

Also, have a look at the following example

另外，看看下面的例子

SQLFiddle DEMO

SQLFiddle 演示

回答by Don Rolling

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

这些答案非常好，但就让物品正确订购而言，您最好查看这篇文章 http://dataeducation.com/dr-output-or-how-i-learned-to-stop -担心和喜欢合并

Here's an example of his query.

这是他的查询示例。

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

回答by natur3

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.

我在第 N 个 cte 中使用了带有内部连接的选择作为我的选择语句。第二个 cte 我需要提取 X 上的开始日期和 Y 上的结束日期，并使用 1 作为左连接的 id 值将它们放在一行中。

Works for me, hope this helps.

对我有用，希望这会有所帮助。

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 
),

.... other ctes

.... 其他 ctes

回答by Subhransu Panda

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

我们可以创建嵌套的 cte。请参见下面的 cte 示例

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

select * from cte_data,cte_data1