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进行合并,但我没用。  想想以后怎么优化吧

posted @ 2020-01-20 10:18  没事就更  阅读(599)  评论(0编辑  收藏  举报