php mysql 数据库 (yii) 分组查询实例
public function run($params){ if(!empty($params['match_code'])){ $match_code = $params['match_code']; }else{ return $this->errorReturn(1000); } $result = Yii::$app->db->createCommand("select count(id) as count, sum(fee) AS all_fee, group_concat(id) AS list, FROM_UNIXTIME(UNIX_TIMESTAMP(created_at), '%Y-%m-%d') AS create_date from postware where created_at>='".date("Y-m-d 00:00:00", strtotime("-20 year"))."' and created_at<'".date("Y-m-d 23:59:59", time())."' and `match_code`='".$match_code."' group by FROM_UNIXTIME(UNIX_TIMESTAMP(created_at), '%Y-%m-%d') order by created_at desc")->queryAll(); foreach($result as $key=>$val){ $ids = explode(",",$val['list']); foreach($ids as $k=>$v){ // $ids[$k] = Postware::find()->select(['id', 'account_id', 'order_code', 'no', 'customer_id', 'fee'])->where(['id'=>$v])->asArray()->all(); $ids[$k] = Postware::find()->select(new Expression("id, account_id, order_code, no, customer_id, fee, FROM_UNIXTIME(UNIX_TIMESTAMP(created_at), '%H:%i:%s') AS created_time"))->with('account')->where(['id'=>$v])->asArray()->all(); } $result[$key]['list'] = $ids; } $result = array_column($result, null, 'create_date'); return $this->dataReturn(['records'=>$result]); }
mysql 实现每天分组查询订单,统计每天的单数,总金额,将每天的订单列表展示出来, 我的created为 datetime 格式,所以使用 group by FROM_UNIXTIME(UNIX_TIMESTAMP(created_at), '%Y-%m-%d') ,将其转为date格式,然后进行分组。
其中字段可使用GROUP_CONCAT进行合并,但我没用。 想想以后怎么优化吧