自联接查询以查找节点语法问题的后代 [英] Self-joining query to find descendants of a node syntax issue

问题描述

我有一个自链接表，它为流程中的每个步骤"记录一条记录(Step.ParentStepId 是 Step.StepId 的外键):

I have a self-linking table that records one record for every "Step" in a process (Step.ParentStepId is a foreign key to Step.StepId):

CREATE TABLE [dbo].[Step](
    [StepId] [int] IDENTITY(1,1) NOT NULL,
    [Name] [varchar](50) NOT NULL,
    [Description] [text] NULL,
    [Sequence] [int] NULL,
    [ParentStepId] [int] NULL,
 CONSTRAINT [PK_Step] PRIMARY KEY CLUSTERED 
(
    [StepId] ASC
)
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]


ALTER TABLE [dbo].[Step]  WITH CHECK ADD  CONSTRAINT [FK_Step_Step] FOREIGN KEY([ParentStepId])
REFERENCES [dbo].[Step] ([StepId])

我想编写一个查询来返回给定 StepId 是父级的所有步骤(在任何级别).

I'd like to write a query to return all of the Steps where a given StepId is a parent (at any level).

Oracle 曾经为此提供一些很酷的 SQL 扩展.这将如何在 T-SQL、SQL 2008、R2 中完成?

Oracle used to have some cool SQL extensions for this. How would this be done in T-SQL, SQL 2008, R2?

这是我的尝试.请帮帮我，递归太常伤我的头.

Here's my attempt. Help me, please, recursion too often hurts my head.

DECLARE @StepId INT = 3

WITH cteRecursion
     AS (SELECT
             Stepid
             ,1 AS Level
         FROM
             Step
         WHERE
             StepId = @StepId
         UNION ALL
         SELECT
             t.StepId
             ,c.Level + 1
         FROM
             Step t
             INNER JOIN cteRecursion c
                 ON t.ParentStepId = c.StepId
        )
SELECT
    StepId,Level
FROM
    cteRecursion
ORDER BY
    Level,
    StepId;

运行时:

消息 319，级别 15，状态 1，第 3 行
关键字with"附近的语法不正确.如果这个陈述是一个公共表表达式、xmlnamespaces 子句或更改跟踪上下文子句，前面的语句必须以分号.

Msg 319, Level 15, State 1, Line 3
Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.

推荐答案

阅读精彩的错误信息！

它清楚地说:

关键字with"附近的语法不正确.如果该语句是公用表表达式、xmlnamespaces 子句或更改跟踪上下文子句，前一条语句必须以分号结束.

Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.

所以:用分号结束你之前的语句，你应该没问题！

So: terminate your previous statement with a semicolon, and you should be fine!

DECLARE @StepId INT = 3

; WITH cteRecursion
     AS (SELECT
             Stepid, 1 AS Level
         FROM
             Step
         WHERE
             StepId = @StepId
        .......

这篇关于自联接查询以查找节点语法问题的后代的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！