我有一个完全可以工作的mysql查询,它提取所有订单、用户、地址和从woocommerce购买的商品,但是它只单独列出产品,我想为每个显示的产品添加数量。
当前显示“订购的项目”
跑步鞋
步行鞋
显示“订购物品”的位置
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将数量存储在下表/条目中:
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
1条答案
按热度按时间1tu0hz3e1#
您需要用以下内容替换“订购的项目”部分:
您可以在concat函数中更改产品名称和数量之间的分隔符,现在它是“x”。我还补充说
i.order_item_type = 'line_item'
到哪里条款-它阻止获得运费,费用和优惠券。如果你需要在你的查询中全部删除它。