用于检索按州分组的WooCommerce订单的SQL查询

what I'm trying to retrieve from the Wordpress/WooCommerce database is a list of orders grouped by the states in the US with the Order totals and Order taxes summed for each state.

我试图从Wordpress / WooCommerce数据库中检索的是按美国各州分组的订单列表,其中订单总计和订单税总计为每个州。

So the closest I've gotten is this query I've written:

所以我得到的最接近的是我写的这个查询:

SELECT meta_key, meta_value 
FROM wp_posts 
LEFT OUTER JOIN wp_postmeta ON wp_posts.ID=wp_postmeta.post_id 
WHERE post_type = "shop_order" 
AND (
meta_key = "_billing_state" 
OR meta_key = "_order_tax" 
OR meta_key = "_order_total"
) 
ORDER BY ID ASC

And it returns:

它返回:

meta_key         meta_value
_billing_state   NJ
_order_tax       0.21
_order_total     3.21
_billing_state   NJ
_order_tax       1.75
_order_total     26.70
_billing_state   IA
_order_tax       6.79
_order_total     103.54
_billing_state   PA
_order_tax       1.82
_order_total     27.72

...and so on.

...等等。

What I really need to return is:

我真正需要回报的是:

state  order_tax_sum  order_total_sum
NJ     1.96           29.91
IA     6.79           103.54
PA     1.82           27.72

All help is massively appreciated!

所有帮助都非常感谢!

1 个解决方案

#1

try something like this:

尝试这样的事情:

select
    state,
    sum(order_tax_sum),
    sum(order_total_sum)
from (
    select
        (select meta_value from wp_postmeta pm1 where p.ID = pm1.post_id and meta_key = "_billing_state") as state,
        (select meta_value from wp_postmeta pm2 where p.ID = pm2.post_id and meta_key   = "_order_tax") as order_tax_sum,
        (select meta_value from wp_postmeta pm3 where p.ID = pm3.post_id and meta_key   = "_order_total") as order_total_sum
    from
        wp_posts AS p
    WHERE post_type = "shop_order"
) A
group by A.state

caveat - totally untested, but the basic idea (do that join in the select statement itself) should be sound

警告 - 完全未经测试,但基本思想(加入select语句本身)应该是合理的

#1