MySQL同表查询：如何将父分类slug作为虚拟列返回结果

现有分类数据表，通过cat_parent字段存储父分类ID实现层级关联，查询时需新增虚拟列parent_slug，展示对应父分类的cat_slug值；若分类无父分类（cat_parent为0），则该列返回NULL。要求不修改现有表结构或新增关联表。

表结构及示例数据 #

+-----+------------+------------+------------+
| id  | cat_name   | cat_slug   | cat_parent |
+-----+------------+------------+------------+
| 1   | Cars       | cars       | 0          |
| 2   | Planes     | planes     | 0          |
| 3   | Volvo      | volvo      | 1          |
| 4   | Alfa Romeo | alfa-romeo | 1          |
| 5   | Boeing     | boeing     | 2          |
| 6   | Mitsubishi | mitsubishi | 1          |
| 7   | Mitsubishi | mitsubishi | 2          |
+-----+------------+------------+------------+

核心解决方案 #

使用自左连接（LEFT JOIN）将表与自身关联，通过子分类的cat_parent匹配父分类的id，从而获取父分类的cat_slug作为parent_slug。基础SQL模板如下：

SELECT 
    c.id,
    c.cat_name,
    c.cat_slug,
    c.cat_parent,
    p.cat_slug AS parent_slug
FROM 
    categories c
LEFT JOIN 
    categories p ON c.cat_parent = p.id
WHERE 
    -- 此处添加搜索条件

不同搜索场景示例 #

1. 搜索'volvo' #

SQL语句：

SELECT 
    c.id,
    c.cat_name,
    c.cat_slug,
    c.cat_parent,
    p.cat_slug AS parent_slug
FROM 
    categories c
LEFT JOIN 
    categories p ON c.cat_parent = p.id
WHERE 
    c.cat_slug = 'volvo'

查询结果：

+-----+----------+----------+------------+-------------+
| id  | cat_name | cat_slug | cat_parent | parent_slug |
+-----+----------+----------+------------+-------------+
| 3   | Volvo    | volvo    | 1          | cars        |
+-----+----------+----------+------------+-------------+

2. 搜索'mitsubishi' #

SQL语句：

SELECT 
    c.id,
    c.cat_name,
    c.cat_slug,
    c.cat_parent,
    p.cat_slug AS parent_slug
FROM 
    categories c
LEFT JOIN 
    categories p ON c.cat_parent = p.id
WHERE 
    c.cat_slug = 'mitsubishi'

查询结果：

+-----+------------+------------+------------+-------------+
| id  | cat_name   | cat_slug   | cat_parent | parent_slug |
+-----+------------+------------+------------+-------------+
| 6   | Mitsubishi | mitsubishi | 1          | cars        |
| 7   | Mitsubishi | mitsubishi | 2          | planes      |
+-----+------------+------------+------------+-------------+

3. 搜索包含's'的分类（LIKE '%s%'） #

SQL语句：

SELECT 
    c.id,
    c.cat_name,
    c.cat_slug,
    c.cat_parent,
    p.cat_slug AS parent_slug
FROM 
    categories c
LEFT JOIN 
    categories p ON c.cat_parent = p.id
WHERE 
    c.cat_name LIKE '%s%' OR c.cat_slug LIKE '%s%'

查询结果：

+-----+------------+------------+------------+-------------+
| id  | cat_name   | cat_slug   | cat_parent | parent_slug |
+-----+------------+------------+------------+-------------+
| 1   | Cars       | cars       | 0          | NULL        |
| 2   | Planes     | planes     | 0          | NULL        |
| 6   | Mitsubishi | mitsubishi | 1          | cars        |
| 7   | Mitsubishi | mitsubishi | 2          | planes      |
+-----+------------+------------+------------+-------------+

注：示例中存在两个Mitsubishi分类，分别归属Cars和Planes分类，符合实际业务场景（三菱确实生产飞机）。

内容的提问来源于stack exchange，提问作者Paul