纯SQL获取当月所有日期(不借助任何表)

最新推荐文章于 2024-07-15 21:55:14 发布

chenwo9755

最新推荐文章于 2024-07-15 21:55:14 发布

阅读量898

点赞数

原文链接：https://my.oschina.net/u/3320076/blog/3022976

版权

本文分享了一种不依赖其他表，仅使用SQL获取当月所有日期的方法。通过创建一个包含31天的临时表，结合日期运算，筛选出当前月份的日期，可用于生成当月日期列表，也可扩展到计算剩余日期等场景。

摘要由CSDN通过智能技术生成

之前有个需求需要获取当月的所有需求，搜了一下实现SQL，很多都是借助其他表实现的:

https://blog.csdn.net/zhaofanjack/article/details/80231597

然后我想不用借助任何表实现这个功能，就想出如下杰作:

SELECT DATE(DATE_ADD(NOW(), INTERVAL tb.monthOfday - DAY(NOW()) DAY)) as monthOfDate FROM (
	SELECT 1 as monthOfday UNION ALL
	SELECT 2 as monthOfday UNION ALL
	SELECT 3 as monthOfday UNION ALL
	SELECT 4 as monthOfday UNION ALL
	SELECT 5 as monthOfday UNION ALL
	SELECT 6 as monthOfday UNION ALL
	SELECT 7 as monthOfday UNION ALL
	SELECT 8 as monthOfday UNION ALL
	SELECT 9 as monthOfday UNION ALL
	SELECT 10 as monthOfday UNION ALL
	SELECT 11 as monthOfday UNION ALL
	SELECT 12 as monthOfday UNION ALL
	SELECT 13 as monthOfday UNION ALL
	SELECT 14 as monthOfday UNION ALL
	SELECT 15 as monthOfday UNION ALL
	SELECT 16 as monthOfday UNION ALL
	SELECT 17 as monthOfday UNION A