MySQL - 仅获取特定 parent 的 child

Question

如果我运行以下查询:

SELECT loc2.* FROM `locations` AS locINNER JOIN `locations` AS loc2 ON loc.location_id = loc2.location_parentWHERE loc.location_status='publish'

我得到以下结果:

+-------------+------------------+-----------------+-----------------+| location_id | location_name    | location_parent | location_status |+-------------+------------------+-----------------+-----------------+|          19 | Dhaka Division   |             564 | publish         ||          22 | Dhaka District   |              19 | publish         ||          26 | Dhaka City       |              22 | publish         ||          28 | Mirpur           |              26 | publish         ||          30 | Mirpur - 12      |              28 | publish         ||          32 | Mirpur DOHS      |              30 | publish         ||         634 | Gazipur District |              19 | publish         |+-------------+------------------+-----------------+-----------------+7 rows in set (0.00 sec)

实际上我正在尝试从数据库中获取所有的子/孙子。现在上面的查询只是按照父子规则对行进行排序并返回所有行。但是，我想向 SQL 添加一个条件，以便它只获取特定父项的子项。

例如，我想获取19 的所有子行/节点/位置。结果集应该如下:

+-------------+------------------+-----------------+-----------------+| location_id | location_name    | location_parent | location_status |+-------------+------------------+-----------------+-----------------+|          22 | Dhaka District   |              19 | publish         ||          26 | Dhaka City       |              22 | publish         ||          28 | Mirpur           |              26 | publish         ||          30 | Mirpur - 12      |              28 | publish         ||          32 | Mirpur DOHS      |              30 | publish         |+-------------+------------------+-----------------+-----------------+

我试过这个:

SELECT loc2.* FROM `locations` AS locINNER JOIN `locations` AS loc2 ON loc.location_id = loc2.location_parentWHERE loc.location_status='publish' AND loc2.location_parent=19

还有这个:

SELECT loc2.* FROM `locations` AS locINNER JOIN `locations` AS loc2 ON loc.location_id = loc2.location_parentWHERE loc.location_status='publish' AND loc.location_parent=19

但它们都返回相同的结果，这是应该的:

+-------------+------------------+-----------------+-----------------+| location_id | location_name    | location_parent | location_status |+-------------+------------------+-----------------+-----------------+|          22 | Dhaka District   |              19 | publish         |+-------------+------------------+-----------------+-----------------+

那么，我应该怎么做才能达到我需要的结果呢？

Answer 1

实际上这是一个称为递归的数据库高级主题，您的要求是从最顶层的父级及其所有子级、孙级等获取数据树。

其他 DBMS 提供了解决此类问题的功能，例如Microsoft SQL Server 将其解决方案称为 CTE(Common Table Express)，但这在 mySQL 中早就应该解决了，并且没有简单的解决方案来解决您的问题。但是，您可以在 mysql 中搜索递归以获取有关此主题的更多详细信息。