mySql Distinct - 按问题分组

Question

我试图找出购买最多的产品，但只计算不同的用户ID。基本上，我的客户希望停止来自同一用户的重复购买，这样他们就不会影响图表/畅销书。我需要仅使用不同用户 ID 来计算该产品的所有order_items。目前，结果正在计算所有order_items因此Distinct不起作用。

任何帮助，我将不胜感激。

提前致谢

SELECT * 
   FROM 
      ( SELECT DISTINCT 
              order_item_meta_3.meta_value as distinct_user_order_items_id,
              order_item_meta_2.meta_value as product_id,
              SUM( order_item_meta.meta_value ) as item_quantity
           FROM
              wp_woocommerce_order_items as order_items
                 LEFT JOIN wp_woocommerce_order_itemmeta as order_item_meta
                    ON order_items.order_item_id = order_item_meta.order_item_id
                 LEFT JOIN wp_woocommerce_order_itemmeta as order_item_meta_2
                    ON order_items.order_item_id = order_item_meta_2.order_item_id
                 LEFT JOIN wp_woocommerce_order_itemmeta as order_item_meta_3
                    ON order_items.order_item_id = order_item_meta_3.order_item_id 
                 LEFT JOIN wp_posts AS posts 
                    ON order_items.order_id = posts.ID
                    LEFT JOIN wp_term_relationships AS rel 
                       ON posts.ID = rel.object_ID
                    LEFT JOIN wp_term_taxonomy AS tax 
                       USING( term_taxonomy_id )
                    LEFT JOIN wp_terms AS term 
                       USING( term_id )
           WHERE
                  posts.post_type = 'shop_order'
              AND posts.post_status = 'publish'
              AND tax.taxonomy = 'shop_order_status'
              AND term.slug IN ('completed','processing','on-hold')
              AND order_items.order_item_type = 'line_item'
              AND order_item_meta.meta_key = '_qty'
              AND order_item_meta_2.meta_key = '_product_id'
              AND order_item_meta_3.meta_key = '_user_id'
           GROUP BY
              order_item_meta_2.meta_value
           ORDER BY
              item_quantity DESC ) as order_table, 
      wp_posts 
         LEFT JOIN wp_postmeta as mk1
            ON wp_posts.ID = mk1.post_id
         LEFT JOIN wp_postmeta as mk2
            ON wp_posts.ID = mk2.post_id
   WHERE
          order_table.product_id = wp_posts.ID
      AND wp_posts.ID = mk1.post_id
      AND mk1.meta_key = 'is_album' 
      AND mk1.meta_value = 0
      AND mk2.meta_key = '_price' 
      AND mk2.meta_value = 0

Answer 1

你不需要在组外做总和吗？ 您必须先具体化不同的集合，然后聚合它......

因此，将前几行更改为...

SELECT distinct_user_order_items_Id, product_Id, sum(item_Quantity) as item_Quantity
FROM (
SELECT
        order_item_meta_3.meta_value as distinct_user_order_items_id,
        order_item_meta_2.meta_value as product_id,
        order_item_meta.meta_value as item_quantity