php 年月日分组分页

最新推荐文章于 2024-07-10 18:43:48 发布

暗影~行星

最新推荐文章于 2024-07-10 18:43:48 发布

阅读量429

点赞数

分类专栏： PHP工作后遇到的问题 ThinkPHP 文章标签： php 开发语言

本文链接：https://blog.csdn.net/weixin_42136237/article/details/131942059

版权

PHP工作后遇到的问题同时被 2 个专栏收录

125 篇文章 0 订阅

订阅专栏

ThinkPHP

58 篇文章 0 订阅

订阅专栏

//年月日 //分组 分页
        $type = $this->request->type;
        $dateType = "%Y-%m";//月
        $dateType1 = "CONCAT(tmp.date,'-01 00:00:00')";
        $dateType2 = "CONCAT(LAST_DAY(CONCAT(tmp.date, '-15')),' 23:59:59')";
        if ($type == 'day') {//日
            $dateType = "%Y-%m-%d";
            $dateType1 = "CONCAT(tmp.date,' 00:00:00')";
            $dateType2 = "CONCAT(tmp.date,' 23:59:59')";
        }elseif ($type == 'year') {//年
            $dateType = "%Y";
            $dateType1 = "CONCAT(tmp.date,'-01-01 00:00:00')";
            $dateType2 = "CONCAT(tmp.date,'-12-31 23:59:59')";
        }

        $sql = "SELECT (
                    SELECT CONCAT(sum(go.pv),'-',sum(go.buy_money),'-',count(go.id),'-',sum(go.number)) FROM orders go WHERE go.uid IN (
                        SELECT id FROM user WHERE recommend_path LIKE concat(u.recommend_path,',',u.id,',%') OR recommend_uid = u.id
                    ) AND go.pay_time >= {$dateType1} AND go.pay_time <= {$dateType2} {$whereGroupOrderSql}
                ) as group_total, (
                        SELECT CONCAT(sum(mo.pv),'-',sum(mo.buy_money),'-',count(mo.id),'-',sum(mo.number)) 
                        FROM orders mo
                        WHERE mo.uid = u.id {$whereMyOrderSql} AND mo.pay_time >= {$dateType1} AND mo.pay_time <= {$dateType2}
                ) as my_total, u.username, tmp.date
                FROM `user` u, (SELECT date_format(pay_time,'{$dateType}') as date FROM orders GROUP BY date) tmp
                WHERE 1 {$whereSql}
                GROUP BY tmp.date, u.id ORDER BY tmp.date desc, u.id 
                LIMIT {$start}, {$limit}";

        $records = Db::query($sql);