PHP-在MySql中获取两列并相乘结果
内容导读
互联网集市收集整理的这篇技术教程文章主要介绍了PHP-在MySql中获取两列并相乘结果,小编现在分享给大家,供广大互联网技能从业者学习和参考。文章包含4163字,纯文字阅读大概需要6分钟。
内容图文
![PHP-在MySql中获取两列并相乘结果](/upload/InfoBanner/zyjiaocheng/882/308824c8749b432bbfc69774b382efe8.jpg)
好的,我有以下内容:
user id meta
2 _qty 3
2 Weight 20
1 _qty 1
1 weight 30
我需要获取用户ID列,然后将数量乘以权重,得出用户ID 2 = 60和用户ID = 1 30 …
我尝试了以下方法,但无济于事:
$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)" );
其中($query_select_order_items)是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 |
+--------+-------+
使第一个查询适应您的小提琴可能看起来像这样…
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);
内容总结
以上是互联网集市为您收集整理的PHP-在MySql中获取两列并相乘结果全部内容,希望文章能够帮你解决PHP-在MySql中获取两列并相乘结果所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错,欢迎将互联网集市网站推荐给程序员好友。
内容备注
版权声明:本文内容由互联网用户自发贡献,该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容, 请发送邮件至 gblab@vip.qq.com 举报,一经查实,本站将立刻删除。
内容手机端
扫描二维码推送至手机访问。