MYSQL复杂GROUP BY和SUM查询失败

debugcn 发表于 Dev

零和一

我有一个复杂的查询，它获取当前的库存水平并将单位转换为磅，然后查看未完成的订单并将单位转换为磅，最后显示所有未完成的订单每种成分所需的总金额。

目的是列出具有库存水平的成分，然后列出满足所有未完成订单所需的数量。如果面包师没有足够的面粉，那么他可以看到这一点，并在开始一天的烘烤之前考虑寻找更多的面粉。

问题是，当我运行此查询时，它正在跳过组函数中的第一行。当我不对其进行分组而运行时，它会使所有行正确，但是当我使用SUM和GROUP BY运行它时，它会跳过第一行，因此它会根据计算得出所需的项目数量。有小费吗？谢谢！

SELECT 
    inventory.id, 
    inventory.title, 
    products.weight, 
    products.id AS product_id, 
    (orders_items.quantity - orders_items.quantity_baked) AS quantity_to_be_baked,
    (SELECT @inventory_pounds:=
        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,
    (SELECT @dough_recipe_ingredient_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,
    ( SELECT @dough_recipe_yield_pounds:=
        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,
    (SELECT SUM( @dough_recipe_ingredient_pounds / @dough_recipe_yield_pounds * weight * (orders_items.quantity - orders_items.quantity_baked))) as amount_needed_for_orders
    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
    GROUP BY id

花哨的裤子

Group by不跳过行或任何内容。您的查询结构似乎是错误的。尝试一下：

SELECT SUM(dough_recipe_ingredient_pounds / dough_recipe_yield_pounds * weight * (quantity - quantity_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,
    (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