SQL语句从一个给出的月份查出所有的日期

最新推荐文章于 2024-04-22 13:24:23 发布

weixin_42156610

最新推荐文章于 2024-04-22 13:24:23 发布

阅读量553

点赞数 11

文章标签： sql 数据库 mysql

本文链接：https://blog.csdn.net/weixin_42156610/article/details/136567867

版权

先上代码

SELECT
	ADDDATE( monthAndDay.FIRST, allMonth.d - 1 ) AS AttendanceDate 
FROM
	(
	SELECT
		1 AS d 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 
	) allMonth,
	( SELECT CONCAT( '2024-02', '-01' ) AS FIRST, DAY ( LAST_DAY( str_to_date( '2024-02', '%Y-%m' ))) AS lastday ) monthAndDay 
WHERE
	allMonth.d <= monthAndDay.lastday;

首先得建一个中间表，假设表名allMonth, 获得31行1-31的数字;

再建一张中间表 放指定月份第一天日期和最后一天序号（最后一天用LAST_DAY（）函数查出）, 比如假设这张表monthAndDay,  2024-02月最后一天是29号，这里值就是29。本代码示例中 monthAndDay 表的FIRST字段就是 '2024-02-01', last字段值就是 29。

最后用 ADDDATE( monthAndDay.FIRST, allMonth.d - 1 )得到此月所有日期，注意allMonth.d 是一个表，就查出符合条件的所有行。