SQL SELECT查询如何替换重复服务记录的费用列实现准确计费

问题核心原因 #

当前查询通过completed_work_order关联used_parts表时，单条已完成服务记录如果关联N条零配件使用记录，会生成N行结果，每行都完整携带了服务固定收费字段，直接累加就会导致服务费用被重复计算N次。

实现逻辑 #

• 用窗口函数ROW_NUMBER()给同一项已完成服务下的所有零配件条目做编号，仅编号为1的首条记录保留原服务费用，其余重复行的服务费用置为0.00
• 基于调整后的服务费用计算每行的账单小计，避免重复计费
• 用CTE（公共表达式）存储计算完成的明细结果，再通过UNION ALL拼接末尾的总合计行

修正后SQL代码 #

WITH bill_detail AS (
    SELECT 
        s.name AS Service,
        -- 同一项已完成服务仅第一条零配件记录显示服务费，其余行置0
        CASE 
            WHEN ROW_NUMBER() OVER (
                PARTITION BY cwo.completed_work_order_ID 
                ORDER BY p.part_ID
            ) = 1 THEN ms.cost
            ELSE 0.00
        END AS `Cost of Service`,
        p.name AS `Part Used`,
        up.quantity AS Quantity,
        up.price_sold AS `Cost of Part`,
        -- 每行小计 = 零件费用 + 调整后服务费
        (up.quantity * up.price_sold) + CASE 
            WHEN ROW_NUMBER() OVER (
                PARTITION BY cwo.completed_work_order_ID 
                ORDER BY p.part_ID
            ) = 1 THEN ms.cost
            ELSE 0.00
        END AS `Total Bill`
    FROM work_order
        JOIN client ON client.client_ID = work_order.client_ID
        JOIN vehicle v ON v.vehicle_ID = work_order.vehicle_ID
        JOIN model m ON m.model_ID = v.model_ID
        JOIN completed_work_order cwo ON work_order.work_order_ID = cwo.work_order_ID
        JOIN service s ON s.service_ID = cwo.service_ID
        JOIN model_services ms ON m.model_ID = ms.model_ID AND s.service_ID = ms.service_ID
        JOIN used_parts up ON cwo.completed_work_order_ID = up.completed_work_order_ID
        JOIN part p ON p.part_ID = up.part_ID
    WHERE client.client_ID = 6
)
-- 查询明细
SELECT * FROM bill_detail
UNION ALL
-- 拼接末尾合计行
SELECT 
    'TOTAL' AS Service,
    SUM(`Cost of Service`) AS `Cost of Service`,
    '' AS `Part Used`,
    NULL AS Quantity,
    SUM(`Cost of Part` * Quantity) AS `Cost of Part`,
    SUM(`Total Bill`) AS `Total Bill`
FROM bill_detail;

注意事项 #

• 窗口函数分区字段用cwo.completed_work_order_ID而非服务名称/服务ID，避免同一工单下多次做同一项服务时服务费被错误合并
• 合计行的零配件、数量字段留空/置空，和明细行做区分，符合账单展示习惯
• 如果使用的数据库版本低于8.0、不支持窗口函数，可以改用子查询统计每个已完成服务下最小的零件ID，匹配到的首条记录保留服务费，其余置0即可。

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