sum,group 一起使用 TP3.2

$order_item = M('order_item');
$order_item_list = $order_item->field(['*','sum(num) as num'])->group('sku_id')->order('num desc')->limit(10)->select();
// SELECT *,sum(num) as num FROM `tf_order_item` GROUP BY sku_id ORDER BY num desc LIMIT 10

group的同时,求和。求和之后,根据和的大小排序。然后获取最高的10条数据。

也可以加上where筛选条件

$order_item = M('order_item');
$order_item_list = $order_item->field(['*','sum(num) as num'])->where(['order_id'=>['in','1,2']])->group('sku_id')->order('num desc')->limit(10)->select();
// SELECT *,sum(num) as num FROM `tf_order_item` WHERE `order_id` IN ('1','2') GROUP BY sku_id ORDER BY num desc LIMIT 10

先where筛选,然后group的同时,求和。求和之后,根据和的大小排序。然后获取最高的10条数据。

SELECT select_list    
FROM table_name   
[ WHERE search_condition ]   
[ GROUP BY group_by_expression ]   
[ HAVING search_condition ]   
[ ORDER BY order_expression [ ASC | DESC ] ] 
[ LIMIT m,n] 
/**
 * mysql 执行顺序
 * SELECT select_list    
 * FROM table_name   
 * [ WHERE search_condition ]   
 * [ GROUP BY group_by_expression ]   
 * [ HAVING search_condition ]   
 * [ ORDER BY order_expression [ ASC | DESC ] ] 
 * [ LIMIT m,n] 
 */
public function index() {
    $order_item = M('order_item');
    $order_item_list = $order_item->field(['*','sum(num) as num'])->where(['order_id'=>['in','1,2']])->group('sku_id')->order('num desc')->limit(10)->select();
    $this->json->ok($order_item_list);
}
posted @ 2020-07-10 17:41  TBHacker  阅读(784)  评论(0编辑  收藏  举报