SQL 查询父子节点并限制行数的写法？

V2EX = way to explore

V2EX 是一个关于分享和探索的地方

现在注册

已注册用户请登录

这是一个创建于 1725 天前的主题，其中的信息可能已经有所发展或是发生改变。

数据库里有一张 tree 表保存了树结构的数据，有 id 和 parent 两列，子节点的 parent 等于父节点的 id，没有父节点的 parent=0。

能否做到在一次查询中获取：

前两个 parent=0 的节点
上一步查询出来的每个节点的前两个子节点

比如:

INSERT INTO "tree" ("id", "parent") VALUES (1, 0); INSERT INTO "tree" ("id", "parent") VALUES (2, 1); INSERT INTO "tree" ("id", "parent") VALUES (3, 1); INSERT INTO "tree" ("id", "parent") VALUES (4, 1); INSERT INTO "tree" ("id", "parent") VALUES (5, 0); INSERT INTO "tree" ("id", "parent") VALUES (6, 5); INSERT INTO "tree" ("id", "parent") VALUES (7, 5); INSERT INTO "tree" ("id", "parent") VALUES (8, 5); INSERT INTO "tree" ("id", "parent") VALUES (9, 5); INSERT INTO "tree" ("id", "parent") VALUES (10, 0);

查询出结果应该是（顺序无所谓）：

+-----+--------+ | id | parent | +-----+--------+ | 1 | 0 | | 5 | 0 | | 2 | 1 | | 3 | 1 | | 6 | 5 | | 7 | 5 | +-----+--------+

尝试写了下但是不对，数据库是 pg 和 mariadb，不知道如何限制每个节点的前两个。

WITH top AS (SELECT * FROM tree WHERE parent=0 LIMIT 2) (SELECT * FROM top) UNION SELECT B.* FROM top JOIN (SELECT * FROM tree LIMIT 2) AS B ON B.parent=top.id

1 条回复 2021-01-27 15:18:30 +08:00

tianhei826

2021-01-27 15:18:30 +08:00

with tmp as
(select id
from a
where parent = 0
and rownum <= 2
order by parent asc)
select a.*
from tmp t, a a
where t.id = a.parent
and rownum <= 2
order by parent desc;