好的，我有以下内容：
user id meta
2 _qty 3
2 Weight 20
1 _qty 1
1 weight 30
我需要获取用户id列，然后将数量乘以用户id 2=60和用户id=130的权重。。。。。。我尝试了以下方法，但没有成功：
$myrows = $wpdb->get_results( "SELECT meta_value, SUM(_qty*weight) AS product_id FROM {$wpdb->prefix}woocommerce_order_itemmeta GROUP BY ($query_select_order_items)" );
其中CCD_ 1是user_id。我该怎么做？
我当前运行的实际php：
/**
* Returns all the orders made by the user
*
* @param int $user_id
* @param string $status (completed|processing|canceled|on-hold etc)
* @return array of order ids
*/
function fused_get_all_user_orders($user_id,$status='completed'){
if(!$user_id)
return false;
$orders=array();//order ids
$args = array(
'numberposts' => -1,
'meta_key' => '_customer_user',
'meta_value' => $user_id,
'post_type' => 'shop_order',
'post_status' => 'publish',
/* 'tax_query'=>array(
array(
'taxonomy' =>'shop_order_status',
'field' => 'slug',
'terms' =>$status
)
) */
);
$posts=get_posts($args);
//get the post ids as order ids
$orders=wp_list_pluck( $posts, 'ID' );
return $orders;
}
function fused_get_all_products_ordered_by_user($user_id=false){
$orders=fused_get_all_user_orders($user_id);
if(empty($orders))
return false;
$order_list='('.join(',', $orders).')';//let us make a list for query
//so we have all the orders made by this user which was successfull
//we need to find the products in these order and make sure they are downloadable
// find all products in these order
global $wpdb;
$query_select_order_items="SELECT order_item_id as id FROM {$wpdb->prefix}woocommerce_order_items WHERE order_id IN {$order_list}";
$query_select_product_ids="SELECT meta_value as product_id FROM {$wpdb->prefix}woocommerce_order_itemmeta WHERE meta_key=%s AND order_item_id IN ($query_select_order_items)";
$products=$wpdb->get_col($wpdb->prepare($query_select_product_ids,'weight'));
$qty = $wpdb->get_col($wpdb->prepare($query_select_product_ids,'_qty'));
return $products;
}
当前末尾的$products返回一个重量列表，或者如果我将其设置为_qty，它将返回一个订购数量列表。。。sqlfiddle
草莓更新答案：
"SELECT order_item_id, weight * quantity total FROM (
SELECT order_item_id,
MAX(CASE WHEN meta_key = '_qty' THEN meta_value ELSE 0 END) quantity ,
MAX(CASE WHEN meta_key = 'weight' THEN meta_value ELSE 0 END) weight
FROM wp_woocommerce_order_itemmeta GROUP BY order_item_id)
WHERE order_item_id IN ($query_select_order_items) x;"
这里有一种方法。。。
DROP TABLE IF EXISTS my_table;
CREATE TABLE my_table
(entity INT NOT NULL
,attribute VARCHAR(12) NOT NULL
,value INT NOT NULL
,PRIMARY KEY(entity,attribute)
);
INSERT INTO my_table VALUES
(2,'quantity',3),
(2,'weight',20),
(1,'quantity',1),
(1,'weight',30);
SELECT entity, weight * quantity total
FROM
( SELECT entity
, MAX(CASE WHEN attribute = 'quantity' THEN value END) quantity
, MAX(CASE WHEN attribute = 'weight' THEN value END) weight
FROM my_table
GROUP
BY entity
) x;
+--------+-------+
| entity | total |
+--------+-------+
| 1 | 30 |
| 2 | 60 |
+--------+-------+
甚至只是。。。
SELECT entity
, ROUND(EXP(SUM(LOG(value)))) total
FROM my_table
GROUP
BY entity;
+--------+-------+
| entity | total |
+--------+-------+
| 1 | 30 |
| 2 | 60 |
+--------+-------+
将第一个查询调整为fiddle可能看起来像这样。。。
SELECT order_item_id, weight * quantity total
FROM
( SELECT order_item_id
, MAX(CASE WHEN meta_key = '_qty' THEN meta_value ELSE 0 END) quantity
, MAX(CASE WHEN meta_key = 'weight' THEN meta_value ELSE 0 END) weight
FROM wp_woocommerce_order_itemmeta
GROUP
BY order_item_id
) x;
第二个可能是这样的。。。
SELECT order_item_id
, ROUND(EXP(SUM(LOG(meta_value)))) total
FROM wp_woocommerce_order_itemmeta
WHERE meta_key IN('_qty','weight')
GROUP
BY order_item_id;
并根据您的最新需求调整第一个。。。
SELECT i.order_item_id, weight * quantity total
FROM
( SELECT order_item_id
, MAX(CASE WHEN meta_key = '_qty' THEN meta_value ELSE 0 END) quantity
, MAX(CASE WHEN meta_key = 'weight' THEN meta_value ELSE 0 END) weight
FROM wp_woocommerce_order_itemmeta
GROUP
BY order_item_id
) x
JOIN wp_woocommerce_order_items i
ON i.order_item_id = x.order_item_id
WHERE i.order_id IN(647,649,650);
您可以使用以下查询：
select tbl1.val*tbl2.val
from mytbl as tbl1,mytbl as tbl2
where tbl1.user_id=tbl2.user_id
and tbl2.meta='Weight'
and tbl1.meta='_qty'
我还准备了小提琴。
SELECT A.user_id,
SUM(A.meta_value * B.meta_value)
FROM t A INNER JOIN t B ON A.user_id = B.user_id
WHERE A.meta = 'weight'
AND B.meta = '_qty'
GROUP BY
A.user_id
你可以在这个小提琴中尝试一下