通过child查询出顶级父类
- 假设有如下表结构:
CREATE TABLE category (
id INT PRIMARY KEY,
name VARCHAR(50),
parent_id INT
);
其中,id为分类ID,name为分类名称,parent_id为父分类ID,如果为顶级分类,则parent_id为0。
现在需要查询每个分类的顶级父分类,可以使用如下SQL语句:
SELECT c1.id,
c1.name,
IFNULL(c3.name, IFNULL(c2.name, c1.name)) AS top_parent
FROM category c1
LEFT JOIN category c2
ON c1.parent_id = c2.id
LEFT JOIN category c3
ON c2.parent_id = c3.id
WHERE c1.id = 1;
其中,使用了LEFT JOIN进行表自连接,通过连接三次category表,分别查询当前分类的父分类、父分类的父分类以及父分类的父分类的父分类,如果存在则将其名称作为结果,否则使用当前分类的名称作为结果。IFNULL函数用于处理空值情况。最后,通过WHERE子句指定查询分类ID为1的结果。
MySQL递归
- MySQL中可以使用递归CTE(Common Table Expression)来进行递归查询。下面是一个示例脚本,用来查询给定分类ID的所有子孙分类:
只支持8.X以上版本
WITH RECURSIVE subcategories AS (
SELECT id, name,
parent_id
FROM category
WHERE id = 1 -- 查询分类ID为1的子孙分类
UNION ALL
SELECT c.id,
c.name,
c.parent_id
FROM category c
JOIN subcategories s
ON c.parent_id = s.id
)
SELECT id,
name,
parent_id
FROM subcategories;
这个脚本使用了一个递归CTE来查询给定分类ID的所有子孙分类。首先,定义一个名为subcategories的CTE,该CTE包含一个初始查询,用来查询分类ID为1的分类的信息。然后,使用UNION ALL将该查询结果与一个递归查询的结果合并。递归查询中,查询category表中所有父分类ID等于前一级分类ID的分类信息,并将其与前一级分类信息合并。这样,就可以递归地查询出所有子孙分类的信息。最后,从subcategories中查询出所有分类的ID、名称和父分类ID,并返回结果。
需要注意的是,递归查询可能会导致性能问题,因此应该尽量避免在大型数据集上使用。
- Oracle递归相对来说比较简单,给一个简单示例
SELECT id,
name,
parent_id
FROM category
START WITH EMPLOYEE_ID = 1
CONNECT BY PRIOR parent_id = id