如何删除MySQL Query中的重复行

Question

我拼凑了以下SQL查询来报告Magento网站的销售额。它按照我的意愿工作，除了复制每一行之外。例如：

Name    |   Product     |   Quantity
Fred    |   Prod 1      |   3
Fred    |   Prod 1      |   3
Sarah   |   Prod 2      |   1
Sarah   |   Prod 3      |   1
Sarah   |   Prod 2      |   1
Sarah   |   Prod 3      |   1

你知道我哪里错了吗？

SELECT CONCAT(address.firstname, ' ', address.lastname) AS Name,
address.email AS Email,
items.order_id AS Order_ID,
items.created_at AS Date,
items.name AS Description,
items.sku AS SKU,
items.qty_ordered AS Quantity,
items.price AS Price,
items.original_price AS Original_Price,
items.tax_amount AS Tax_Amount,
items.base_cost AS Base_Cost,
shipment.carrier_code AS Carrier,
shipmentcost.base_shipping_amount AS Shipping_Cost
FROM sales_order AS orders
JOIN sales_order_item AS items ON items.order_id = orders.entity_id
LEFT JOIN sales_order_address AS address ON orders.entity_id = address.parent_id
LEFT JOIN sales_shipment_track AS shipment ON shipment.order_id = orders.entity_id
LEFT JOIN sales_order AS shipmentcost ON shipmentcost.entity_id = orders.entity_id
WHERE items .created_at BETWEEN '2020-06-01 00:00:00' AND '2020-07-30 00:00:00'
AND orders.status = 'complete'

Answer 1

---通过添加组

Group BY
address.email ,
items.order_id ,
items.created_at ,
items.name ,          
items.sku ,
items.qty_ordered ,
items.price ,
items.original_price ,
items.tax_amount ,
items.base_cost ,
shipment.carrier_code ,
shipmentcost.base_shipping_amount