Mysql会计周期查询

最近写MySQL会计周期查询的时候，一条语句写出来很繁琐，于是简化而一下步骤

1、使用临时表

-- 并不需要删除可以把结束时间设置为十年后一次性计算出来
drop table date_period; 
-- 创建临时表
CREATE TEMPORARY TABLE date_period
AS (
	WITH RECURSIVE date_range AS (
	  SELECT DATE('2019-01-26') AS start_date, DATE('2019-02-25') AS end_date
	  UNION ALL
	  SELECT DATE_ADD(end_date, INTERVAL 1 DAY), DATE_ADD(end_date, INTERVAL 1 MONTH)
	  FROM date_range
	  WHERE end_date < CURDATE()
	)
	SELECT
	  start_date,
	  end_date,
	  DATE_FORMAT(DATE_ADD(start_date, INTERVAL 1 MONTH), '%Y-%m') AS month_start
	FROM date_range
);
-- 查询临时表数据
select * from date_period;

2、使用临时表关联

-- 关联查询
SELECT
	t.start_date,t.end_date,t.month_start,SUM( output ) AS total_quantity 
FROM
	date_period t
	JOIN you_table u ON u.data_day >= t.start_date AND u.data_day <= t.end_date 
-- WHERE 如果有条件接条件即可
GROUP BY
	t.start_date,
	t.end_date,
	t.month_start