我正在尝试获取特定日期/时间的所有订单，然后使用SQL（MySQL）订购产品，如下查询所示：
select
p.ID as order_id,
p.post_date,
i.order_item_name,
max( CASE WHEN im.meta_key = \'_product_id\' and p.ID = im.order_item_id THEN im.meta_value END ) as Prod_ID
from
wp_posts as p,
wp_postmeta as pm,
wp_woocommerce_order_items as i,
wp_woocommerce_order_itemmeta as im
where
p.post_type = \'shop_order\'
and p.ID = pm.post_id
and p.ID = i.order_id
and p.post_date BETWEEN \'2016-01-14 00:00:00\' AND \'2016-01-14 23:59:59\'
and p.post_status = \'wc-processing\'
当我尝试查询时
wp_woocommerce_order_itemmeta 订购产品数据表我正在断开与数据库的连接（查询期间连接超时）。
有什么线索吗？
最合适的回答,由SO网友:JackTheKnife 整理而成
固定查询
select
p.ID as order_id,
p.post_date,
i.order_item_name,
max( CASE WHEN im.meta_key = \'_product_id\' and i.order_item_id = im.order_item_id THEN im.meta_value END ) as Prod_ID
from
wp_posts as p,
wp_postmeta as pm,
wp_woocommerce_order_items as i,
wp_woocommerce_order_itemmeta as im
where
p.post_type = \'shop_order\'
and p.ID = pm.post_id
and p.ID = i.order_id
and p.post_date BETWEEN \'2016-01-14 00:00:00\' AND \'2016-01-14 23:59:59\'
and p.post_status = \'wc-processing\'
说明：Post ID与订单项元没有直接关系
SO网友:user2711750
我在构建自己的报告时发现，我想更新这个。
更好的方法是进行适当的内部联接，以最大限度地减少查询时间：
select
p.ID as order_id,
p.post_date,
i.order_item_name,
max( CASE WHEN im.meta_key = \'_product_id\' and i.order_item_id = im.order_item_id THEN im.meta_value END ) as Prod_ID
from
wp_posts as p
inner join
wp_postmeta as pm
on p.id = pm.post_id
inner join
wp_woocommerce_order_items as i
on p.id = i.order_id
inner join
wp_woocommerce_order_itemmeta as im
on i.order_item_id = im.order_item_id
where
p.post_type = \'shop_order\'
and p.post_date BETWEEN \'2017-04-29 00:00:00\' AND \'2017-04-30 23:59:59\'
and p.post_status = \'wc-processing\';
