mysql - 是否可以将这些复杂的 MYSQL 查询组合起来？

Question

我有两个查询，都提供 inventory.id、inventory.title 以及订单所需的金额。

我想做的是将它们组合起来，以便将 amount_needed_for_orders 列的结果相加。

这里是对正在发生的事情的快速总结。我正在开发一个面包店库存管理程序。面包师有库存、面团配方和作为面团配方的一部分的产品。

这就是事情变得复杂的地方/头发拉扯开始了。

每个食谱都有一个成分列表和产量。***有些食谱中含有另一种食谱作为成分。这种面团中的面团被称为“preation”，也称为“poolish”。

第一个查询提供包含优惠的任何订单所需的库存量，第二个查询提供当前库存水平以及订单所需的库存量(不包括任何优惠成分)。

由于它很复杂，所以我正在边构建边构建它。下一步是以某种方式组合这两个查询。

以下是所有订单所需的库存量(不包括偏好):

SELECT SUM(dough_recipe_ingredient_pounds / dough_recipe_yield_pounds * weight *      
quantity_to_be_baked) as amount_needed_for_orders,id,title,inventory_pounds
        FROM (
            SELECT 
            inventory.id, 
            inventory.title, 
            products.weight, 
            products.id AS product_id, 
            (orders_items.quantity - orders_items.quantity_baked) AS 
quantity_to_be_baked,
                CASE inventory.units
                    WHEN  'kilograms'
                    THEN 2.20462 * inventory.quantity
                    WHEN  'pounds'
                    THEN 1 * inventory.quantity
                    WHEN  'ounces'
                    THEN 0.0625 * inventory.quantity
                    WHEN  'grams'
                    THEN 0.00220462 * inventory.quantity
                END as inventory_pounds,
                CASE dough_recipes.units
                    WHEN 'kilograms'
                    THEN 2.20462 * dough_recipes.amount
                    WHEN 'pounds'
                    THEN 1 * dough_recipes.amount
                    WHEN 'ounces'
                    THEN 0.0625 * dough_recipes.amount
                    WHEN 'grams'
                    THEN 0.00220462 * dough_recipes.amount
                END  AS dough_recipe_ingredient_pounds, 
            (orders_items.quantity - orders_items.quantity_baked) AS num_loaves_needed,
                CASE doughs.units
                    WHEN 'kilograms'
                    THEN 2.20462 * doughs.yield
                    WHEN 'pounds'
                    THEN 1 * doughs.yield
                    WHEN 'ounces'
                    THEN 0.0625 * doughs.yield
                    WHEN 'grams'
                    THEN 0.00220462 * doughs.yield
                END AS dough_recipe_yield_pounds
            FROM inventory
            LEFT JOIN dough_recipes ON inventory.id = dough_recipes.inventory_id
            LEFT JOIN products ON dough_recipes.dough_id = products.dough_id
            LEFT JOIN orders_items ON products.id = orders_items.product_id AND 
(orders_items.quantity - orders_items.quantity_baked) > 0
            LEFT JOIN doughs ON doughs.id = products.dough_id
        ) sq
        GROUP BY id

这是所有订单首选项所需的库存量。

SELECT id,title, SUM(pre_ferment_recipe_ingredient_pounds / 
pre_ferment_recipe_yield_pounds * weight * quantity_to_be_prepared) as 
amount_needed_for_orders
        FROM (SELECT inventory.id,
                 inventory.title,
                 dough_pre_ferments.amount,
                 dough_pre_ferments.unit,
                 products.weight,
                 (order_preferments.quantity-order_preferments.quantity_prepared) as 
quantity_to_be_prepared,
          CASE pre_ferment_recipes.units
                WHEN 'kilograms'
                THEN 2.20462 * pre_ferment_recipes.amount
                WHEN 'pounds'
                THEN 1 * pre_ferment_recipes.amount
                WHEN 'ounces'
                THEN 0.0625 * pre_ferment_recipes.amount
                WHEN 'grams'
                THEN 0.00220462 * pre_ferment_recipes.amount
          END  AS pre_ferment_recipe_ingredient_pounds,

          CASE pre_ferments.units
                    WHEN 'kilograms'
                    THEN 2.20462 * pre_ferments.yield
                    WHEN 'pounds'
                    THEN 1 * pre_ferments.yield
                    WHEN 'ounces'
                    THEN 0.0625 * pre_ferments.yield
                    WHEN 'grams'
                    THEN 0.00220462 * pre_ferments.yield
          END AS pre_ferment_recipe_yield_pounds

            FROM inventory
            LEFT JOIN pre_ferment_recipes ON pre_ferment_recipes.inventory_id = 
inventory.id
            LEFT JOIN dough_pre_ferments ON dough_pre_ferments.pre_ferment_id = 
pre_ferment_recipes.pre_ferment_id
            LEFT JOIN products ON products.dough_id = dough_pre_ferments.dough_id
            LEFT JOIN pre_ferments ON pre_ferments.id = 
pre_ferment_recipes.pre_ferment_id
            LEFT JOIN order_preferments ON order_preferments.preferment_id = 
pre_ferment_recipes.pre_ferment_id
            ) sq
            GROUP BY id

Answer 1

这是您问题的有效解决方案。

SELECT id, title, SUM(amount_needed_for_orders) 
FROM(
(SELECT id,title, SUM(dough_recipe_ingredient_pounds / dough_recipe_yield_pounds * weight *      
quantity_to_be_baked) as amount_needed_for_orders
        FROM (
            SELECT 
            inventory.id, 
            inventory.title, 
            products.weight, 
            products.id AS product_id, 
            (orders_items.quantity - orders_items.quantity_baked) AS 
quantity_to_be_baked,
                CASE inventory.units
                    WHEN  'kilograms'
                    THEN 2.20462 * inventory.quantity
                    WHEN  'pounds'
                    THEN 1 * inventory.quantity
                    WHEN  'ounces'
                    THEN 0.0625 * inventory.quantity
                    WHEN  'grams'
                    THEN 0.00220462 * inventory.quantity
                END as inventory_pounds,
                CASE dough_recipes.units
                    WHEN 'kilograms'
                    THEN 2.20462 * dough_recipes.amount
                    WHEN 'pounds'
                    THEN 1 * dough_recipes.amount
                    WHEN 'ounces'
                    THEN 0.0625 * dough_recipes.amount
                    WHEN 'grams'
                    THEN 0.00220462 * dough_recipes.amount
                END  AS dough_recipe_ingredient_pounds, 
            (orders_items.quantity - orders_items.quantity_baked) AS num_loaves_needed,
                CASE doughs.units
                    WHEN 'kilograms'
                    THEN 2.20462 * doughs.yield
                    WHEN 'pounds'
                    THEN 1 * doughs.yield
                    WHEN 'ounces'
                    THEN 0.0625 * doughs.yield
                    WHEN 'grams'
                    THEN 0.00220462 * doughs.yield
                END AS dough_recipe_yield_pounds
            FROM inventory
            LEFT JOIN dough_recipes ON inventory.id = dough_recipes.inventory_id
            LEFT JOIN products ON dough_recipes.dough_id = products.dough_id
            LEFT JOIN orders_items ON products.id = orders_items.product_id AND 
(orders_items.quantity - orders_items.quantity_baked) > 0
            LEFT JOIN doughs ON doughs.id = products.dough_id
        ) sq
        GROUP BY id
) 
UNION ALL

(SELECT id,title, SUM(pre_ferment_recipe_ingredient_pounds / 
pre_ferment_recipe_yield_pounds * weight * quantity_to_be_prepared) as 
amount_needed_for_orders
        FROM (SELECT inventory.id,
                 inventory.title,
                 dough_pre_ferments.amount,
                 dough_pre_ferments.unit,
                 products.weight,
                 (order_preferments.quantity-order_preferments.quantity_prepared) as 
quantity_to_be_prepared,
          CASE pre_ferment_recipes.units
                WHEN 'kilograms'
                THEN 2.20462 * pre_ferment_recipes.amount
                WHEN 'pounds'
                THEN 1 * pre_ferment_recipes.amount
                WHEN 'ounces'
                THEN 0.0625 * pre_ferment_recipes.amount
                WHEN 'grams'
                THEN 0.00220462 * pre_ferment_recipes.amount
          END  AS pre_ferment_recipe_ingredient_pounds,

          CASE pre_ferments.units
                    WHEN 'kilograms'
                    THEN 2.20462 * pre_ferments.yield
                    WHEN 'pounds'
                    THEN 1 * pre_ferments.yield
                    WHEN 'ounces'
                    THEN 0.0625 * pre_ferments.yield
                    WHEN 'grams'
                    THEN 0.00220462 * pre_ferments.yield
          END AS pre_ferment_recipe_yield_pounds

            FROM inventory
            LEFT JOIN pre_ferment_recipes ON pre_ferment_recipes.inventory_id = 
inventory.id
            LEFT JOIN dough_pre_ferments ON dough_pre_ferments.pre_ferment_id = 
pre_ferment_recipes.pre_ferment_id
            LEFT JOIN products ON products.dough_id = dough_pre_ferments.dough_id
            LEFT JOIN pre_ferments ON pre_ferments.id = 
pre_ferment_recipes.pre_ferment_id
            LEFT JOIN order_preferments ON order_preferments.preferment_id = 
pre_ferment_recipes.pre_ferment_id
            ) sq
            GROUP BY id
)
) as t
GROUP BY id

<小时/>

编辑:

这是一个带有连接的可行解决方案。

SELECT 
    t.id, t.title, t.inventory_pounds, 
    t.amount_needed_for_orders + t1.amount_needed_for_orders
FROM(
    SELECT 
        id,title, inventory_pounds,
        SUM(dough_recipe_ingredient_pounds / dough_recipe_yield_pounds * weight * quantity_to_be_baked) AS amount_needed_for_orders
        FROM (
            SELECT 
            inventory.id, 
            inventory.title, 
            products.weight, 
            products.id AS product_id, 
            (orders_items.quantity - orders_items.quantity_baked) AS quantity_to_be_baked,
                CASE inventory.units
                    WHEN  'kilograms'
                    THEN 2.20462 * inventory.quantity
                    WHEN  'pounds'
                    THEN 1 * inventory.quantity
                    WHEN  'ounces'
                    THEN 0.0625 * inventory.quantity
                    WHEN  'grams'
                    THEN 0.00220462 * inventory.quantity
                END AS inventory_pounds,
                CASE dough_recipes.units
                    WHEN 'kilograms'
                    THEN 2.20462 * dough_recipes.amount
                    WHEN 'pounds'
                    THEN 1 * dough_recipes.amount
                    WHEN 'ounces'
                    THEN 0.0625 * dough_recipes.amount
                    WHEN 'grams'
                    THEN 0.00220462 * dough_recipes.amount
                END  AS dough_recipe_ingredient_pounds, 
            (orders_items.quantity - orders_items.quantity_baked) AS num_loaves_needed,
                CASE doughs.units
                    WHEN 'kilograms'
                    THEN 2.20462 * doughs.yield
                    WHEN 'pounds'
                    THEN 1 * doughs.yield
                    WHEN 'ounces'
                    THEN 0.0625 * doughs.yield
                    WHEN 'grams'
                    THEN 0.00220462 * doughs.yield
                END AS dough_recipe_yield_pounds
            FROM inventory
            LEFT JOIN dough_recipes ON inventory.id = dough_recipes.inventory_id
            LEFT JOIN products ON dough_recipes.dough_id = products.dough_id
            LEFT JOIN orders_items ON products.id = orders_items.product_id AND 
(orders_items.quantity - orders_items.quantity_baked) > 0
            LEFT JOIN doughs ON doughs.id = products.dough_id
        ) sq
        GROUP BY id
) AS t
JOIN
(   SELECT 
        id,title, 
        SUM(pre_ferment_recipe_ingredient_pounds / pre_ferment_recipe_yield_pounds * weight * quantity_to_be_prepared) AS amount_needed_for_orders
    FROM (SELECT inventory.id,
                 inventory.title,
                 dough_pre_ferments.amount,
                 dough_pre_ferments.unit,
                 products.weight,
                 (order_preferments.quantity-order_preferments.quantity_prepared) AS 
quantity_to_be_prepared,
          CASE pre_ferment_recipes.units
                WHEN 'kilograms'
                THEN 2.20462 * pre_ferment_recipes.amount
                WHEN 'pounds'
                THEN 1 * pre_ferment_recipes.amount
                WHEN 'ounces'
                THEN 0.0625 * pre_ferment_recipes.amount
                WHEN 'grams'
                THEN 0.00220462 * pre_ferment_recipes.amount
          END  AS pre_ferment_recipe_ingredient_pounds,

          CASE pre_ferments.units
                    WHEN 'kilograms'
                    THEN 2.20462 * pre_ferments.yield
                    WHEN 'pounds'
                    THEN 1 * pre_ferments.yield
                    WHEN 'ounces'
                    THEN 0.0625 * pre_ferments.yield
                    WHEN 'grams'
                    THEN 0.00220462 * pre_ferments.yield
          END AS pre_ferment_recipe_yield_pounds

            FROM inventory
            LEFT JOIN pre_ferment_recipes ON pre_ferment_recipes.inventory_id = inventory.id
            LEFT JOIN dough_pre_ferments ON dough_pre_ferments.pre_ferment_id = pre_ferment_recipes.pre_ferment_id
            LEFT JOIN products ON products.dough_id = dough_pre_ferments.dough_id
            LEFT JOIN pre_ferments ON pre_ferments.id = pre_ferment_recipes.pre_ferment_id
            LEFT JOIN order_preferments ON order_preferments.preferment_id = pre_ferment_recipes.pre_ferment_id
            ) sq
            GROUP BY id
) AS t1 ON t.id = t1.id
GROUP BY id