在Recurssive Query中需要帮助 [英] Need help in Recurssive Query

问题描述

我有一个名为test的表： -

i have a table called test :-

create table test (id int , Docname varchar(200), parentkey int, ContentType varchar(200))

insert into test values (1,'ParentFolder',-3,'Folder')
insert into test values (2,'ChildFolder1',1,'Folder')
insert into test values ( 3,'ChildFolder1',2,'Folder')
insert into test values (4,'ParentFolder 2',-3,'Folder')
insert into test values (5,'test Folder 1',4,'Folder')
insert into test values (6,'test Folder 2',4,'Folder')

$这个表中的b $ b id是主键，我在这个表中有一个名为parentkey的列，在这个表中有父子关系的记录



ex： - id姓名PrentKey

1测试-3

2 test2 1



-3是这里的父键，1个vlaue在parentkey中是id 1的孩子，



这里如果我给孩子id密钥我想得到所有n那个孩子的父亲n的父亲的文件。例如： - 如果我给''2'作为输入参数我想要如下所示：



ID路径

1-2 test-test2



这个我尝试下面的查询： -

in this table id is the primarykey, i have a column called parentkey in this table, in this table records having parent-child relation

ex:- id Name PrentKey
1 test -3
2 test2 1

-3 is the parentkey here, 1 vlaue in the parentkey is the child of id 1,

here if i give child id key i want to get the all names of that child n parent fileds. example :- if i give ''2'' as input parament i want out put like below :

ID Path
1-2 test-test2

for this i m trying below query :-

 WITH Emp_CTE (id,ParentKey,ContentType,Path,LEVEL) AS
  (SELECT id ,
          ParentKey,
          ContentType,
          CONVERT(varchar(1000),DocName) ,
          0 LEVEL
   FROM test S
   WHERE id = 2
   UNION ALL SELECT SS.ID , SS.ParentKey,SS.ContentType, CONVERT(varchar(1000), ISNULL(SS.DocName,'')+'|'+ISNULL(Path,'')) ,LEVEL +1
   FROM test SS
   INNER JOIN Emp_CTE ecte ON ecte.ParentKey = SS.ID)
SELECT CASE
           WHEN Path IS NULL THEN ''
           ELSE '|'
       END + Path AS folderpath,
             *
FROM Emp_CTE
WHERE ContentType = 'Folder'
  AND ParentKey = -3

ablove查询输出如下： -



ID路径

1 test | test2



这里我要显示所有ID，但我能够附加表格中的所有文档名称，如何实现id''s喜欢1-2？

ablove query is giving output as below :-

ID Path
1 test|test2

here i want to display all the IDs , but i am able to append all the docnames from table , how to achive id''s like 1-2?

推荐答案

WITH Emp_CTE(DocName, id, ContentType, ParentKey) AS
(
    SELECT DocName, id, ContentType, '-3' AS ParentKey
    FROM test
    WHERE ParentKey = -3
    UNION ALL
    SELECT e.DocName, e.id, e.ContentType, e.ParentKey + 1
    FROM test AS e
        INNER JOIN test AS d
        ON e.ParentKey = d.ParentKey
)
SELECT DocName, id, ContentType, ParentKey
FROM test

这篇关于在Recurssive Query中需要帮助的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！