php实现 sql 查找月，日分组查询

最新推荐文章于 2021-03-26 19:53:24 发布

weixin_30799995

最新推荐文章于 2021-03-26 19:53:24 发布

阅读量185

点赞数

文章标签： php 数据库

原文链接：http://www.cnblogs.com/silverwangx/p/5010488.html

版权

1，按日查询

public static function getOrderDaysListByUserId($id, $limit, $offset)
    {

        $sql = "SELECT FROM_UNIXTIME(`created`, '%Y-%m-%d') as `days`, ";
        $sql .= "count(*) as day_num ";
        $sql .= "FROM `serve_order` `order` ";
        $sql .= "WHERE `id`=" . $id . " ";
        $sql .= "AND `status`=" . $search['status'] . " ";
        $sql .= "GROUP BY `days` ";
        $sql .= "ORDER BY `days` DESC ";
//        $sql .= "LIMIT {$offset}, {$limit}";

        $data = System_Model_ServeOrder::dataAccess()
            ->nativeSql($sql, array());

        if (empty($data)) {
            return array();
        }

        return $data;
    }

2.按月查询

public static function getOrderMonthsListByUserId($search, $limit, $offset)
    {

        $sql = "SELECT FROM_UNIXTIME(`created`, '%Y-%m') as `months`, ";
        $sql .= "count(*) as month_num ";
        $sql .= "FROM `serve_order` `order` ";
        $sql .= "WHERE `id`=" . $search['id'] . " ";
        $sql .= "AND `status`=" . $search['status'] . " ";
        $sql .= "GROUP BY `months` ";
        $sql .= "ORDER BY `months` DESC ";
//        $sql .= "LIMIT {$offset}, {$limit}";

        $data = System_Model_ServeOrder::dataAccess()
            ->nativeSql($sql, array());

        if (empty($data)) {
            return array();
        }

        return $data;
    }