虽然不是严格嵌套的，但可以使用公共表表达式在后续查询中重用先前的查询。

要做到这一点，您正在寻找的语句的形式将是

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

我试图测量事件之间的时间，除了在开始和结束之间有多个进程的一个条目。我需要在其他单行流程的上下文中使用它。

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

对我有用，希望有帮助。

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

．．．． 其他ct

虽然不是严格嵌套的，但可以使用公共表表达式在后续查询中重用先前的查询。

要做到这一点，您正在寻找的语句的形式将是

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

不支持嵌套的'With'，但你总是可以使用第二个With作为子查询，例如:

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

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

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

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

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

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

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

编辑 修正了语法…

还有，看看下面的例子

SQLFiddle演示