我有一个'transactions‘表:
id user amount currency plan_id
1 1 11 usd 1
2 1 9 usd 2
3 1 10 usd 2
4 2 10 usd 7
5 1 7 eur 3
6 1 5 eur 3
7 1 6 eur 4
8 3 3 usd 8我想要获得按货币排序的金额总和,然后按给定用户进行plan_id:$results[currency][plan_id]
$results['usd'][1] = 11
$results['usd'][2] = 19
$results['eur'][3] = 12
$results['eur'][4] = 6到目前为止,我有:
$query = '
SELECT plan_id, currency, SUM( amount ) as earnings
FROM transactions
WHERE user = 1
GROUP BY currency, plan_id
';
$results = $wpdb->get_results( $query, OBJECT_K );但它只返回按plan_id定期排序的行,而忽略了按货币排序:
Array ( [1] => stdClass Object ( [plan_id] => 1 [currency] => usd [earnings] => 11 ) [2] => stdClass Object ( [plan_id] => 2 [currency] => usd [earnings] => 19 ) [3] => stdClass Object ( [plan_id] => 3 [currency] => eur [earnings] => 12 ) [7] => stdClass Object ( [plan_id] => 7 [currency] => eur [earnings] => 6 ) ) 有没有可能用$wpdb
请帮帮忙。谢谢
发布于 2018-08-13 00:58:10
您需要告诉MySQL如何使用ORDER BY clause对结果进行排序(查询中缺少)。
这将产生您想要的结果:
SELECT `plan_id`, `currency`, SUM( `amount` ) AS `earnings`
FROM `transactions`
WHERE `user` = 1
GROUP BY `currency`, `plan_id`
ORDER BY `currency` DESC, `plan_id` ASChttps://stackoverflow.com/questions/51809810
复制相似问题