如何获取所有父名称

please see the query. i want to develop a query in which when i give an id i need to get all the names recursively. for example when i give 3 i should get the names Customer,setup and Admin i need to get it without using temporarytable and cursors. Thanks in advance for your help.

请查看查询。我想开发一个查询，当我给出一个id时，我需要递归获取所有的名字。例如，当我给3我应该得到名称客户，设置和管理员我需要得到它而不使用临时表和游标。在此先感谢您的帮助。

DECLARE @tblPagePath TABLE 
                        (id int,
                         name varchar(100),
                         pid int);

INSERT INTO @tblPagePath
        ( id, name, pid )
VALUES  ( 1, -- id - int
          'Admin', -- name - varchar(100)
          null  -- pid - int
          ) 
INSERT INTO @tblPagePath
        ( id, name, pid )
VALUES  ( 2, -- id - int
          'Setup', -- name - varchar(100)
          1  -- pid - int
          )                      

INSERT INTO @tblPagePath
        ( id, name, pid )
VALUES  ( 3, -- id - int
          'Customer', -- name - varchar(100)
          2  -- pid - int
          );    



SELECT *
FROM @tblPagePath

3 个解决方案

#1

WITH C AS
(
  SELECT T.id, 
         T.name, 
         T.pid
  FROM @tblPagePath AS T
  WHERE T.id = 3
  UNION ALL
  SELECT T.id, 
         T.name, 
         T.pid
  FROM @tblPagePath AS T
    INNER JOIN C
      ON C.pid = T.id

)
SELECT *
FROM C
--WHERE C.id <> 3

#2

WITH Parents (ID, pid, Level, Name)
AS
(
  SELECT ID 'ID', 
         pid 'ParentId', 
         1 as level, 
         Name 'Name'
  FROM tblPagePath  
  WHERE ID = 3  
  UNION ALL
  SELECT  j.ID 'ID', 
          j.pid 'ParentId', 
          Level + 1, 
          j.Name 'Name'
  FROM tblPagePath  as j
    INNER JOIN Parents AS jpt ON j.ID = jpt.pid
)
SELECT * 
FROM Parents 
;

--- Enjoy

- - 请享用

#3

Assuming SQLServer:

假设SQLServer：

;with cte as (select id, id pid from @tblPagePath a
              where not exists (select null from @tblPagePath c
                                where a.id=c.pid)
              union all
              select c.id, t.pid
              from @tblPagePath t
              join cte c on c.pid =t.id)
select t.id, t.name 
from @tblPagePath t
join cte c on t.id = c.pid and c.id = @id

#1

WITH C AS
(
  SELECT T.id, 
         T.name, 
         T.pid
  FROM @tblPagePath AS T
  WHERE T.id = 3
  UNION ALL
  SELECT T.id, 
         T.name, 
         T.pid
  FROM @tblPagePath AS T
    INNER JOIN C
      ON C.pid = T.id

)
SELECT *
FROM C
--WHERE C.id <> 3

#2

WITH Parents (ID, pid, Level, Name)
AS
(
  SELECT ID 'ID', 
         pid 'ParentId', 
         1 as level, 
         Name 'Name'
  FROM tblPagePath  
  WHERE ID = 3  
  UNION ALL
  SELECT  j.ID 'ID', 
          j.pid 'ParentId', 
          Level + 1, 
          j.Name 'Name'
  FROM tblPagePath  as j
    INNER JOIN Parents AS jpt ON j.ID = jpt.pid
)
SELECT * 
FROM Parents 
;