Mysql日期统计自动补充日期，Mysql日期统计日期不连续解决方案

蕃薯耀

已于 2022-10-10 16:03:27 修改

阅读量2.4k

点赞数 1

分类专栏： java 蕃薯耀分享数据库文章标签： sql 数据库 Mysql日期统计 Mysql统计日期不连续蕃薯耀

于 2022-10-10 16:02:04 首次发布

本文链接：https://blog.csdn.net/w995223851/article/details/127246081

版权

蕃薯耀分享同时被 3 个专栏收录

409 篇文章 1 订阅

订阅专栏

java

278 篇文章 1 订阅

订阅专栏

数据库

44 篇文章 0 订阅

订阅专栏

==============================

©Copyright 蕃薯耀 2022-10-10

蕃薯耀的博客_CSDN博客-蕃薯耀分享,java,js领域博主

一、定义日期表（临时）

查询出连续的日期，数量7表示一周，可修改成别的，如365

sys_date：

随便一个表，但这个表的数据量一定要大于7（或者更多）这个数量才可以，不然查询的数据数量会少。建议自定义一张表，里面的数据满足查询的数量

SELECT * FROM (
	SELECT
		@date := DATE_ADD(@date, INTERVAL + 1 DAY) date_count
	FROM
	(
		SELECT
		@date := DATE_SUB(CURDATE(), INTERVAL + 7 DAY)
		FROM sys_date --随便一个表，但这个表的数据量一定要大于7（或者更多）这个数量才可以，不然查询的数据数量会少。建议自定义一张表，里面的数据满足查询的数量
		LIMIT 7
	) TIME
) tt
;

二、自定义日期表

日期表只是一张中间表，为了查询出充足的数据量（条数），表中的数据不重要，根据业务要求插入数据

--定义日期表，往里面插入1万条数据
CREATE TABLE `sys_date` (
  Id BIGINT PRIMARY KEY AUTO_INCREMENT COMMENT 'ID主键'
  ,Remark VARCHAR(20) COMMENT '备注'

) ENGINE=INNODB CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT '日期查询表';

三、Mysql批量插入数据

往表插入1万条数据

/*循环插入*/

--drop PROCEDURE insertData;
--定义存储过程
DELIMITER //
CREATE PROCEDURE insertData()
BEGIN
  DECLARE num INT;
  SET num = 1;
  WHILE
    num < 10001 DO
    INSERT INTO sys_date(Remark) VALUES(num);
    SET num = num + 1;
  END WHILE;
END; 
//


--调用存储过程
CALL insertData();

四、Mysql按照日期统计（日期连续、自动补充日期）

Mysql日期统计自动补充日期，Mysql日期统计日期不连续解决方案

SELECT tt.* 
,IFNULL(SUM(d.Quantity), 0) Quantity
FROM (
	SELECT
		@date := DATE_ADD(@date, INTERVAL + 1 DAY) `date`
	FROM
	(
		SELECT @date := DATE_SUB(CURDATE(), INTERVAL + 7 DAY)
		FROM sys_date
		LIMIT 7
	) TIME 
) tt
LEFT JOIN stock_in si ON DATE(si.StockInDate) = tt.date AND si.ApprovalStatus=90
LEFT JOIN stock_in_details d ON d.StockInId = si.StockInId
GROUP BY tt.date
ORDER BY tt.date
;

查询一年的示例结果：

date	Quantity
2021-10-11	0
2021-10-12	0
2021-10-13	0
2021-10-14	0
2021-10-15	0
2021-10-16	0
2021-10-17	0
2021-10-18	0
2021-10-19	0
2021-10-20	0
2021-10-21	0

…………

2022-09-17	0
2022-09-18	0
2022-09-19	0
2022-09-20	5
2022-09-21	305
2022-09-22	0
2022-09-23	0
2022-09-24	0
2022-09-25	0
2022-09-26	0
2022-09-27	177
2022-09-28	0
2022-09-29	10
2022-09-30	3
2022-10-01	0
2022-10-02	0
2022-10-03	0
2022-10-04	0
2022-10-05	0
2022-10-06	0
2022-10-07	0
2022-10-08	0
2022-10-09	0
2022-10-10	0

==============================

蕃薯耀的博客_CSDN博客-蕃薯耀分享,java,js领域博主