如何在Firebird中用递归CTE计算层级工件的聚合成本

需求说明 #

基于给定的两张表：

• WORKS_WITH_BASE_COSTS：存储零件编码和基础成本
• WORKS_STRUCT：存储零件的组装结构（父件、子件、子件数量）

需要创建一个视图，递归计算每个零件的总成本：

• 叶子零件（无下属子件）的总成本为自身基础成本
• 组装零件的总成本为所有子件总成本乘以对应数量的累加和，加上自身基础成本（若有）

表结构与示例数据 #

表结构定义 #

create table WORKS_WITH_BASE_COSTS
(
  CODE      integer not null,
  BASE_COST decfloat default 0 not null,
  constraint PK_WORKS_WITH_BASE_COSTS primary key (CODE)
);

create table WORKS_STRUCT
(
  PARENT_CODE integer not null,
  CHILD_CODE  integer not null,
  QUANTITY    decfloat not null,
  constraint PK_WORKS_STRUCT primary key (PARENT_CODE, CHILD_CODE)
);

示例数据 #

WORKS_WITH_BASE_COSTS

WORKS_STRUCT

递归CTE视图实现 #

CREATE OR ALTER VIEW WORK_PART_TOTAL_COSTS AS
WITH RECURSIVE PART_COSTS (CODE, TOTAL_COST) AS (
    -- 锚点：初始化所有零件的基础成本
    SELECT 
        CODE, 
        BASE_COST AS TOTAL_COST
    FROM WORKS_WITH_BASE_COSTS
    UNION ALL
    -- 递归：计算父件总成本 = 子件总成本×数量之和 + 父件自身基础成本
    SELECT 
        ws.PARENT_CODE,
        SUM(pc.TOTAL_COST * ws.QUANTITY) + COALESCE(wbc.BASE_COST, 0) AS TOTAL_COST
    FROM WORKS_STRUCT ws
    JOIN PART_COSTS pc ON ws.CHILD_CODE = pc.CODE
    LEFT JOIN WORKS_WITH_BASE_COSTS wbc ON ws.PARENT_CODE = wbc.CODE
    GROUP BY ws.PARENT_CODE, wbc.BASE_COST
)
-- 去重并取最终总成本（递归过程中父件会被多次计算，取最大值确保结果正确）
SELECT 
    CODE, 
    MAX(TOTAL_COST) AS TOTAL_COST
FROM PART_COSTS
GROUP BY CODE
ORDER BY CODE;

逻辑说明 #

1. 锚点成员：从基础成本表中读取所有零件的初始成本，叶子零件的总成本直接等于自身基础成本。
2. 递归成员：通过结构表关联父件与子件，累加子件总成本乘以对应数量的结果，再加上父件自身的基础成本，得到父件的当前总成本。
3. 结果处理：由于递归过程中父件会被多次计算（每次子件完成计算后都会更新父件成本），使用MAX(TOTAL_COST)获取最终的累加结果，确保每个零件只保留正确的总成本。

验证结果 #

查询视图WORK_PART_TOTAL_COSTS将得到符合预期的结果：

版本要求 #

该实现依赖Firebird 2.1及以上版本（递归CTE从Firebird 2.1开始支持）。

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