1. 首页
  2. 问题
  3. 父-子SQL查询
admin

父-子SQL查询

sql

id parent_id
1 0
2 0
3 2
4 0
5 1
6 0

我需要一个查询，该查询将返回父行（parent_id = 0），然后返回其子行：

  1. first parent
  2. all children of first parent
  3. second parent
  4. all children of second parent
  5. third parent
  6. fourth parent

预期结果：按ID排序

id   parent_id
-------------------------------------------
1    0 (first parent)
5    1     (all children of first parent)
2    0 second parent
3    2     (all children of second parent)
4    0 third parent
6    0 fourth parent

我可以在所有孩子之后都使用父母联合，但这先给了我父母，然后才是孩子。我需要父母，立即需要它的孩子。

有人可以帮忙吗？


阅读 126

收藏
2021-06-07

共1个答案

admin

这可以使用两个临时表和三个变量来完成。

CREATE TABLE #Parents
(
RowId bigint identity(1,1),
Id    bigint
)



CREATE TABLE #Results
(
RowId    bigint identity(1,1),
Id       bigint,
ParentId bigint
)





DECLARE @Count1   bigint
DECLARE @Count2   bigint
DECLARE @ParentId bigint





INSERT INTO #Parents
SELECT Id 
FROM MyTable
WHERE ParentId = 0
ORDER BY Id





SET @Count1 = 0
SELECT @Count2 = MAX(RowId) FROM #Parents





WHILE @Count1 < @Count2
BEGIN
  SET @Count1 = @Count1 +1
  SELECT @ParentId = Id FROM #Parents WHERE RowId = @Count1
  INSERT INTO #Results (Id, ParentId) VALUES (@Count1, 0)
  INSERT INTO #Results (Id, ParentId) 
  SELECT ID, ParentId 
  FROM MyTable
  WHERE ID = @Count1
  ORDER BY Id
END





SELECT
Id,
ParentId
FROM #Results
ORDER BY RowId





DROP TABLE #Results
DROP TABLE #Parents
2021-06-07