MySQL 上下级 SQL 查询技巧（mysql 上下级sql）

MySQL 上下级 SQL 查询技巧

在开发Web应用程序时，经常需要从数据库中获取层次结构数据，如商品分类数据，组织架构数据以及菜单等等。MySQL数据库提供了一些SQL语句来查询层次结构数据，本文将介绍一些常用的SQL语句和技巧。

1. 查询子节点数据

查询一个父节点的所有子节点数据，可以使用如下的SQL语句：

SELECT * FROM table_name
WHERE parent_id = '父节点的ID';

这条SQL语句将返回一个结果集，其中包含了所有该父节点的子节点的数据。

2. 查询所有子节点数据

如果需要查询一个父节点的所有子节点及其子节点的所有数据，可以使用如下的SQL语句：

SELECT *
FROM table_name
WHERE FIND_IN_SET(parent_id, @pv := '父节点的ID')
AND LENGTH(@pv := CONCAT(@pv, ',', id));

这条SQL语句使用了MySQL的 FIND_IN_SET 和 CONCAT 函数来实现。@pv 变量用于保存每个子节点的 ID，从而实现递归查询。

3. 查询所有父节点数据

查询一个节点的所有父节点数据，可以使用如下的SQL语句：

SELECT node.*, parent.name AS p_name, parent.id AS p_id
FROM table_name AS node
JOIN table_name AS parent
ON node.parent_id = parent.id
WHERE node.id = '节点的ID';

这条SQL语句使用了MySQL的关联查询（JOIN）来实现。同时，它还将所有的父节点的名称和ID返回。

4. 查询所有祖先节点数据

查询一个节点的所有祖先节点数据，可以使用如下的SQL语句：

SET @id = '节点的ID';
SELECT node.*, parent.name AS p_name, parent.id AS p_id
FROM table_name AS node,
     table_name AS parent
WHERE node.lft BETWEEN parent.lft AND parent.rgt
AND node.id = @id
ORDER BY node.lft;

这条SQL语句使用了 MySQL 的 SET 和 WHERE 子句来实现。在这条语句中，@id 变量用于保存当前节点的ID，lft 和 rgt 则是左右值数据，它们用于表征树形层次结构。使用 ORDER BY 子句将查询结果按照左值进行排序。

5. 查询所有孩子节点数目

查询一个节点的孩子节点数目，可以使用如下的SQL语句：

SET @id = '节点的ID';
SELECT COUNT(*) - 1
FROM table_name
WHERE lft BETWEEN (SELECT lft FROM table_name WHERE id = @id) AND
                  (SELECT rgt FROM table_name WHERE id = @id);

这条SQL语句使用了MySQL的 COUNT 函数和左右值数据来计算孩子节点数目。其中 -1 的原因是为了去除当前节点。

总结

MySQL提供了多种查询层次结构数据的SQL技巧，我们可以根据自己的需要选择不同的方法来实现。同时，我们也需要注意到这些查询语句的性能，尽可能地优化它们的执行效率。