关于SQLite3分类树结构递归查询的技术求助

我完全理解刚接触递归查询时的困惑，尤其是用来处理这种层级分类结构的时候。别担心，咱们一步步来，结合你给出的分类数据，用Python和SQLite3搞定这个需求。

首先要注意：SQLite从**3.38.0版本（2022年2月发布）**开始支持WITH RECURSIVE语法（递归公共表表达式），这是实现树形查询的核心。你可以先在Python里检查你的SQLite版本：

import sqlite3
print(sqlite3.sqlite_version)

如果版本低于3.38.0，建议更新你的SQLite或者Python环境（新版Python自带的SQLite通常满足要求）。

第一步：创建示例表并插入数据 #

先把你给出的分类数据转换成SQLite的表结构和插入语句，咱们先把数据准备好：

import sqlite3

# 连接数据库（本地文件，不存在则创建）
conn = sqlite3.connect('categories.db')
cursor = conn.cursor()

# 创建分类表
cursor.execute('''
CREATE TABLE IF NOT EXISTS categories (
    code TEXT PRIMARY KEY,
    label TEXT NOT NULL,
    level INTEGER,
    parent_code TEXT,
    FOREIGN KEY (parent_code) REFERENCES categories(code)
)
''')

# 插入你提供的示例数据
sample_data = [
    ('auto-moto', 'Auto - Moto', 1, None),
    ('autokinito', 'Car', 2, 'auto-moto'),
    ('car-aksesouar', 'Car accessories', 3, 'autokinito'),
    ('diakosmitika-aytokinitou', 'Car Decorations', 4, 'car-aksesouar'),
    ('katharistika-aytokinitou', 'Car Cleaners', 4, 'car-aksesouar'),
    ('keraies autokinhtou', 'Car Antennas', 4, 'car-aksesouar'),
    ('car-analosima', 'Car Reusable...', 1, None)  # 把它当作一级分类处理
]

cursor.executemany('INSERT OR IGNORE INTO categories VALUES (?, ?, ?, ?)', sample_data)
conn.commit()

这里我把car-analosima设为了一级分类（level=1，parent_code=NULL），因为它没有提供父节点和层级，符合根节点的特征。

第二步：递归查询的核心语法（WITH RECURSIVE） #

递归CTE的结构分为两部分：

1. 锚点成员：定义递归的起始点（比如所有根节点）
2. 递归成员：定义如何从当前节点找到子节点，并且和锚点/之前的递归结果合并，直到没有新的子节点为止

示例1：查询完整分类树，包含层级路径 #

这个查询会返回每个节点的完整路径（从根到当前节点），方便直观看到层级关系：

# 执行递归查询
cursor.execute('''
WITH RECURSIVE category_tree AS (
    -- 锚点：所有根节点（parent_code为NULL的节点）
    SELECT 
        code, 
        label, 
        level, 
        parent_code,
        label AS path  -- 根节点的路径就是自己的名称
    FROM categories
    WHERE parent_code IS NULL
    
    UNION ALL
    
    -- 递归成员：找到子节点，拼接路径
    SELECT 
        c.code, 
        c.label, 
        c.level, 
        c.parent_code,
        ct.path || ' > ' || c.label AS path
    FROM categories c
    JOIN category_tree ct ON c.parent_code = ct.code
)
SELECT * FROM category_tree ORDER BY level, code;
''')

# 获取并打印结果
results = cursor.fetchall()
print("完整分类树（含路径）：")
for row in results:
    print(f"层级{row[2]} | 路径: {row[4]} | 编码: {row[0]}")

执行后你会看到类似这样的输出：

层级1 | 路径: Auto - Moto | 编码: auto-moto
层级1 | 路径: Car Reusable... | 编码: car-analosima
层级2 | 路径: Auto - Moto > Car | 编码: autokinito
层级3 | 路径: Auto - Moto > Car > Car accessories | 编码: car-aksesouar
层级4 | 路径: Auto - Moto > Car > Car accessories > Car Decorations | 编码: diakosmitika-aytokinitou
层级4 | 路径: Auto - Moto > Car > Car accessories > Car Cleaners | 编码: katharistika-aytokinitou
层级4 | 路径: Auto - Moto > Car > Car accessories > Car Antennas | 编码: keraies autokinhtou

示例2：查询某个父节点下的所有后代节点 #

比如要查询auto-moto下的所有子分类（包括多级子节点）：

target_parent_code = 'auto-moto'

cursor.execute('''
WITH RECURSIVE sub_categories AS (
    -- 锚点：目标父节点本身
    SELECT code, label, level, parent_code
    FROM categories
    WHERE code = ?
    
    UNION ALL
    
    -- 递归成员：找到所有子节点
    SELECT c.code, c.label, c.level, c.parent_code
    FROM categories c
    JOIN sub_categories sc ON c.parent_code = sc.code
)
SELECT * FROM sub_categories ORDER BY level;
''', (target_parent_code,))

results = cursor.fetchall()
print(f"\n{target_parent_code}下的所有后代：")
for row in results:
    print(f"层级{row[2]} | 名称: {row[1]} | 编码: {row[0]}")

第三步：把查询结果转换成Python树形结构 #

如果需要把扁平的查询结果转换成嵌套的字典（方便后续业务逻辑使用），可以这样处理：

def build_category_tree(results):
    # 先把所有节点按code存起来
    node_map = {row[0]: {'label': row[1], 'level': row[2], 'children': []} for row in results}
    
    # 遍历节点，把子节点添加到父节点的children里
    for row in results:
        code = row[0]
        parent_code = row[3]
        if parent_code and parent_code in node_map:
            node_map[parent_code]['children'].append(node_map[code])
    
    # 根节点是parent_code为NULL的节点
    root_nodes = [node for code, node in node_map.items() if any(row[0] == code and row[3] is None for row in results)]
    return root_nodes

# 先获取所有节点的结果
cursor.execute('SELECT code, label, level, parent_code FROM categories ORDER BY level')
all_nodes = cursor.fetchall()

# 构建树形结构
category_tree = build_category_tree(all_nodes)

# 打印树形结构（递归打印）
def print_tree(nodes, indent=0):
    for node in nodes:
        # 找到当前节点对应的code
        node_code = next(k for k, v in node_map.items() if v == node)
        print('  ' * indent + f"- {node['label']} (code: {node_code})")
        print_tree(node['children'], indent + 1)

print("\nPython嵌套树形结构：")
print_tree(category_tree)

执行后会输出结构化的树形：

Python嵌套树形结构：
- Auto - Moto (code: auto-moto)
  - Car (code: autokinito)
    - Car accessories (code: car-aksesouar)
      - Car Decorations (code: diakosmitika-aytokinitou)
      - Car Cleaners (code: katharistika-aytokinitou)
      - Car Antennas (code: keraies autokinhtou)
- Car Reusable... (code: car-analosima)

关键知识点总结 #

• WITH RECURSIVE是SQLite实现递归查询的核心，必须确保版本支持
• 锚点成员是递归的起点，通常是根节点或者特定的起始节点
• 递归成员通过JOIN关联之前的结果集，不断获取子节点，直到没有新数据返回
• 可以通过拼接路径、排序层级来让结果更直观
• 扁平结果转树形结构时，用字典映射节点，再通过父节点关联子节点是高效的方式

最后别忘了关闭数据库连接：

conn.close()

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