数据库里有一张 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