mysql 近一年、近一月、昨日、近一周等时间统计-Toy模板网

这篇具有很好参考价值的文章主要介绍了mysql 近一年、近一月、昨日、近一周等时间统计。希望对大家有所帮助。如果存在错误或未考虑完全的地方，请大家不吝赐教，您也可以点击"举报违法"按钮提交疑问。

根据业务需求，我们需要统计得到各式各样的数据，以下是展示按照时间条件进行的统计样例。

--近一年
select * from 表名 where YEAR (create_time )= YEAR (date_sub( now(), INTERVAL 0 YEAR ))

--近一月
select * from 表名 where date(create_time) >= DATE_SUB(CURDATE(),INTERVAL 30 DAY)

--近一周
select * from 表名 where date(create_time) >= DATE_SUB(CURDATE(),INTERVAL 7 DAY)

--昨日
select * from 表名 where to_days(now( ) ) - to_days( create_time) <= 1

在此基础上，有部分业务场景需要符合一些统计图形进行展示：
一、统计近一年每个月的数据

     SELECT
            v.`month`,
            IFNULL(b.settlement_total_amount,0) settlement_total_amount,
            IFNULL(b.supplier_sharing,0) supplier_sharing
        FROM
            (
                SELECT
                    DATE_FORMAT( CURDATE(), '%Y-%m' ) AS `month` UNION
                SELECT
                    DATE_FORMAT(( CURDATE() - INTERVAL 1 MONTH ), '%Y-%m' ) AS `month` UNION
                SELECT
                    DATE_FORMAT(( CURDATE() - INTERVAL 2 MONTH ), '%Y-%m' ) AS `month` UNION
                SELECT
                    DATE_FORMAT(( CURDATE() - INTERVAL 3 MONTH ), '%Y-%m' ) AS `month` UNION
                SELECT
                    DATE_FORMAT(( CURDATE() - INTERVAL 4 MONTH ), '%Y-%m' ) AS `month` UNION
                SELECT
                    DATE_FORMAT(( CURDATE() - INTERVAL 5 MONTH ), '%Y-%m' ) AS `month` UNION
                SELECT
                    DATE_FORMAT(( CURDATE() - INTERVAL 6 MONTH ), '%Y-%m' ) AS `month` UNION
                SELECT
                    DATE_FORMAT(( CURDATE() - INTERVAL 7 MONTH ), '%Y-%m' ) AS `month` UNION
                SELECT
                    DATE_FORMAT(( CURDATE() - INTERVAL 8 MONTH ), '%Y-%m' ) AS `month` UNION
                SELECT
                    DATE_FORMAT(( CURDATE() - INTERVAL 9 MONTH ), '%Y-%m' ) AS `month` UNION
                SELECT
                    DATE_FORMAT(( CURDATE() - INTERVAL 10 MONTH ), '%Y-%m' ) AS `month` UNION
                SELECT
                    DATE_FORMAT(( CURDATE() - INTERVAL 11 MONTH ), '%Y-%m' ) AS `month`
            ) v
                LEFT JOIN (
                SELECT LEFT
                    ( a.create_time, 7 ) AS `month`,
                    sum(a.settlement_total_amount) as settlement_total_amount,
                    sum(a.supplier_sharing) as supplier_sharing
                FROM
                    settlement_record AS a
                WHERE
                    a.create_time >= DATE_SUB(CURDATE(), INTERVAL 1 YEAR)
                GROUP BY
                    `month`
            ) AS b ON v.`month` = b.`month`
        GROUP BY
            v.`month`

mysql 近一年、近一月、昨日、近一周等时间统计
二、统计近一月30天的数据

   SELECT
            a.date_str,
            IFNULL(b.settlement_total_amount,0) settlement_total_amount,
            IFNULL(b.supplier_sharing,0) supplier_sharing
        FROM
            (
                SELECT
                    @cdate := date_add( @cdate, INTERVAL - 1 DAY ) AS date_str
                FROM
                    ( SELECT @cdate := date_add(CURDATE(), INTERVAL + 1 DAY ) FROM course_date ) tmp1
                where @cdate > DATE_SUB(CURDATE(),INTERVAL 1 MONTH)
            ) a
                LEFT JOIN (
                SELECT
                    DATE_FORMAT( create_time, '%Y-%m-%d' ) AS days,
                    sum(settlement_total_amount) as settlement_total_amount,
                    sum(supplier_sharing) as supplier_sharing
                FROM
                    settlement_record
                WHERE
                    date(create_time) >= DATE_SUB(CURDATE(),INTERVAL 30 DAY)
        GROUP BY days
            ) b on a.date_str = b.days
        ORDER BY a.date_str desc

mysql 近一年、近一月、昨日、近一周等时间统计
ps:course_date表可以试任意一个表，但是前提表的数据量必须大于查询的天数跨度
三、当天按照小时展示

    SELECT
            a.za_hour,
            IFNULL(b.settlement_total_amount,0),
            IFNULL(b.supplier_sharing,0)
        FROM
            (
                SELECT
                    0 AS za_hour UNION
                SELECT
                    1 AS za_hour UNION
                SELECT
                    2 AS za_hour UNION
                SELECT
                    3 AS za_hour UNION
                SELECT
                    4 AS za_hour UNION
                SELECT
                    5 AS za_hour UNION
                SELECT
                    6 AS za_hour UNION
                SELECT
                    7 AS za_hour UNION
                SELECT
                    8 AS za_hour UNION
                SELECT
                    9 AS za_hour UNION
                SELECT
                    10 AS za_hour UNION
                SELECT
                    11 AS za_hour UNION
                SELECT
                    12 AS za_hour UNION
                SELECT
                    13 AS za_hour UNION
                SELECT
                    14 AS za_hour UNION
                SELECT
                    15 AS za_hour UNION
                SELECT
                    16 AS za_hour UNION
                SELECT
                    17 AS za_hour UNION
                SELECT
                    18 AS za_hour UNION
                SELECT
                    19 AS za_hour UNION
                SELECT
                    20 AS za_hour UNION
                SELECT
                    21 AS za_hour UNION
                SELECT
                    22 AS za_hour UNION
                SELECT
                    23 AS za_hour
            ) a
                LEFT JOIN (
                SELECT HOUR
                    ( create_time ) AS days,
                    sum( settlement_total_amount ) AS settlement_total_amount,
                    sum( supplier_sharing ) AS supplier_sharing
                FROM
                    settlement_record
                WHERE
                    DATE_FORMAT( create_time, '%Y-%m-%d' ) = DATE_FORMAT( '', '%Y-%m-%d' )
                GROUP BY days
            ) b ON a.za_hour = b.days
        ORDER BY a.za_hour