SQL递归循环实现产品结构/BOM生成技术问询

我来帮你搞定这个递归生成产品BOM的需求！结合你提到的ps_mstr和pt_mstr两张核心表，我会从数据库层递归查询和应用层递归遍历两个方向给你实用方案，两种方式各有适用场景，你可以根据自己的技术栈和业务需求来选。

一、数据库层递归查询（推荐，性能更优） #

如果你的数据库支持递归CTE（Common Table Expressions）——比如PostgreSQL、MySQL 8+、SQL Server这些主流数据库——直接用SQL就能一次性生成完整的BOM结构，不用在应用层写循环，效率拉满。

示例SQL（适配多数主流数据库） #

WITH RECURSIVE bom_structure AS (
    -- 递归起点：顶层父件（替换成你的目标父件编号，比如'PROD-001'）
    SELECT 
        ps.ps_par AS parent_part,
        ps.ps_comp AS component_part,
        ps.ps_qty_per AS quantity,
        pt.pt_desc AS component_description, -- 从pt_mstr拉取零件描述
        1 AS level -- 标记层级，顶层为1
    FROM ps_mstr ps
    JOIN pt_mstr pt ON ps.ps_comp = pt.pt_part
    WHERE ps.ps_par = 'PROD-001' 
      AND ps.ps_end_date IS NULL -- 严格过滤已终止的组件
    
    UNION ALL
    
    -- 递归部分：向下遍历子组件
    SELECT 
        ps.ps_par AS parent_part,
        ps.ps_comp AS component_part,
        ps.ps_qty_per AS quantity,
        pt.pt_desc AS component_description,
        bs.level + 1 AS level
    FROM ps_mstr ps
    JOIN bom_structure bs ON ps.ps_par = bs.component_part
    JOIN pt_mstr pt ON ps.ps_comp = pt.pt_part
    WHERE ps.ps_end_date IS NULL
)
SELECT * FROM bom_structure
ORDER BY level, parent_part;

实用说明： #

• 递归CTE分两部分：UNION ALL前是锚点查询（顶层父件的直接组件），后面是递归查询（用已生成的BOM节点作为父层，继续关联ps_mstr找子组件）。
• 如果要查询所有顶层产品（没有父件的零件），把锚点查询的WHERE ps.ps_par = 'PROD-001'改成WHERE ps.ps_par NOT IN (SELECT ps_comp FROM ps_mstr)即可。

二、应用层递归遍历（灵活度更高） #

如果你的数据库不支持递归CTE，或者需要在代码里对BOM节点做额外业务处理（比如计算总成本、自定义过滤规则），可以在应用层写递归函数来遍历。

示例Python代码（适配通用数据库连接） #

import psycopg2  # 替换成你用的数据库驱动，比如pymysql、sqlite3

def get_bom_component(parent_part, conn, visited=None, level=1):
    """递归获取父件的所有子组件，自动处理循环依赖"""
    if visited is None:
        visited = set()
    # 防止循环依赖死循环
    if parent_part in visited:
        return []
    
    visited.add(parent_part)
    bom_nodes = []
    
    # 查询当前父件的直接组件
    cursor = conn.cursor()
    query = """
        SELECT ps.ps_comp, ps.ps_qty_per, pt.pt_desc
        FROM ps_mstr ps
        JOIN pt_mstr pt ON ps.ps_comp = pt.pt_part
        WHERE ps.ps_par = %s AND ps.ps_end_date IS NULL
    """
    cursor.execute(query, (parent_part,))
    components = cursor.fetchall()
    
    for comp_part, qty, desc in components:
        node = {
            "parent_part": parent_part,
            "component_part": comp_part,
            "quantity": qty,
            "description": desc,
            "level": level
        }
        bom_nodes.append(node)
        # 递归查询子组件的子组件
        bom_nodes.extend(get_bom_component(comp_part, conn, visited.copy(), level + 1))
    
    cursor.close()
    return bom_nodes

# 用法示例
if __name__ == "__main__":
    # 建立数据库连接
    conn = psycopg2.connect(
        dbname="your_db_name",
        user="your_username",
        password="your_password",
        host="your_host"
    )
    
    # 获取顶层产品的完整BOM
    full_bom = get_bom_component("PROD-001", conn)
    
    # 打印格式化结果
    for node in full_bom:
        indent = "  " * (node["level"] - 1)
        print(f"{indent}Level {node['level']}: {node['parent_part']} -> {node['component_part']} (Qty: {node['quantity']}, Desc: {node['description']})")
    
    conn.close()

实用说明： #

• 我特意加了visited集合来处理循环依赖（比如A包含B，B又包含A的情况），避免递归死循环。
• 如果BOM层级极深（比如超过1000层），递归可能触发栈溢出，这时可以改成迭代式的深度优先/广度优先遍历，用栈或队列来实现。

关键注意事项 #

• 严格过滤终止日期：一定要保留ps_end_date IS NULL的条件，别把已经从BOM中移除的组件拉进来。
• 性能优化：数据量大的话，数据库递归CTE的性能远优于应用层递归——毕竟减少了多次数据库查询的开销。如果用应用层递归，建议加缓存或者批量查询逻辑。
• 扩展字段：如果需要更多零件信息，直接在SQL或代码里关联pt_mstr的其他字段（比如重量、规格）就行。

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