Question

我有一个查询寻找废弃的车看起来像这样:

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

对于每个客户(独特的客户),BLOB会为订货物品生产类似此类物品:

 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辆废弃的车。因为这个查询将被用来发送一个我不想垃圾邮件的废弃车型电子邮件, 并发送一个电子邮件, 包含每个废弃车型(独家订货)的每件产品, 原因多种多样, 包括上面的例子, 客户试图将产品A放在车内, 3次加3次, 从而在电子邮件上收到3次。

因此,我如何限制查询,使其只返回每个客户的1个定单的结果?

Answer 1

在您的查询中添加 DISTINCT, 这样就可以成功。

http://www.w3schools.com/sql/sql_gradle.asp" rel="nofollows.com/sql/sql_gradle.asp http://www.w3schools. com/sql/sql_gradle.asp

友情链接