Firebird 3.0服务销售场景查询返回过多冗余行的修复

冗余结果产生的核心根因 #

你查询返回大量冗余、数值错误的核心原因有两个：

• 关联字段错配：错误把出库流水表RecsOut的主键id和库存基础表Inventory的主键id做关联（即ro.id=i.id），两个字段分属不同业务维度，不存在对应匹配关系，会触发跨表笛卡尔积匹配，生成完全无意义的交叉行
• 关联层级混乱：把「基于业务规则计算的理论原料消耗」和「实际产生的出库流水」做无差别强等值关联，没有区分两个逻辑层的匹配关系，一旦出库记录和理论消耗规则无法完全一一对应，就会进一步放大冗余行的数量

正确修复思路 #

要准确输出指定服务单据的原料消耗明细，核心是按照业务流转顺序建立关联关系，避免逻辑跳步：

1. 从目标服务单据出发，逐层关联对应的服务销售记录、服务项目信息、服务-原料配比规则，先算出不依赖出库流水的理论消耗明细
2. 用左关联接入对应单据的原料出库流水、入库成本数据，避免因为出库记录未生成、或者批次匹配问题，导致本该输出的消耗明细被过滤丢失
3. 修正所有跨表关联的字段匹配关系，从根源上消除无意义的笛卡尔积匹配

修正后的可执行查询语句 #

SELECT
  i.id,
  i.name AS UsedRawMaterialName,
  s.name AS ServiceName,
  sr.Doc_id AS ServiceDoc_id,
  ri.cost AS CostofRawMaterial,
  sr.qnt AS ServiceQnt,
  sr.qnt * c.qnt AS UsedRawMaterialQnt,
  i.unit_k
FROM Docs d
-- 关联当前单据下的所有服务销售记录
JOIN ServRecs sr 
  ON sr.Doc_id = d.doc_id
-- 关联服务项目基础信息
JOIN Serv s 
  ON s.Id = sr.serv_id
-- 关联对应服务的原料配比规则
JOIN Calc c 
  ON c.serv_id = sr.serv_id
-- 关联配比涉及的原料库存基础信息
JOIN Inventory i 
  ON i.id = c.RawMat_id
-- 左关联当前单据下对应原料的出库记录，避免无出库时丢明细
LEFT JOIN RecsOut ro 
  ON ro.doc_id = d.doc_id 
  AND ro.good_id = c.RawMat_id
-- 左关联出库记录对应的入库批次，获取原料成本
LEFT JOIN RecsIn ri 
  ON ri.id = ro.RecsIn_id
-- 关联单位维度信息
JOIN unit u 
  ON u.unit_k = i.unit_k
WHERE d.doc_id = 323 -- 替换为目标查询的单据号

长期优化注意点 #

• 所有跨表关联必须严格匹配业务主键对应关系：比如RecsOut.good_id对应Inventory.id、RecsOut.RecsIn_id对应RecsIn.id，禁止将不同业务表的独立主键直接做关联匹配
• 当查询核心目标是输出业务规则计算结果时，对流水类表（出库、入库记录）必须使用左关联，避免因为流水数据缺失导致核心计算结果被过滤
• 坚持使用新式标准JOIN语法：将表间关联判断放在ON子句、全局过滤条件放在WHERE子句，逻辑分层清晰后可以快速定位错配的关联字段，比旧式逗号连接的排查效率高很多，从语法层面减少漏写关联条件、触发笛卡尔积的概率

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