父子sql查询

发布于 2024-08-08 17:39:19 字数 584 浏览 8 评论 0原文

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

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

第一个父级
第一个父级的所有子级
第二个父级第二
个父级的所有子级
第三个父级
第四个父级

预期结果：按 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

我可以使用父级的并集所有孩子都跟着但这首先给了我父母，然后是孩子。我需要父母，并且立即需要它的孩子。

有人可以帮忙吗？

原文

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

I need a query that will return parent rows (parent_id=0) followed by its child rows:

first parent
all children of first parent
second parent
all children of second parent
third parent
fourth parent

Expected result: ordered by 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

I can use union of parents followed by all childs
But that gives me parents first then the children.
I need parent and immediately its children.

Anyone can help?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

寂寞陪衬 2024-08-15 17:39:20

如果您使用的是 SQL Server 2005+，则可以使用递归 CTE，确保维护一个可以在最后进行排序的字段。

试试这个：

declare @t table (id int, parent_id int)
insert @t
select 1,0
union all select 2,0
union all select 3,2
union all select 4,0
union all select 5,1
union all select 6,0
;

with tree as (
select t.*, convert(varbinary(max),t.id) as ordered
from @t t
where parent_id = 0
union all
select t.*, ordered + convert(varbinary(max),t.id)
from tree base
 join
 @t t
 on t.parent_id = base.id
 )
select * 
from tree
order by ordered
;

If you're in SQL Server 2005+, you can use a recursive CTE, making sure that you maintain a field that you can order by at the end.

Try this:

declare @t table (id int, parent_id int)
insert @t
select 1,0
union all select 2,0
union all select 3,2
union all select 4,0
union all select 5,1
union all select 6,0
;

with tree as (
select t.*, convert(varbinary(max),t.id) as ordered
from @t t
where parent_id = 0
union all
select t.*, ordered + convert(varbinary(max),t.id)
from tree base
 join
 @t t
 on t.parent_id = base.id
 )
select * 
from tree
order by ordered
;

回复收藏 0 原文

深海夜未眠 2024-08-15 17:39:20

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


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

创建表#结果
（
RowId bigint 身份(1,1),
ID 大整数，
父 ID bigint
）

声明@Count1 bigint
声明 @Count2 bigint
声明@ParentId bigint

插入#Parents
选择 ID 
来自我的表
其中父 ID = 0
按 Id

 SET @Count1 = 0 排序
从 #Parents 中选择 @Count2 = MAX(RowId)

 WHILE @Count1 < @Count2
开始
  设置@Count1 = @Count1 +1
  SELECT @ParentId = Id FROM #Parents WHERE RowId = @Count1
  插入 #Results (Id, ParentId) 值 (@Count1, 0)
  插入#Results（Id，ParentId） 
  选择 ID、父级 ID 
  来自我的表
  其中 ID = @Count1
  按 ID 排序
结束

选择
ID，
父ID
来自#Results
ORDER BY RowId

删除表#Results
删除表#Parents

This can be accomplished using two temp tables and three variables.



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