MySQL - 如何在两个 hasMany 关联的查询中获取 hasMany 关联的不同行的总和？

Question

背景:

我有三个表，即生产、生产线和操作。

生产线引用其先前的生产线，列名为“parent_id”。生产线的深度应等于操作的深度。

以下是他们的关系:

Production_lines has many (child) production_lines.
Production_lines belongs to Production.
Productions has many operations.

问题:

我必须创建一个选择查询，该查询使用操作数和总数量来进行选择。问题在于，由于操作表的连接，子生产线是重复的。如何才能区分出 child 生产线的数量总和？

当前解决方案:

SELECT 
    `ProductionLine`.`id`,
    `ProductionLine`.`operation_number`,
    COUNT(DISTINCT `Production.StockCode.Operations`.id) AS 'numberOfOperations',
    # This is where the problem is: It sums up including the duplicated entries due to Operations table.
    SUM(ChildrenProductionLines.dozen_quantity * 12 + ChildrenProductionLines.piece_quantity) AS 'quantityOut'
FROM 
    `production_lines` AS `ProductionLine`
LEFT OUTER JOIN `productions` AS `Production` 
    ON `ProductionLine`.`production_id` = `Production`.`id`
LEFT OUTER JOIN `operations` AS `Production.Operations` 
    ON `Production.Operations`.`production_id` = `Production`.`id`
LEFT OUTER JOIN `production_lines` AS `ChildrenProductionLines` 
    ON `ProductionLine`.`id` = `ChildrenProductionLines`.`parent_id`
GROUP BY `ProductionLine`.`id`
HAVING `operation_number` < `numberOfOperations`
ORDER BY `id`;

Answer 1

尝试这样的事情:

SELECT `id`, `operation_number`, `numberOfOperations`,
       SUM( `dozen_quantity` * 12 + `piece_quantity`) AS `quantityOut`
FROM `production_lines`
LEFT JOIN ( SELECT `P`.`id`, COUNT( `C`.`id`) AS  `numberOfOperations`
            FROM `productions` AS `P`
            LEFT JOIN `operations` AS `O` ON `O`.`production_id` = `P`.`id`
            LEFT JOIN `production_lines` AS `C` ON `P`.`id` = `C`.`parent_id`
            GROUP BY `P`.`id`
          ) AS `Q` ON `production_id` = `Q`.`id`
WHERE `operation_number` < `numberOfOperations`
ORDER BY `id`;