mysql - 使用 GROUP BY 根据 order_ID 对订单信息(数量、order_total 等)进行分组

Question

我有一个复杂的 SQL 代码需要编辑，以便它根据 order_ID 对订单信息进行分组。现在，单个产品有数量，product_totals 列出(即订单有多个产品，多个 order_ID)。

这是我的尝试。我所做的只是将 SUM() 添加到 order_quantity、product_cost、order_total 和 discount。然后我添加了一个 GROUP BY o1.id。我收到一条错误消息，说 GROUP BY 没有正确使用。我知道这段代码有很多困惑，我只是不想删除任何必要的东西，因为我没有自己编写这段代码。

这是我尝试过的代码:

DROP TABLE IF EXISTS reporting.promo_report;
CREATE TABLE reporting.promo_report
SELECT
    v1.id AS variant_id
    , v1.cost_price
    , p1.name AS product
    , GROUP_CONCAT(DISTINCT t1.parent_category SEPARATOR ", ") AS parent_categories
    , GROUP_CONCAT(DISTINCT t1.category SEPARATOR ", ") AS sub_categories
FROM `shop`.spree_variants AS v1 
INNER JOIN `shop`.spree_products AS p1 ON v1.product_id = p1.id
INNER JOIN `shop`.spree_products_taxons AS t2 ON p1.id = t2.product_id
INNER JOIN (SELECT
        t3.id
        , t3.name AS category
        , COALESCE(t4.name, t3.name) AS parent_category
    FROM shop.spree_taxons AS t3
    LEFT JOIN shop.spree_taxons AS t4 ON t3.parent_id = t4.id AND t3.parent_id IS NOT NULL) AS t1 ON t2.taxon_id = t1.id
WHERE t1.parent_category NOT IN('City','Themes')
GROUP BY 
    v1.id;
CREATE INDEX i1 ON reporting.tmp_shop_promo_report(variant_id);

SELECT o1.id AS order_id
        , o1.number
        , CAST(o1.completed_at AS DATE) AS order_date
        , SUM(l1.quantity) as order_quantity
        , SUM(l1.price * l1.quantity) AS product_total
        , SUM(adjust.amount) as discount
        , SUM(l1.cost_price) * l1.quantity AS product_cost
        , a2.`code` AS promotion_code

    FROM shop.spree_adjustments AS a1
        INNER JOIN shop.spree_promotion_actions AS p1 
            ON a1.`originator_id` = p1.id 
            AND a1.`originator_type` = 'Spree::PromotionAction'
        INNER JOIN shop.spree_activators AS a2 
            ON p1.`activator_id` = a2.id
        INNER JOIN shop.spree_orders AS o1 
            ON a1.`source_id` = o1.id 
            AND a1.`source_type` = 'Spree::Order'
        INNER JOIN shop.spree_addresses AS a3
            ON o1.bill_address_id = a3.id
        INNER JOIN shop.spree_states AS s1
            ON a3.state_id = s1.id
        INNER JOIN shop.spree_line_items AS l1 
            ON o1.id = l1.order_id
        LEFT JOIN reporting.tmp_shop_promo_report AS t1 
            ON l1.variant_id = t1.variant_id 
        INNER JOIN shop.line_item_histories AS l2 
            ON l1.id = l2.line_item_id
        LEFT JOIN shop.spree_adjustments as adjust
           ON adjust.adjustable_id = o1.id 
                AND adjust.adjustable_type = 'Spree::Order' 
                AND adjust.originator_type = 'Spree::PromotionAction'
                AND adjust.originator_id <> 2770 #free shipping
    WHERE a1.`eligible` = 1
        AND a2.code IN('PROMOCODE')
        AND NULLIF(a2.code,'') IS NOT NULL
        AND o1.completed_at IS NOT NULL;
    GROUP BY o1.id

Answer 1

你不说你的错误是什么，但试试这个:

-- as your code
SELECT o1.id AS order_id
    , MAX(o1.number) as number
    , CAST(o1.completed_at AS DATE) AS order_date
    , SUM(l1.quantity) as order_quantity
    , SUM(l1.price * l1.quantity) AS product_total
    , SUM(adjust.amount) as discount
    , SUM(l1.cost_price) * l1.quantity AS product_cost
    , max(a2.`code`) AS promotion_code
-- as your code