多级物料结构SQL查询需求：计算嵌套物料关联数量

要实现需求，我们可以将查询拆分为两部分，再通过UNION ALL合并结果：

-- 合并两部分结果：22的直接子物料 + 展开20后的子物料
SELECT 
    22 AS main_article,
    sub_article,
    qty
FROM compose_articles
WHERE main_article = 22
  AND sub_article != 20

UNION ALL

SELECT 
    22 AS main_article,
    ac.sub_article,
    ac.qty * multiplier.qty AS qty
FROM compose_articles ac
-- 获取22对应sub_article=20的数量作为乘数
CROSS JOIN (
    SELECT qty 
    FROM compose_articles 
    WHERE main_article = 22 AND sub_article = 20
) AS multiplier
WHERE ac.main_article = 20;

逻辑说明 #

1. 第一部分查询：直接获取main_article=22的子物料，但排除sub_article=20（因为这个子物料需要展开为它自身的子物料），得到结果：

   main_article | sub_article| qty
   -------------+------------+----
   22           |     4      |  12
   22           |    33      |  2
   

2. 第二部分查询：

   • 先通过子查询获取main_article=22且sub_article=20的数量（即10）作为乘数
   • 关联查询main_article=20的所有子物料，将它们的数量乘以这个乘数，得到展开后的结果：

     main_article | sub_article| qty
     -------------+------------+-----
     22           |      4     |  150
     22           |      6     |  140
     

3. 合并结果：用UNION ALL将两部分结果合并，就得到了符合需求的最终数据。

扩展说明 #

如果你的业务场景存在多层嵌套（比如某个子物料本身也是主物料，且需要继续展开），可以使用递归CTE来处理，但当前需求仅需一层展开，上述方案足够简洁高效。

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