MySQL避免重复值

Question

我使用下面的查询来列出包含 4 个或更多个 SKU 的订单。找到这些订单后，我不想重复 customer_email。

当前输出示例:

Order ID    | customer_email    | total_cases_invoiced
5           | this@that.com     | 6
45          | email1@here.com   | 4
32          | email1@here.com   | 7
23          | email1@here.com   | 5
28          | another@email.com | 10

期望的输出示例:

Order ID    | customer_email    | total_cases_invoiced
5           | this@that.com     | 6
45          | email1@here.com   | 4
28          | another@email.com | 10

当前查询:

SELECT 
    *, customer_email, SUM(qty_invoiced) AS total_cases_invoiced
FROM
    mg_sales_flat_order_item
        JOIN
    mg_sales_flat_order ON mg_sales_flat_order.entity_id = mg_sales_flat_order_item.order_id
WHERE
    (mg_sales_flat_order_item.sku LIKE '%A'
        OR mg_sales_flat_order_item.sku LIKE '%F'
        OR mg_sales_flat_order_item.sku LIKE '%B'
        OR mg_sales_flat_order_item.sku LIKE '%C'
        OR mg_sales_flat_order_item.sku LIKE '%FP'
        OR mg_sales_flat_order_item.sku LIKE '%AP')
        AND row_total != 0
GROUP BY order_id
HAVING SUM(qty_invoiced) >= 4
ORDER BY customer_email DESC

Answer 1

您是否尝试像这样将您的 customer_email 与 customer_id 分组？

SELECT 
    *, customer_email, SUM(qty_invoiced) AS total_cases_invoiced
FROM
    mg_sales_flat_order_item
        JOIN
    mg_sales_flat_order ON mg_sales_flat_order.entity_id = mg_sales_flat_order_item.order_id
WHERE
    (mg_sales_flat_order_item.sku LIKE '%A'
        OR mg_sales_flat_order_item.sku LIKE '%F'
        OR mg_sales_flat_order_item.sku LIKE '%B'
        OR mg_sales_flat_order_item.sku LIKE '%C'
        OR mg_sales_flat_order_item.sku LIKE '%FP'
        OR mg_sales_flat_order_item.sku LIKE '%AP')
        AND row_total != 0
GROUP BY (order_id,customer_email)
HAVING SUM(qty_invoiced) >= 4
ORDER BY customer_email DESC