mysql - Woocommerce mySQL 查询 - 列出所有订单、用户和购买的商品

Question

我有一个完全可用的 mySQL 查询，它提取所有订单、用户、地址和从 Woocommerce 购买的商品，但它只单独列出产品，我想为显示的每个产品添加数量。

当前显示“已订购商品”
跑鞋
步行鞋

它应该在哪里显示“已订购的商品”
3 x 跑鞋
4 x 步行鞋

SELECT
  p.ID AS 'Order ID',
  p.post_date AS 'Purchase Date',
  MAX( CASE WHEN pm.meta_key = '_billing_email'       AND p.ID = pm.post_id THEN pm.meta_value END ) AS 'Email Address',
  MAX( CASE WHEN pm.meta_key = '_billing_first_name'  AND p.ID = pm.post_id THEN pm.meta_value END ) AS 'First Name',
  MAX( CASE WHEN pm.meta_key = '_billing_last_name'   AND p.ID = pm.post_id THEN pm.meta_value END ) AS 'Last Name',
  MAX( CASE WHEN pm.meta_key = '_billing_address_1'   AND p.ID = pm.post_id THEN pm.meta_value END ) AS 'Address',
  MAX( CASE WHEN pm.meta_key = '_billing_city'        AND p.ID = pm.post_id THEN pm.meta_value END ) AS 'City',
  MAX( CASE WHEN pm.meta_key = '_billing_state'       AND p.ID = pm.post_id THEN pm.meta_value END ) AS 'State',
  MAX( CASE WHEN pm.meta_key = '_billing_postcode'    AND p.ID = pm.post_id THEN pm.meta_value END ) AS 'Post Code',
    CASE p.post_status
      WHEN 'wc-pending'    THEN 'Pending Payment'
      WHEN 'wc-processing' THEN 'Processing'
      WHEN 'wc-on-hold'    THEN 'On Hold'
      WHEN 'wc-completed'  THEN 'Completed'
      WHEN 'wc-cancelled'  THEN 'Cancelled'
      WHEN 'wc-refunded'   THEN 'Refunded'
      WHEN 'wc-failed'     THEN 'Failed'
    ELSE 'Unknown'
    END AS 'Purchase Status',
  MAX( CASE WHEN pm.meta_key = '_order_total'         AND p.ID = pm.post_id THEN pm.meta_value END ) AS 'Order Total',
  MAX( CASE WHEN pm.meta_key = '_paid_date'           AND p.ID = pm.post_id THEN pm.meta_value END ) AS 'Paid Date',
  ( select group_concat( order_item_name separator '</p>' ) FROM wp_woocommerce_order_items where order_id = p.ID ) AS 'Items Ordered'
FROM  wp_posts AS p
JOIN  wp_postmeta AS pm ON p.ID = pm.post_id
JOIN  wp_woocommerce_order_items AS oi ON p.ID = oi.order_id
WHERE post_type = 'shop_order'
GROUP BY p.ID

我相信 Woocommerce 将数量存储在下表/条目中:

wp_woocommerce_order_itemmeta
  order_item_id
    SELECT wp_woocommerce_order_itemmeta.meta_value
    WHERE wp_woocommerce_order_itemmeta.meta_value = '_qty' and wp_woocommerce_order_itemmeta.order_item_id =

我需要以某种方式将它加入到这个部分:

( select group_concat( order_item_name separator '</p>' ) FROM wp_woocommerce_order_items where order_id = p.ID ) AS 'Items Ordered'

提前致谢。

更新了来自 Lucek 的回答

谢谢 Lucek，绝对完美。

我已经合并了完整的查询以防其他人想要复制它。

SELECT
  p.ID AS 'Order ID',
  p.post_date AS 'Purchase Date',
  MAX( CASE WHEN pm.meta_key = '_billing_email'       AND p.ID = pm.post_id THEN pm.meta_value END ) AS 'Email Address',
  MAX( CASE WHEN pm.meta_key = '_billing_first_name'  AND p.ID = pm.post_id THEN pm.meta_value END ) AS 'First Name',
  MAX( CASE WHEN pm.meta_key = '_billing_last_name'   AND p.ID = pm.post_id THEN pm.meta_value END ) AS 'Last Name',
  MAX( CASE WHEN pm.meta_key = '_billing_address_1'   AND p.ID = pm.post_id THEN pm.meta_value END ) AS 'Address',
  MAX( CASE WHEN pm.meta_key = '_billing_city'        AND p.ID = pm.post_id THEN pm.meta_value END ) AS 'City',
  MAX( CASE WHEN pm.meta_key = '_billing_state'       AND p.ID = pm.post_id THEN pm.meta_value END ) AS 'State',
  MAX( CASE WHEN pm.meta_key = '_billing_postcode'    AND p.ID = pm.post_id THEN pm.meta_value END ) AS 'Post Code',
    CASE p.post_status
      WHEN 'wc-pending'    THEN 'Pending Payment'
      WHEN 'wc-processing' THEN 'Processing'
      WHEN 'wc-on-hold'    THEN 'On Hold'
      WHEN 'wc-completed'  THEN 'Completed'
      WHEN 'wc-cancelled'  THEN 'Cancelled'
      WHEN 'wc-refunded'   THEN 'Refunded'
      WHEN 'wc-failed'     THEN 'Failed'
    ELSE 'Unknown'
    END AS 'Purchase Status',
  MAX( CASE WHEN pm.meta_key = '_order_total'         AND p.ID = pm.post_id THEN pm.meta_value END ) AS 'Order Total',
  MAX( CASE WHEN pm.meta_key = '_paid_date'           AND p.ID = pm.post_id THEN pm.meta_value END ) AS 'Paid Date',
  ( SELECT GROUP_CONCAT(CONCAT(m.meta_value, ' x ', i.order_item_name) separator '</br>' )
    FROM wp_woocommerce_order_items i
    JOIN wp_woocommerce_order_itemmeta m ON i.order_item_id = m.order_item_id AND meta_key = '_qty'
    WHERE i.order_id = p.ID AND i.order_item_type = 'line_item') AS 'Items Ordered',
  MAX( CASE WHEN pm.meta_key = 'post_excerpt'         AND p.ID = pm.post_id THEN pm.meta_value END ) AS 'User Comments'
FROM  wp_posts AS p
JOIN  wp_postmeta AS pm ON p.ID = pm.post_id
JOIN  wp_woocommerce_order_items AS oi ON p.ID = oi.order_id
WHERE post_type = 'shop_order'
GROUP BY p.ID

Answer 1

您需要将 Items Ordered 部分替换为:

( SELECT GROUP_CONCAT(CONCAT(m.meta_value, ' x ', i.order_item_name) separator '</p>' )
FROM da_woocommerce_order_items i
JOIN da_woocommerce_order_itemmeta m ON i.order_item_id = m.order_item_id AND meta_key = '_qty'
WHERE i.order_id = p.ID AND i.order_item_type = 'line_item') AS 'Items Ordered'

您可以在 CONCAT 函数中更改产品名称和数量之间的分隔符，现在是 ' x '。我还将 i.order_item_type = 'line_item' 添加到 where 子句 - 它会阻止获取运费、费用和优惠券。如果您在查询中需要全部内容 - 只需将其删除即可。