英文:

How to count referenced items in same table in MySQL?

问题 {#heading}

更新了新的表定义

我可能太累了，无法理解如何实现以下目标。我有一个表，其中项目在同一表中相互引用。我想要计算每个项目有多少引用。

为了简化，我定义了一个人员表，其中父亲是从他们的子女那里链接的。创建表的代码如下：

DROP TABLE IF EXISTS persons;

CREATE TABLE persons (
id        INT AUTO_INCREMENT,
isfather  TINYINT,
name      VARCHAR(20),
father_id INT,
PRIMARY KEY (id)
);


INSERT INTO persons (name, isfather, father_id)
VALUES ('John', 1, 0), ('Mike', 1, 0), ('Anne', 0, 1),
('Peter', 0, 1), ('Jane', 0, 2), ('Olivia', 0, 1),
('Max', 0, 2), ('Eric', 1, 0), ('Hugh', 0, 0);

`SELECT * FROM persons;
`

这会输出以下表格：

我想要实现的目标是一个查询，用于计算每个父亲有多少子女。例如，John有3个，Mike有2个，Eric没有子女：

我已经成功创建了类似的查询，但是使用了两个不同的表格（例如，一个父母表格和一个子女表格）。我使用了SELECT DISTINCT(...), COUNT(...) FROM ... INNER JOIN。但我无法理解如何在同一张表上使用JOIN或类似的查询。请注意，子女不能有父母，而父母不一定有子女。我已经努力了很长时间，我的大脑已经累得无法清晰思考。我希望有人可以帮助 英文:

Updated with new table definition

I may be too tired to understand how to achieve the following. I have a table where items refer to each other within the same table. I would like to count how many references each item has.

To simplify I have defined a table of persons where the fathers are linked from their children. The table is created by following:

DROP TABLE IF EXISTS persons;

CREATE TABLE persons (
id        INT AUTO_INCREMENT,
isfather  TINYINT,
name      VARCHAR(20),
father_id INT,
PRIMARY KEY (id));


INSERT INTO persons (name, isfather, father_id)
VALUES (\'John\', 1, 0), (\'Mike\', 1, 0), (\'Anne\', 0, 1),
(\'Peter\', 0, 1), (\'Jane\', 0, 2), (\'Olivia\', 0, 1),
(\'Max\', 0, 2), (\'Eric\', 1, 0), (\'Hugh\', 0, 0);

`SELECT * FROM persons;
`

This outputs the following table:

What I would like to achieve is a query that counts how many children each father has. I.e. John has 3, Mike has 2 and Eric has 0:

I have managed to create similar queries, but with two different tables (e.g. a parents table, and a children table). I used SELECT DISTINCT(...), COUNT(...) FROM ... INNER JOIN. But I cannot understand how to use a query with JOIN or similar on the same table. Note that a child must not have a parent, and a parent doesn't necessarily has a child. I have struggled so long now and my brain is tired to think clearly. I hope somebody can help

答案1 {#1}

得分: 1

使用自连接：

SELECT father.name, COUNT(child.id) AS children
FROM persons AS father
LEFT JOIN persons AS child ON child.father_id = father.id
GROUP BY father.id

如果不需要没有子女的人数为0，请更改为INNER JOIN。 英文:

Use a self-join:

SELECT father.name, COUNT(child.id) AS children
FROM persons AS father
LEFT JOIN persons AS child ON child.father_id = father.id
GROUP BY father.id

Using LEFT JOIN will get 0 counts for people without any children. Change to INNER JOIN if you don't need them.

答案2 {#2}

得分: 0

我更倾向于将其写成一个简单的相关查询：

select name, (
    select Count(*) from persons p2
    where p2.father_id = p.id
) Children
from persons p
where isfather = 1;

英文:

I would prefer to write this as a simple correlation:

select name, (
    select Count(*) from persons p2
	where p2.father_id = p.id
  ) Children
from persons p
where isfather = 1;