我有一个查询来查找废弃的购物车,如下所示:
SELECT c.customerid,
c.custconfirstname,
c.custconemail,
o.ordstatus,
o.orddate,
GROUP_CONCAT( 'Order Id: ', orderid, ' | Product name: ', ordprodname, ' | Quantity: ', ordprodqty, '<br>' ) AS ordered_items
FROM isc_customers c
LEFT OUTER JOIN isc_orders o ON o.ordcustid = c.customerid
LEFT OUTER JOIN isc_order_products op ON op.orderorderid = o.orderid
LEFT OUTER JOIN isc_product_images pi ON pi.imageprodid = op.orderprodid
GROUP BY c.customerid
HAVING COUNT( DISTINCT o.ordcustid ) >0
AND o.ordstatus = 0
AND o.orddate < UNIX_TIMESTAMP( ) - '18000'
AND o.orddate > UNIX_TIMESTAMP( ) - '259200'
对于每个客户(唯一客户 ID),BLOB 将为ordered_items生成类似以下内容:
Order Id: 15256 | Product name: PROD A | Quantity: 1
,Order Id: 15256 | Product name: PROD B | Quantity: 1
,Order Id: 15299 | Product name: PROD A | Quantity: 1
,Order Id: 15301 | Product name: PROD A | Quantity: 1
这基本上可以解释为客户在时间范围内有 3 辆废弃的购物车。由于此查询将用于发送废弃的购物车电子邮件,因此出于各种原因,我不想发送垃圾邮件并发送一封电子邮件,其中包含每个废弃购物车(唯一订单ID)中的每个产品,包括在上面的示例中,客户尝试将产品A放入购物车3次超过3个订单,因此会在电子邮件中收到商品3次。
那么如何限制查询,使其只返回每个客户 ID 1 个订单 id 的结果呢?
在查询中添加 DISTINCT,这将成功。
http://www.w3schools.com/sql/sql_distinct.asp