递归SQL函数需要

Question

我有一个简单的自我引用表所描绘的位置：

CREATE TABLE [dbo].[Project](
    [ProjectId] [int] NOT NULL, 
    [ProjectName] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL, 
    [ParentProjectId] [int] NULL 
CONSTRAINT [PK_Project] PRIMARY KEY CLUSTERED 
(
    [ProjectId] ASC 
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY] 
) ON [PRIMARY] 

GO 
ALTER TABLE [dbo].[Project] WITH CHECK 
ADD CONSTRAINT [FK_Project_Project] FOREIGN KEY([ParentProjectId]) 
REFERENCES [dbo].[Project] ([ProjectId]) 

我试图得到一个存储过程将输出树视图类型的方式记录与已生成字段会索引的每个像的内容，像这样的表的项目的级别：

1.0  parent 
1.1  1st child 
1.2  2nd child 
1.2.1 1st grandchild of 2nd child 

我有此PROC返回LVL但这是一个平面表示，我想的场如上述所显示的数字（1.1.1 ，1.2等）

CREATE PROCEDURE [dbo].[rpt_ExpandProjectList_stefano]  
(@ProjectId int = null)  
AS  
BEGIN  
WITH ProjectList(ProjectId, ParentProjectId, ProjectName, 
       ParentPath, Fullpath, Level) 
AS 
( 
-- Anchor member definition 
    SELECT p.ProjectId, p.ParentProjectId, p.ProjectName, CONVERT(nvarchar(1000), ''), CONVERT(nvarchar(1000), p.ProjectName) AS FullPath, 0 AS Level 
    FROM Project AS p  
    WHERE p.ProjectId = @ProjectId 
    UNION ALL 
-- Recursive member definition 
    SELECT p.ProjectId, p.ParentProjectId, p.ProjectName, CONVERT(nvarchar(1000), d.FullPath), CONVERT(nvarchar(1000), d.FullPath + '|' + p.ProjectName) as FullPath, Level + 1 
    FROM Project AS p 
    INNER JOIN ProjectList AS d 
     ON p.ParentProjectId = d.ProjectId 
) 
-- Statement that executes the CTE 
SELECT pl.ProjectId, pl.ParentProjectId, pl.ProjectName, pl.ParentPath, pl.FullPath, pl.Level 
FROM ProjectList pl 
ORDER BY pl.FullPath 
END 

Answer 1

下面是得出每个组ParentProjectId值的行号值的版本，可以帮助

CREATE PROCEDURE [dbo].[rpt_ExpandProjectList_stefano]  
(@ProjectId int = null)  
AS  
BEGIN  

WITH ProjectNode (ProjectId, ParentProjectId, ProjectName, ProjectNode) 
AS 
(
-- Derive project node ID 
    SELECT ProjectId, ParentProjectId, ProjectName, 
     CAST(ROW_NUMBER() OVER (PARTITION BY ParentProjectId ORDER BY ProjectId) AS VARCHAR) AS ProjectNode 
    FROM Project 
), 

    ProjectList(ProjectId, ParentProjectId, ProjectName, 
       ParentPath, Fullpath, Level, FullNodePath) 
AS 
(
-- Anchor member definition 
    SELECT p.ProjectId, p.ParentProjectId, p.ProjectName, CONVERT(nvarchar(1000), ''), 
     CONVERT(nvarchar(1000), p.ProjectName) AS FullPath, 0 AS Level, 
     CONVERT(nvarchar(1000), p.ProjectNode) AS FullNodePath 
    FROM ProjectNode AS p  
    WHERE p.ProjectId = @ProjectId 
    UNION ALL 
-- Recursive member definition 
    SELECT p.ProjectId, p.ParentProjectId, p.ProjectName, CONVERT(nvarchar(1000), d.FullPath), 
     CONVERT(nvarchar(1000), d.FullPath + '|' + p.ProjectName) as FullPath, Level + 1, 
     CONVERT(nvarchar(1000), d.FullNodePath + '.' + p.ProjectNode) AS FullNodePath  
    FROM ProjectNode AS p 
    INNER JOIN ProjectList AS d 
     ON p.ParentProjectId = d.ProjectId 
) 

-- Statement that executes the CTE 
SELECT pl.ProjectId, pl.ParentProjectId, pl.ProjectName, pl.ParentPath, pl.FullPath, pl.Level, pl.FullNodePath 
FROM ProjectList pl 
ORDER BY pl.FullPath 
END 

你会发现一个额外的CTE（你原来的CTE之前）。行号被用作数字ID元素。