CASE语句以及如何使用其中的值 [英] CASE statement and how to use values from it
问题描述
我有一个查询
select
p.ID as order_id,
p.post_date,
i.order_item_id,
max( CASE WHEN im.meta_key = '_product_id' and i.order_item_id = im.order_item_id THEN im.meta_value END ) as product_id,
max( CASE WHEN im.meta_key = '_qty' and i.order_item_id = im.order_item_id THEN im.meta_value END ) as qty,
max( CASE WHEN prm.meta_key = '_sku' and im.meta_value = prm.post_id THEN prm.meta_value END ) as sku,
max( CASE WHEN prm.meta_key = '_regular_price' and im.meta_value = prm.post_id THEN prm.meta_value END ) as product_price
from
wp_posts as p,
wp_postmeta as pm,
wp_woocommerce_order_items as i,
wp_woocommerce_order_itemmeta as im,
wp_postmeta as prm
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'
and p.ID = i.order_id
and i.order_item_id = im.order_item_id
group by
p.ID
但是我一直坚持使用product_id
获取两个值(两个值都为NULL),当在phpMyAdmin上我将按其值进行搜索时,我可以得到SKU和产品价格meta_value
作为位于wp_postmeta
表中的meta_key
的其他值.
but I'm stuck on obtaining sku
and product_price
using product_id
(both values came as NULL), when on phpMyAdmin I will do search by its value I can get SKU and product price meta_value
as well other values for meta_key
located in the wp_postmeta
table.
根据下面的评论和答案的有用指导,我进行了JOIN
版本查询,该查询有效但很慢:
Based on comments below and helpful directions from an answer I have made JOIN
version query which works but is slow:
select
p.ID as order_id,
p.post_date,
i.order_item_id,
max( CASE WHEN im.meta_key = '_product_id' THEN im.meta_value END ) as product_id,
max( CASE WHEN imq.meta_key = '_qty' THEN imq.meta_value END ) as qty,
max( CASE WHEN prm.meta_key = '_sku' THEN prm.meta_value END ) as sku,
max( CASE WHEN prm.meta_key = '_regular_price' THEN prm.meta_value END ) as product_price
from
wp_posts as p
join
wp_postmeta as pm
on
p.ID = pm.post_id
join
wp_woocommerce_order_items as i
on
p.ID = i.order_id
join
wp_woocommerce_order_itemmeta as im
on
i.order_item_id = im.order_item_id
join
wp_woocommerce_order_itemmeta as imq
on
i.order_item_id = imq.order_item_id
join
wp_postmeta as prm
on
im.meta_value = prm.post_id
where
p.post_type = 'shop_order'
and p.post_date BETWEEN '2016-01-14 00:00:00' AND '2016-01-14 23:59:59'
and p.post_status = 'wc-processing'
group by
p.ID
我的问题是为什么我需要为每个CASE
语句创建JOIN
-示例qty
的其他方式将值返回为NULL.
My question is why do I need to create JOIN
for each CASE
statement - other way for an example qty
returns value as NULL.
推荐答案
好-我最终删除了CASE
陈述,转而使用了显式的JOIN
语法.
Well - I have ended up removing CASE
statement and moving towards explicit JOIN
syntax.
select
p.ID as order_id,
p.post_date,
i.order_item_id,
im.meta_value as product_id,
imq.meta_value as qty,
prm.meta_value as sku,
prp.meta_value as product_price
from
wp_posts as p
join
wp_postmeta as pm
on
p.ID = pm.post_id
join
wp_woocommerce_order_items as i
on
p.ID = i.order_id
join
wp_woocommerce_order_itemmeta as im
on
i.order_item_id = im.order_item_id
join
wp_woocommerce_order_itemmeta as imq
on
i.order_item_id = imq.order_item_id
join
wp_postmeta as prm
on
im.meta_value = prm.post_id
join
wp_postmeta as prp
on
im.meta_value = prp.post_id
where
p.post_type = 'shop_order'
and p.post_date BETWEEN '2016-01-14 00:00:00' AND '2016-01-14 23:59:59'
and p.post_status = 'wc-processing'
and im.meta_key = '_product_id'
and imq.meta_key = '_qty'
and prm.meta_key = '_sku'
and prp.meta_key = '_regular_price'
group by
p.ID
感谢spencer7593提供了很好的指导.
Thanks to spencer7593 for good directions.
这篇关于CASE语句以及如何使用其中的值的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!