使用CTE在SQL中的递归查询中需要帮助 [英] Need Help in Recursive Query in SQL using CTE

问题描述

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

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

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

从上表数据中我想要一个搜索查询，例如，如果我使用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



谢谢

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.

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

thanks

推荐答案

这本身不是解决方案，因为评论是不够的。



这是父子关系的递归cte。

This is not a solution in itself, as the comment is not really adequate.

Here is a recursive cte for the parent child relationship.

with DetailData as (
	SELECT
		'' ParentID,
		'7419-01' DetailComponent,
		0 DetailLevel
	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
), DetailDataRecursive as (
	select 
		ParentID,
		DetailComponent,
		DetailLevel
		,
		convert(varchar(max), DetailComponent) NameSpaceDetailComponent,
		convert(varchar(max), row_number() over (partition by ParentID order by DetailComponent)) SortCode,
		1 level
	from DetailData
	where ParentID = ''

	union all
	select 
		DetailDataChild.ParentID,
		DetailDataChild.DetailComponent,
		DetailDataChild.DetailLevel
		,
		DetailDataParent.NameSpaceDetailComponent + '.' + DetailDataChild.DetailComponent NameSpaceDetailComponent,
		DetailDataParent.SortCode + '.' + convert(varchar(max), format(row_number() over (partition by DetailDataChild.ParentID order by DetailDataChild.DetailComponent), 'd4')),
		level + 1 level

	from DetailData DetailDataChild
	inner join DetailDataRecursive DetailDataParent
		on DetailDataChild.ParentID = DetailDataParent.DetailComponent
	--a limit has been implemented here as otherwise too many recursions
	where DetailDataParent.level <= 10
)
select * from DetailDataRecursive
order by SortCode
;

从此查询中，您应该能够在NameSpaceDetailComponent列中看到父子关系。



如果这是正确的和您的预期结果。

然后发表评论，我可以帮助更多。

From this query you should be able to see the parent child relationships in the NameSpaceDetailComponent column.

If this is correct and your expected result.
Then make a comment and I may be able to help some more.

不确定我是否正确理解了这个问题，但是如果你需要找到父组件根据给定的组件ID，可能是这样的：

Not sure if I understand the question correctly, but if you need to find the parent components based on the given component id, perhaps something like this:

WITH BOM (ParentComponent, ChildComponent, DetailLevel) AS (
    SELECT a.parentid AS ParentComponent, 
	       a.DetailComponent AS ChildComponent,
           a.DetailLevel AS DetailLevel
    FROM Details a
	WHERE a.DetailComponent = '52342-00' -- defines the most detailed row
    UNION ALL
    SELECT a.parentid AS ParentComponent, 
	       a.DetailComponent AS ChildComponent,
           a.DetailLevel AS DetailLevel
    FROM Details a INNER JOIN BOM AS b
        ON b.ParentComponent = a.DetailComponent AND b.DetailLevel - 1 = a.DetailLevel
        -- move to parent which is one level above
)
SELECT DISTINCT b.ParentComponent, b.ChildComponent, b.DetailLevel
FROM BOM b
ORDER BY b.DetailLevel, b.ParentComponent, b.ChildComponent

检查这个....



使用CTE的SQL递归 [ ^ ]



希望这会对你有所帮助。





干杯

Hi,

Check this....

Recursion in SQL using CTE[^]

hope this will help you.


Cheers

这篇关于使用CTE在SQL中的递归查询中需要帮助的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！