在SQL中的Recurssive查询中需要帮助

I need help in sql recursive query, for example purpose i m providing sample table with insert script.

我在sql递归查询中需要帮助,例如我提供带有插入脚本的示例表。

CREATE   TABLE Details(
parentid varchar(10), DetailComponent varchar(10) , DetailLevel int)
GO

INSERT INTO Details 
SELECT '','7419-01',0 union all
SELECT '7419-01','44342-00',1 union all
SELECT '7419-01','45342-00',1 union all
SELECT '7419-01','46342-00',1 union all
SELECT '7419-01','47342-00',1 union all
SELECT '7419-01','48342-00',1 union all
SELECT '7419-01','49342-00',1 union all
SELECT '7419-01','50342-00',1 union all
SELECT '50342-00','51342-00',2 union all
SELECT '7419-01','52342-00',1 union all
SELECT '52342-00','54342-00',2 union all
SELECT '54342-00','54442-00',3 union all
SELECT '54342-00','54552-00',3 union all
SELECT '54552-00','R34S-54',4 union all
SELECT '54552-00','R123-54',4 union all
SELECT '54552-00','R111-54',4 union all
SELECT 'R111-54','R222-54',5 union all
SELECT 'R222-54','52342-00',6 union all
SELECT '7419-01','TEST34-00',1 union all
SELECT 'TEST34-00','445334-00',2 union all
SELECT '445334-00','52342-00',3  union all
SELECT '7419-01','1111-00',1 union all
SELECT '7419-01','1111-00',1 union all
SELECT '1111-00','52342-00',2 
GO

SELECT * FROM Details

From the above table data i want a search query , for example if I search data with "52342-00" I want output to be below format using CTE.

从上面的表数据我想要一个搜索查询,例如,如果我用“52342-00”搜索数据,我希望输出低于格式使用CTE。

NULL,'7419-01',0
'7419-01','52342-00',1
'7419-01','52342-00',1
'52342-00','54342-00',2
'54342-00','54552-00',3
'54552-00','R111-54',4
'R111-54','R222-54',5
'R222-54','52342-00',6

kindly provide suggestions .

请提供建议。

1 个解决方案

#1

To get your output, you would have to specify the DetailLevel in your cte, since just searching for '52342-00' will return multiple results.

要获得输出,您必须在cte中指定DetailLevel,因为只搜索'52342-00'将返回多个结果。

This will get you your results without specifying the exact DetailLevel:

这将为您提供结果,而无需指定确切的DetailLevel:

;with cte as (
select d.parentid,d.DetailComponent,d.DetailLevel as DetailLevel
from @Details d
  inner join (select distinct DetailComponent, MAX(DetailLevel) as DetailLevel from @Details
                group by DetailComponent) d2
    on d.DetailComponent = d2.DetailComponent
    and d.DetailLevel = d2.DetailLevel
where d.DetailComponent = '52342-00'
union all
select a.parentid, a.DetailComponent,a.DetailLevel
from @Details a
  inner join cte b
    on a.DetailComponent = b.parentid
    and a.DetailLevel < b.DetailLevel
  )

select * from cte
order by DetailLevel asc

#1