我必须通过订单检索父子 [英] I have to retrieve parent-child by order

问题描述

id |parent_id |name
-------------------------
1  |0         |Sports
2  |0         |Clothes   
3  |0         |Facial     
4  |2         |Mens 
5  |0         |Electronics
6  |2         |Shirts
7  |3         |Cream
8  |5         |Mobile
9  |8         |Apple

< b>我尝试了什么：

What I have tried:

id |parent_id |name
-------------------------
1  |0         |Sports
2  |0         |Clothes   
4  |2         |Mens 
6  |2         |Shirts
3  |0         |Facial
7  |3         |Cream     
5  |0         |Electronics
8  |5         |Mobile
9  |8         |Apple

推荐答案

首先，请按照CHILL60 [ ^ ]。



试试这个：

First of all, please follow the link provided by CHill60[^].

Try this:

DECLARE @hierarchy TABLE (id INT IDENTITY(1,1), parent_id INT, name NVARCHAR(50))
INSERT INTO @hierarchy(parent_id, name)
VALUES(0, 'Sports'),
(0, 'Clothes'),
(0, 'Facial'),
(2, 'Mens'),
(0, 'Electronics'),
(2, 'Shirts'),
(3, 'Cream'),
(5, 'Mobile'),
(8, 'Apple')

;WITH CTE AS
(
        --initial query
	SELECT id, parent_id, name, 1 AS distance, id as pid
	FROM @hierarchy 
	WHERE parent_id = 0
        --recursive part
	UNION ALL
	SELECT h.id, h.parent_id, h.name, distance + 1 AS distance, h.parent_id As pid
	FROM @hierarchy h INNER JOIN CTE c ON c.id = h.parent_id 
)
SELECT *
FROM CTE 
ORDER BY pid, distance

如你所见，我使用小技巧以正确的顺序返回数据：

- 在第一个循环中：

     - 我将1设为距离

     - 我从[code> @hierarchy 表中得到[id]作为[pid]

- 在每个下一个循环中：

     - 我正在计算距离，加上1

     - 我从[code> @hierarchy 表中获取[parent_id]作为[pid]

As you can see, i've used small trick to return data in a correct order:
- in the first loop:
     - i'm setting 1 as a distance
     - i'm getting [id] from @hierarchy table as [pid]
- in every next loop:
     - i'm calculating distance by adding 1
     - i'm getting [parent_id] from @hierarchy table as [pid]

根据我的评论，我不在发布链接到我自己的文章的习惯，但在这种情况下，我认为工作的例子遍历层次结构可能是你需要的。



如果您仍然遇到问题，请完成示例并返回代码 - 处理SQL Server中的循环 [ ^ ]

As per my comment, I'm not in the habit of posting links to my own articles but in this case I think the worked example "Traversing a hierarchy" could be what you need.

Work through the example and come back with your code if you are still having problems - Processing Loops in SQL Server[^]

这篇关于我必须通过订单检索父子的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！