嘿，这个需求我太熟了！要把分类的树形结构和对应末级分类的商品整合到一起，用SQL的递归CTE（公共表表达式）就能搞定，咱们一步步来拆解：

首先我得先假设你的两张表字段（如果实际结构有差异，你对应调整字段名就行）：

• 分类表category：

  CREATE TABLE category (
      id INT PRIMARY KEY,
      parent_id INT NULL, -- 父分类ID，顶级分类为NULL或0
      name VARCHAR(100) NOT NULL -- 分类名称
  );
  

• 商品表product：

  CREATE TABLE product (
      id INT PRIMARY KEY,
      category_id INT NOT NULL, -- 所属末级分类ID
      name VARCHAR(200) NOT NULL -- 商品名称
      -- 其他商品字段比如价格、库存之类的按需添加
  );
  

先通过递归查询把所有分类的层级、路径以及是否为末级分类（叶子节点）标记出来，这是核心步骤：

WITH RECURSIVE category_tree AS (
    -- 递归起点：顶级分类
    SELECT 
        id,
        parent_id,
        name AS category_name,
        1 AS level,
        CAST(name AS VARCHAR(1000)) AS path,
        -- 判断是否为末级分类：子查询看有没有子分类
        CASE WHEN NOT EXISTS (SELECT 1 FROM category c2 WHERE c2.parent_id = c1.id) THEN 1 ELSE 0 END AS is_leaf
    FROM category c1
    WHERE parent_id IS NULL -- 或者parent_id = 0，看你顶级分类的定义
    
    UNION ALL
    
    -- 递归部分：关联子分类
    SELECT 
        c2.id,
        c2.parent_id,
        c2.name AS category_name,
        ct.level + 1 AS level,
        CONCAT(ct.path, ' > ', c2.name) AS path,
        CASE WHEN NOT EXISTS (SELECT 1 FROM category c3 WHERE c3.parent_id = c2.id) THEN 1 ELSE 0 END AS is_leaf
    FROM category c2
    JOIN category_tree ct ON c2.parent_id = ct.id
)
SELECT * FROM category_tree;

这个CTE会返回所有分类的层级信息，is_leaf字段标记了哪些是末级分类，后面咱们就靠这个字段来关联商品。

接下来咱们把分类和对应末级分类的商品整合到一起，用缩进的方式体现树形结构：

WITH RECURSIVE category_tree AS (
    SELECT 
        id,
        parent_id,
        name AS category_name,
        1 AS level,
        CAST(name AS VARCHAR(1000)) AS path,
        CASE WHEN NOT EXISTS (SELECT 1 FROM category c2 WHERE c2.parent_id = c1.id) THEN 1 ELSE 0 END AS is_leaf
    FROM category c1
    WHERE parent_id IS NULL
    
    UNION ALL
    
    SELECT 
        c2.id,
        c2.parent_id,
        c2.name AS category_name,
        ct.level + 1 AS level,
        CONCAT(ct.path, ' > ', c2.name) AS path,
        CASE WHEN NOT EXISTS (SELECT 1 FROM category c3 WHERE c3.parent_id = c2.id) THEN 1 ELSE 0 END AS is_leaf
    FROM category c2
    JOIN category_tree ct ON c2.parent_id = ct.id
)
-- 先查所有分类，再查末级分类对应的商品，合并结果
SELECT
    -- 用空格缩进体现层级，分类前加「📂」，商品前加「📦」
    CONCAT(
        REPEAT('  ', level - 1),
        '📂 ',
        category_name
    ) AS tree_item,
    'category' AS type,
    id AS item_id
FROM category_tree

UNION ALL

SELECT
    -- 商品的层级是对应末级分类的层级+1
    CONCAT(
        REPEAT('  ', ct.level),
        '📦 ',
        p.name
    ) AS tree_item,
    'product' AS type,
    p.id AS item_id
FROM category_tree ct
JOIN product p ON ct.id = p.category_id
WHERE ct.is_leaf = 1 -- 只关联末级分类的商品

-- 按路径排序，保证树形结构的顺序
ORDER BY path, type;

比如你的分类是「数码产品」→「手机」→「智能手机」（末级），「智能手机」下有「iPhone 15」「华为Mate 60」，那查询结果会是这样：

📂 数码产品
  📂 手机
    📂 智能手机
      📦 iPhone 15
      📦 华为Mate 60

• 如果你的数据库不支持REPEAT函数（比如SQL Server），可以用SPACE((level-1)*2)来替代缩进；
• 如果需要更复杂的树形展示（比如嵌套JSON结构），可以在查询后用程序语言（比如Python、Java）把结果转换成JSON，或者某些数据库（比如PostgreSQL）支持直接用json_agg生成嵌套JSON；
• 确保product表的category_id都指向末级分类，不然商品会挂在非末级分类下面，不符合需求。

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