代码之家 › 专栏 › 技术社区 › Joe Phillips

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

common-table-expression nested tsql sql-server sql

155

Joe Phillips · 技术社区 · 16 年前

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

像这样的东西有用吗?我以前试过,但没能让它工作。

6 回复 | 直到 7 年前

256

spender 8 年前

虽然没有严格嵌套,但可以使用公共表表达式在后续查询中重用以前的查询。

要做到这一点,你要找的陈述形式是

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

David Andres 16 年前

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

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

Adriaan Stander 12 年前

不嵌入工作,但连续工作

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

编辑修复了语法…

另外,请看下面的示例

SQLFiddle DEMO

Community CDub 6 年前

这些答案很好,但只要能正确订购商品,你最好看一下这篇文章。 http://dataeducation.com/dr-output-or-how-i-learned-to-stop-worrying-and-love-the-merge

下面是他的查询示例。

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

Werner Henze 8 年前

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

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

select * from cte_data,cte_data1

natur3 7 年前

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

我在第n个CTE中使用了一个带有内部联接的select语句作为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 
),

…其他CTE