CASE语句以及如何使用其中的值

本文介绍了CASE语句以及如何使用其中的值的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我有一个查询

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 CASEstatement 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.