如何使用Select SQL Server查询检索所有子项

I have an input table which looks as follows:

我有一个输入表,如下所示:

ID       NAME       PARENT_ID
------------------------------------
1        ABC          0
2        DEF          1
3        XYZ          1
4        PQR          2
5        GHI          0
6        JKL          5
7        MNO          6
8        STU          6

I want the output as follows in the form a string comma separated:

我希望输出如下形式的字符串逗号分隔:

ABC, ABC -> DEF, ABC -> XYZ, ABC -> DEF -> PQR
GHI, GHI -> JKL, GHI -> JKL -> MNO, GHI -> JKL -> STU

I tried a CTE and Cross Apply but with no success. Can someone please help me to achieve this?

我尝试了CTE和Cross Apply但没有成功。有人可以帮助我实现这个目标吗?

Best regards

3 个解决方案

#1

you can do something like this. If you want you can check in your SQL I have also created insert query for you just check that.

你可以做这样的事情。如果你想要你可以签入你的SQL我也为你创建了插入查询只是检查。

Data For Check The Output

用于检查输出的数据

create table tbl
(
 ID int,
 NAME  varchar(20),
 PARENT_ID int
 )  
 insert into
 tbl
 values
 (1 ,'ABC', 0),

(2, 'DEF', 1),

(3 ,'XYZ', 1),

(4 ,'PQR', 2)
select * from tbl

Query

select table2.NAME as ChildName,table1.NAME as ParentName from tbl as table1 
inner join tbl table2 on table1.ID = table2.PARENT_ID

#2

If you want retrieve a hierarchical list :

如果要检索分层列表:

with cte_1(ID, PARENT_ID, LABEL)
as (
  select ID, PARENT_ID, cast(NAME as varchar(512))
  from a_table
  where PARENT_ID = 0
union all
  select a.ID, a.PARENT_ID,  cast(b.LABEL + ' --> ' +a.NAME as varchar(512))
  from a_table a
  join cte_1 b on a.PARENT_ID = b.ID
)
select LABEL from cte_1
order by LABEL

This gives you :

这给你:

'ABC'
'ABC --> DEF'
'ABC --> DEF --> PQR'
'ABC --> XYZ'
'GHI'
'GHI --> JKL'
'GHI --> JKL --> MNO'
'GHI --> JKL --> STU'

If you also want to concanenate the list in a single string you can use more CTE:

如果您还希望将列表标记为单个字符串,则可以使用更多CTE:

-- recursively build the hierarchical list
with cte_1(ID, PARENT_ID, LABEL)
as (
  select ID, PARENT_ID, cast(NAME as varchar(512))
  from a_table
  where PARENT_ID = 0
union all
  select a.ID, a.PARENT_ID,  cast(b.LABEL + ' --> ' +a.NAME as varchar(512))
  from a_table a
  join cte_1 b on a.PARENT_ID = b.ID
),
-- order them
cte_2 (NR, LABEL)
as (
  select row_number() over (order by LABEL) as NR,
  LABEL  
  from cte_1 
), 
-- recursive concatenation
cte_3 (NR, LABEL) as (
  select NR, cast(LABEL as varchar(1024))
  from cte_2 where NR = 1
union all
  select a.NR, cast(b.LABEL + ', ' + a.LABEL as varchar(1024))
  from cte_2 a
  join cte_3 b on a.NR = b.NR + 1
)
-- get the result
select top 1 LABEL
from cte_3
order by len(LABEL) desc

The result is :

结果是:

'ABC, ABC --> DEF, ABC --> DEF --> PQR, ABC --> XYZ, GHI, GHI --> JKL, GHI --> JKL --> MNO, GHI --> JKL --> STU'

#3

based on @bwt answer

基于@bwt回答

you can do the following

你可以做到以下几点

declare @table table(Id int identity(1,1),
                     Name varchar(10),
                     Parent_Id int)
insert into @table(Name,Parent_Id) values('ABC',0)
insert into @table(Name,Parent_Id) values('DEF',1)
insert into @table(Name,Parent_Id) values('XYZ',1)
insert into @table(Name,Parent_Id) values('PQR',2)
insert into @table(Name,Parent_Id) values('GHI',0)
insert into @table(Name,Parent_Id) values('JKL',5)
insert into @table(Name,Parent_Id) values('MNO',6)
insert into @table(Name,Parent_Id) values('STU',6);

with c(ID, PARENT_ID, LABEL)
as (
  select ID, PARENT_ID, cast(NAME as varchar(512))
  from @table
  where PARENT_ID = 0
union all
  select a.ID, a.PARENT_ID,  cast(b.LABEL + ' --> ' +a.NAME as varchar(512))
  from @table a
  join c b on a.PARENT_ID = b.ID
)

--use the stuff

- 使用这些东西

select replace(STUFF((Select ',' +LABEL
from c t1
FOR XML PATH('')),1,1,''),'&gt;','>')

here a working DEMO

这是一个有效的DEMO

#1