SQL查询只选择孙子孙 [英] SQL query to select only grandchildren

问题描述

我对SQL还是很陌生，试图绕过它，但是有点令人困惑.这是我正在使用的东西的简化版本.

I'm pretty new to SQL, trying to wrap my head around it, but it's getting a little confusing. Here's a simplified version of what I'm working with.

我有这张叫people的表:

+----+------------+-----------+
| id | name       | parent_id |
+----+------------+-----------+
|  1 | Bob        | 3         |
|  2 | John       | 5         |
|  3 | Larry      | 4         |
|  4 | Kevin      | 0         |
|  5 | Kyle       | 0         |
|  6 | Jason      | 5         |
|  7 | Mildred    | 4         |
|  8 | Mabel      | 6         |
+----+------------+-----------+

上表是人员列表.他们的parent_id列指的是他们的父母.如果他们在表上没有父母，则其parent_id为0.

The above table is a list of people. Their parent_id column refers to who their parents are. If they have no parents on the table, their parent_id is 0.

现在，我希望能够获得每组人的单独列表:祖父母，子女和孙子.

Now, I want to be able to get separate lists of each group of people: grandparents, children, and grandchildren.

很容易得到祖父母(拉里和凯文)，我可以执行以下查询:

It's easy to get grandparents (Larry and Kevin), I can just do this query:

SELECT name FROM people WHERE parent_id = 0

但是当涉及到生子(约翰，拉里，杰森和米尔德雷德)和孙子(鲍勃和梅贝尔)时，我迷路了.

But when it comes to getting children (John, Larry, Jason, and Mildred) and grandchildren (Bob and Mabel), I'm lost.

用英语，这是获取子代的过程:从表中获取所有结果.对于每个子代，请查看其父ID.在具有该ID作为其ID的表中查找结果.请参见该人的 ID，如果为0，则原始人是孩子.将其添加到我们要显示的内容列表中."

In English, this would be the process of getting the children: "Get all the results from the table. For each one, look at their parent id. Find the result in the table that has that as their id. See that person's id, and if it's 0, the original person was a child. Add them to the list of what we will display."

对于孙子代来说，它与上述相同，只是增加了一个步骤.

For the grandchildren, it would be the same as above, but just with an additional step.

这有意义吗?如何在上面将我的过程编写为SQL查询?

Does that make sense? How can I write my process above as a SQL query?

推荐答案

可以使用简单的JOIN来解决.

This can be solved using a simple JOIN.

要选择子代列表，请执行以下操作:

To select the list of children:

SELECT c.name
FROM people p
JOIN people c ON c.parent_id = p.id
WHERE p.parent_id = 0

要选择孙子列表，请执行以下操作:

To select the list of grandchildren:

SELECT gc.name
FROM people p
JOIN people c ON c.parent_id = p.id
JOIN people gc ON gc.parent_id = c.id
WHERE p.parent_id = 0

这篇关于SQL查询只选择孙子孙的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！