Mysql查询从表中选择多级 child

Question

这是我的用户表的MySQL表布局。
是否可以

1. 获取每个人下面的 child 数。
2. 选择手下至少有 2 个 child 的人。

+----+------+--------+
| id | Name | Parent |
+----+------+--------+
| 1  |    A |      0 |
+----+------+--------+
| 2  |    B |      0 |
+----+------+--------+
| 3  |    C |      1 |
+----+------+--------+
| 4  |    D |      3 |
+----+------+--------+
| 5  |    E |      2 |
+----+------+--------+

预期的答案是
1.

+----+------+----------+
| id | Name | Children |
+----+------+----------+
| 1  |    A |  2(C, D) |
+----+------+----------+
| 2  |    B |     1(E) |
+----+------+----------+
| 3  |    C |     1(D) |
+----+------+----------+
| 4  |    D |        0 |
+----+------+----------+
| 5  |    E |        0 |
+----+------+----------+

2.

+----+------+----------+
| id | Name | Children |
+----+------+----------+
| 1  |    A |  2(C, D) |
+----+------+----------+

Answer 1

MySQL 不支持递归查询(编辑:因为我在 2014 年回答了这个问题，MySQL 8.0 确实支持递归查询)，所以如果按照存储数据的方式存储数据(使用 parent 表示层级关系)。

您可以用几种不同的方式存储层次结构，以简化此问题。我在这里做了一个介绍:Models for Hierarchical Data with SQL and PHP .

我最喜欢的解决方案是 Closure Table。在此设计中，您使用第二个表来存储层次结构中的所有路径。包括长度为零的路径，这些路径将每个节点连接到自身，因为这使得以后的一些任务更容易。

CREATE TABLE TreePaths (
  ancestor INT NOT NULL,
  descendant INT NOT NULL,
  length INT NOT NULL DEFAULT 0
  PRIMARY KEY (ancestor, descendant)
);

INSERT INTO TreePaths VALUES
(1,1,0), (1,3,1), (1,4,2),
(2,2,0), (2,5,1),
(3,3,0), (3,4,1),
(4,4,0),
(5,5,0);

然后您可以查询给定节点的所有子节点:

SELECT descendant FROM TreePaths WHERE ancestor = 1 AND length > 0;

您可以通过按祖先分组并使用 HAVING 来选择组来将此限制为具有至少两个子节点的节点:

SELECT ancestor, COUNT(*), GROUP_CONCAT(descendant) FROM TreePaths WHERE length > 0
GROUP BY ancestor HAVING COUNT(*) >= 2;