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`

二、统计近一月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

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

仅用于自己学习参考，如有错误，可提醒修改。

来源地址：https://blog.csdn.net/w18973572056/article/details/129364556