MySQL查询当月日期列表（月初至当天、当月整个月）

JMzz

已于 2022-09-07 13:37:36 修改

阅读量4.9k

点赞数 3

分类专栏：【MySQL】文章标签： mysql 数据库 mybatis

于 2022-09-06 16:55:42 首次发布

本文链接：https://blog.csdn.net/weixin_45740811/article/details/126728240

版权

【MySQL】专栏收录该内容

19 篇文章 0 订阅

订阅专栏

一、查询第一天到当月的日期列表

首先，可以在视图部分创建一个的视图，关于月初至当天的日期列表

代码如下

SELECT
	ADDDATE( tab2.firstDay, tab1.firstDay - 1 ) AS date 
FROM
	(
	SELECT
		1 AS firstDay UNION ALL
	SELECT
		2 UNION ALL
	SELECT
		3 UNION ALL
	SELECT
		4 UNION ALL
	SELECT
		5 UNION ALL
	SELECT
		6 UNION ALL
	SELECT
		7 UNION ALL
	SELECT
		8 UNION ALL
	SELECT
		9 UNION ALL
	SELECT
		10 UNION ALL
	SELECT
		11 UNION ALL
	SELECT
		12 UNION ALL
	SELECT
		13 UNION ALL
	SELECT
		14 UNION ALL
	SELECT
		15 UNION ALL
	SELECT
		16 UNION ALL
	SELECT
		17 UNION ALL
	SELECT
		18 UNION ALL
	SELECT
		19 UNION ALL
	SELECT
		20 UNION ALL
	SELECT
		21 UNION ALL
	SELECT
		22 UNION ALL
	SELECT
		23 UNION ALL
	SELECT
		24 UNION ALL
	SELECT
		25 UNION ALL
	SELECT
		26 UNION ALL
	SELECT
		27 UNION ALL
	SELECT
		28 UNION ALL
	SELECT
		29 UNION ALL
	SELECT
		30 UNION ALL
	SELECT
		31 
		) tab1,(
	SELECT
		CURDATE() - INTERVAL DAY (
		CURDATE()) - 1 DAY AS firstDay,
		DAY (CURDATE()) AS today 
	) tab2 
WHERE
	tab1.firstDay <= tab2.today

然后对所想要连接的查询数据进行连接

SELECT a.date perDay,ifnull(b.num,0) dynamicIncre FROM(
select date from since_month_begin ) a    # 查询视图日期部分
LEFT JOIN     
      (SELECT
       sum(dynamic_incre) num,incre_date dates
       from dynamic_statistics
       GROUP BY incre_date) b 
       ON a.date=b.dates  
       ORDER BY date asc

二、查询当月所有天数的日期列表

SELECT
	date 
FROM
	(
	SELECT
		DATE_FORMAT( DATE_SUB( last_day( curdate()), INTERVAL xc - 1 DAY ), '%Y-%m-%d' ) AS date 
	FROM
		(
		SELECT
			@xi := @xi + 1 AS xc 
		FROM
			( SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 ) xc1,
			( SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 ) xc2,
			( SELECT @xi := 0 ) xc0 
		) xcxc 
	) x0 
WHERE
	x0.date >= (
	SELECT
	date_add( curdate(), INTERVAL - DAY ( curdate())+ 1 DAY )) 
ORDER BY
	date ASC

这部分不能当做视图，否则会报含可变参数错误

可以直接写入查询语句

SELECT a.date perDay,ifnull(b.num,0) dynamicIncrement FROM(

select date from (
SELECT DATE_FORMAT(DATE_SUB(last_day(curdate()), INTERVAL xc-1 day), '%Y-%m-%d') as date
    FROM (
        SELECT @xi:=@xi+1 as xc from
            (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6) xc1,
            (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6) xc2,
            (SELECT @xi:=0) xc0
        ) xcxc) x0 where x0.date >= (select date_add(curdate(),interval-day(curdate())+1 day)) ORDER BY date asc) a 

LEFT JOIN

    (SELECT
        sum(dynamic_incre) num,incre_date dates
        from dynamic_statistics
        GROUP BY incre_date) b 

ON a.date=b.dates ORDER BY date asc