mysql自引用表返回父级的id和子级的数量

Question

我有一个自引用表，我想要一个 mysql 查询，该查询将仅返回最顶层的父项（parent = 0）以及属于每个表的子项数量。这是我到目前为止所拥有的，但我知道它不会起作用。

SELECT id, (SELECT COUNT(id) FROM example where Parent_id = id) FROM example WHERE Parent_id = 0;

+--------+-----------+
|   id   | parent_id |
+--------+-----------+
|    1   |     0     |
|    2   |     1     |
|    3   |     1     |
|    4   |     0     |
|    5   |     4     |
+--------+-----------+

Answer 1

SELECT parent_id parent, count( * ) num_children
FROM example
GROUP BY parent_id
HAVING parent_id
IN (

SELECT id
FROM `example`
WHERE parent_id =0
)

Answer 2

像这样简单的事情应该有效：

SELECT parent_id, count( * ) cnt
FROM example
WHERE parent_id
IN (
   SELECT id
   FROM `example`
   WHERE parent_id =0
)
GROUP BY parent_id

Answer 3

感谢 Dave Morris 和 Tomgrohl 提供的答案，我得以让它发挥作用。这是我使用的MySQL。

SELECT parent_id parent, count( * ) num_children
FROM example
GROUP BY parent_id
HAVING parent_id <> 0
IN (
    SELECT id
    FROM `example`
    WHERE parent_id = 0
);

Answer 4

您必须在列前添加表别名

SELECT id, (SELECT COUNT(inner.id) FROM example inside where inner.parent_id =outer.id) FROM exampleouter WHEREparent_id = 0;