英文:
How to count referenced items in same table in MySQL?
问题
更新了新的表定义
我可能太累了,无法理解如何实现以下目标。我有一个表,其中项目在同一表中相互引用。我想要计算每个项目有多少引用。
为了简化,我定义了一个人员表,其中父亲是从他们的子女那里链接的。创建表的代码如下:
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
使用自连接:
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
得分: 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;
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论