Woocommace mySQL 쿼리 - 모든 주문, 사용자 및 구매한 아이템 나열

Woocommace mySQL 쿼리 - 모든 주문, 사용자 및 구매한 아이템 나열

Woocommerce에서 구입한 주문, 사용자, 주소, 아이템을 모두 불러오는 mySQL 쿼리가 있는데, 제품만 개별적으로 표시되므로 표시되는 각 제품의 수량을 더하고 싶습니다.

현재 '주문된 아이템'이 표시됩니다.
러닝화
워킹화

'주문된 아이템'이 표시될 위치
러닝화 3개
워킹화 4개

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는 다음 테이블/엔트리에 QTY를 저장합니다.

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

고마워 루섹, 완벽해

다른 사람이 복사할 경우를 대비해서 전체 쿼리를 통합했습니다.

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

주문된 항목 섹션을 다음과 같이 교체해야 합니다.

( 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 조항 - 배송료, 요금 및 쿠폰을 받을 수 없습니다.쿼리에 모두 필요한 경우 삭제하십시오.

언급URL : https://stackoverflow.com/questions/53057786/woocommerce-mysql-query-list-all-orders-users-and-purchased-items