根据业务需求，我们需要统计得到各式各样的数据，以下是展示按照时间条件进行的统计样例。--近一年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
