时间戳

首页 > 解决方案 > Woocommerce sql 产品订单价格

sql - Woocommerce sql 产品订单价格

问题描述

select
 
max( CASE WHEN pm.meta_key = '_product_id' and p.order_item_id = pm.order_item_id THEN pm.meta_value END ) as "Id",
p.order_item_name as "Product Name",
max( CASE WHEN wppm.meta_key = '_regular_price' and wpp.ID = wppm.post_id and wpp.ID = p.order_id THEN wppm.meta_value END ) as "Regular Prices",
max( CASE WHEN pm.meta_key = '_qty' and p.order_item_id = pm.order_item_id THEN pm.meta_value END ) as "qty",
max( CASE WHEN pm.meta_key = '_line_total' and p.order_item_id = pm.order_item_id THEN pm.meta_value END ) as "Gross",
max( CASE WHEN pm.meta_key = '_line_subtotal_tax' and p.order_item_id = pm.order_item_id THEN pm.meta_value END ) as subTotalTax,
max( CASE WHEN pm.meta_key = '_line_tax' and p.order_item_id = pm.order_item_id THEN pm.meta_value END ) as Tax,
max( CASE WHEN pm.meta_key = '_tax_class' and p.order_item_id = pm.order_item_id THEN pm.meta_value END ) as taxClass,
max( CASE WHEN pm.meta_key = '_line_subtotal' and p.order_item_id = pm.order_item_id THEN pm.meta_value END ) as subtotal
from
wp_woocommerce_order_items as p,
wp_woocommerce_order_itemmeta as pm,
wp_posts as wpp,
wp_postmeta as wppm
 where order_item_type = 'line_item' and

 p.order_item_id = pm.order_item_id 
 and wpp.ID = wppm.post_id
AND wpp.ID = p.order_id

 
 group by
p.order_item_id

所有列结果都可以，但 begular 价格为空，我该如何解决？如何计算总量？

标签： sqlwordpress

解决方案

我无法谈论您的特定问题，但查询的结构应为：

select max( CASE WHEN pm.meta_key = '_product_id' THEN pm.meta_value END ) as "Id",
       p.order_item_name as "Product Name",
       max( CASE WHEN wppm.meta_key = '_regular_price' and wpp.ID = wppm.post_id and wpp.ID = p.order_id THEN wppm.meta_value END ) as "Regular Prices",
       max( CASE WHEN pm.meta_key = '_qty' THEN pm.meta_value END ) as "qty",
       max( CASE WHEN pm.meta_key = '_line_total' THEN pm.meta_value END ) as "Gross",
       max( CASE WHEN pm.meta_key = '_line_subtotal_tax' THEN pm.meta_value END ) as subTotalTax,
       max( CASE WHEN pm.meta_key = '_line_tax' THEN pm.meta_value END ) as Tax,
       max( CASE WHEN pm.meta_key = '_tax_class' THEN pm.meta_value END ) as taxClass,
       max( CASE WHEN pm.meta_key = '_line_subtotal' THEN pm.meta_value END ) as subtotal
from wp_woocommerce_order_items p join
     wp_woocommerce_order_itemmeta pm
     on p.order_item_id = pm.order_item_id join
     wp_posts wpp
     on wpp.ID = p.order_id join
     wp_postmeta as wppm
     on wpp.ID = wppm.post_id
where order_item_type = 'line_item'
group by p.order_item_id;

笔记：

  • 使用正确、明确、标准、易读的JOIN语法。
  • 切勿在FROM子句中使用逗号。
  • 表达式and p.order_item_id = pm.order_item_id中不需要条件。case

我还怀疑您的查询正在生成笛卡尔积。再一次，这不会影响您的结果，它只会减慢查询速度。但是，您没有提供样本数据和期望的结果，因此这不是 100% 确定的。

我认为这不会解决您的特定问题。但大概'_regular_price'不能作为wppm.

推荐阅读