如何在SQL中处理层级依赖列？查询物料123的所有子物料数量

• 别用Python循环，效率太低

  • 循环遍历需要反复从数据库拉取数据，频繁的IO操作会拖慢速度，数据量一大延迟会特别明显。
  • 手动写递归逻辑容易出错，比如层级嵌套过深时漏数据、循环终止条件写不对，维护起来麻烦。

• 优先用SQL递归查询，高效又省心
  现在主流SQL数据库（MySQL 8.0+、PostgreSQL、SQL Server等）都支持递归公共表表达式（CTE），直接在数据库层面完成层级遍历，性能甩Python循环几条街。

  假设你的物料表结构是这样的（示例）：

  CREATE TABLE materials (
      material_id VARCHAR(20) PRIMARY KEY,
      parent_material_id VARCHAR(20),
      quantity INT -- 该子物料在父物料中的组成数量
  );
  

  拿MySQL或PostgreSQL举例，查询物料123所有子物料总数量的SQL语句：

  WITH RECURSIVE sub_materials AS (
      -- 先查物料123的直接子物料
      SELECT material_id, quantity
      FROM materials
      WHERE parent_material_id = '123'
      UNION ALL
      -- 递归遍历子物料的下一级子物料
      SELECT m.material_id, m.quantity
      FROM materials m
      JOIN sub_materials sm ON m.parent_material_id = sm.material_id
  )
  -- 计算所有子物料的总数量
  SELECT SUM(quantity) AS total_sub_quantity
  FROM sub_materials;
  

  简单说明：

  • WITH RECURSIVE 定义递归逻辑，分初始查询（直接子物料）和递归连接（下一级子物料）两部分。
  • 数据库会自动处理所有层级的遍历，直到没有更深的子物料为止，不用自己写循环逻辑。
  • 所有计算在数据库内部完成，避免了Python和数据库之间的多次数据传输，性能优势特别明显。

• 实在要用Python？只能降级备选
  如果你的数据库版本太老（比如MySQL 5.x）不支持递归CTE，可以用Python批量查询减少IO次数，但效率还是不如SQL方案：

  import pandas as pd
  import sqlalchemy
  
  # 替换成你的数据库连接字符串
  engine = sqlalchemy.create_engine('mysql+pymysql://user:password@host/db_name')
  
  def calculate_total_sub_qty(parent_id):
      total = 0
      current_parent_ids = [parent_id]
      while current_parent_ids:
          # 批量查当前层级子物料的总数量
          placeholders = ','.join(['%s'] * len(current_parent_ids))
          sum_query = f"SELECT SUM(quantity) FROM materials WHERE parent_material_id IN ({placeholders})"
          batch_sum = pd.read_sql(sum_query, engine, params=current_parent_ids).iloc[0,0] or 0
          total += batch_sum
  
          # 获取下一层级的父物料ID
          next_ids_query = f"SELECT material_id FROM materials WHERE parent_material_id IN ({placeholders})"
          current_parent_ids = pd.read_sql(next_ids_query, engine, params=current_parent_ids)['material_id'].tolist()
      return total
  
  print(calculate_total_sub_qty('123'))
  

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