如何在MySQL中计算同一表中的引用项数?

huangapple go评论104阅读模式
英文:

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;

这会输出以下表格:

如何在MySQL中计算同一表中的引用项数?

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

如何在MySQL中计算同一表中的引用项数?

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

英文:

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:

如何在MySQL中计算同一表中的引用项数?

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:

如何在MySQL中计算同一表中的引用项数?

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 如何在MySQL中计算同一表中的引用项数?

答案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;

huangapple
  • 本文由 发表于 2023年8月10日 23:27:07
  • 转载请务必保留本文链接:https://go.coder-hub.com/76877201.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定