可以为公共表表达式创建嵌套子句吗?

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.

像这样工作吗?我以前试过，但我没办法让它工作。

6 个解决方案

#1

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

#2

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

#3

With does not work embedded, but it does work consecutive

With不工作嵌入，但它工作连续

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

EDIT Fixed the syntax...

编辑固定的语法…

Also, have a look at the following example

同样，请看下面的示例

SQLFiddle DEMO

#4

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://sqlblog.com/blogs/adam_machanic/archive/2015/04/07/re-inventing-the-recursive-cte.aspx

这些答案都很好，但是只要让条目正确排序，你最好还是看看这篇文章http://sqlblog.com/blogs/adam_machanic/archive/2015/04/04/07/reinvent-cte.aspx。

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

#5

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

#6

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中选择了一个内部连接作为我的select语句。第二个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

....其他ct