MYSQL是查询的唯一结果

What i'm trying to do is get the DISTINCT results using a MYSQL query:

我要做的是使用MYSQL查询获取DISTINCT结果:

    $sql = "SELECT un.*, co.order_product_quantity AS product_num, cu.first_name, cu.surname, cp.product_name, cp.product_thumbnail AS prod_img, co.order_goods_cost, co.order_delivery_cost, co.order_status as status
        FROM (
            SELECT DISTINCT order_unique_id AS o_id, order_date
            FROM cbs_orders
            GROUP BY order_unique_id
        ) un
            INNER JOIN cbs_orders co ON co.order_unique_id = un.o_id
            INNER JOIN cbs_users cu ON cu.id = co.order_user_id
            INNER JOIN cbs_products cp ON cp.product_id = co.order_product_id
        ORDER BY un.order_date DESC LIMIT $start_from, $num_rec_per_page ";

I'm looking to get the DISTINCT order_unique_id from the query, i never wrote the query initially, i thought adding "SELECT DISTINCT order_unique_id" would solve the issue but it still brings back duplicate ids.

我想从查询中获取DISTINCT order_unique_id,我从来没有写过查询,我认为添加“SELECT DISTINCT order_unique_id”会解决问题,但它仍会带回重复的ID。

cheers for any help guys

为任何帮助人欢呼

Graham

2 个解决方案

#1

You want to leverage GROUP BY correctly; DISTINCT won't help you in this situation. Additional, I'm pretty sure that sub-select is not needed.

您想要正确利用GROUP BY;在这种情况下,DISTINCT不会帮助你。另外,我很确定不需要子选择。

Try this

 $sql = "SELECT un.order_unique_id AS o_id, un.order_date,
 co.order_product_quantity AS product_num, 
 cu.first_name, cu.surname, cp.product_name, 
 cp.product_thumbnail AS prod_img, co.order_goods_cost, 
 co.order_delivery_cost, co.order_status as status
   FROM cbs_orders AS un
   INNER JOIN cbs_orders co ON co.order_unique_id = un.o_id
   INNER JOIN cbs_users cu ON cu.id = co.order_user_id
   INNER JOIN cbs_products cp ON cp.product_id = co.order_product_id
     GROUP BY un.order_unique_id
     ORDER BY un.order_date DESC 
     LIMIT $start_from, $num_rec_per_page ";

This might produce odd results, because you are not applying aggregate functions to other columns. You may need to use those to get desired results.

这可能会产生奇怪的结果,因为您没有将聚合函数应用于其他列。您可能需要使用它们来获得所需的结果。

#2

Let me hazard a guess that you want records for only the most recent order. If this is the case, then you can do what you want, by properly aggregating the data:

让我猜一下你只想要最近订单的记录。如果是这种情况,那么您可以通过正确聚合数据来做您想做的事情:

SELECT un.*, co.order_product_quantity AS product_num, cu.first_name,
       cu.surname, cp.product_name, cp.product_thumbnail AS prod_img,   
       co.order_goods_cost, co.order_delivery_cost, co.order_status as status
FROM (SELECT order_unique_id AS o_id, max(order_date) as order_date
      FROM cbs_orders
      GROUP BY order_unique_id
     ) un INNER JOIN
     cbs_orders co
     ON co.order_unique_id = un.o_id INNER JOIN
     cbs_users cu
     ON cu.id = co.order_user_id INNER JOIN
     cbs_products cp
     ON cp.product_id = co.order_product_id
ORDER BY un.order_date DESC
LIMIT $start_from, $num_rec_per_page;

If you want only one row per order, then you need to figure out what to do with the products. In my opinion, this interpretation is so different from your current question that you should ask another question, providing sample data and desired results.

如果每个订单只需要一行,那么您需要弄清楚如何处理产品。在我看来,这种解释与您当前的问题是如此不同,您应该提出另一个问题,提供样本数据和期望的结果。

#1