【MySQL】MySQL 查询两个日期内的每一天

最新推荐文章于 2024-03-27 17:45:03 发布

Tellsea

最新推荐文章于 2024-03-27 17:45:03 发布

阅读量355

点赞数 2

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

本文链接：https://blog.csdn.net/qq_38762237/article/details/135997015

版权

MySQL 专栏收录该内容

8 篇文章 0 订阅

订阅专栏

文章介绍了三种在MySQL中查询2024年2月1日至29日之间每一天的方法，包括直接计算、使用子查询生成临时表和外部参数传递。每种方法都有其优缺点和适用场景。

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

MySQL 查询两个日期内的每一天

写法1：如果没有操作 MYSQL.HELP_TOPIC 表的权限，则报错

SELECT DATE_ADD('2024-02-01', INTERVAL(CAST(HELP_TOPIC_ID AS SIGNED INTEGER)) DAY ) temp_date
FROM MYSQL.HELP_TOPIC
WHERE HELP_TOPIC_ID < (to_days('2024-02-29') - to_days('2024-02-01')) + 1
ORDER BY HELP_TOPIC_ID

写法2：请求太慢了

SELECT * FROM (
SELECT adddate('2020-01-01',t4.i * 10000 + t3.i * 1000 + t2.i * 100 + t1.i * 10 + t0.i) temp_date
FROM
(SELECT 0 i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t0,
(SELECT 0 i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t1,
(SELECT 0 i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t2,
(SELECT 0 i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t3,
(SELECT 0 i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t4
) date_list
WHERE temp_date BETWEEN '2024-02-01' AND '2024-02-29'

写法3：外面传参数进来当临时表（推荐）

<select id="listCompare" resultType="com.ruoyi.business.vo.TDeviceLogVo">
    SELECT A.temp_date
    FROM (
    <foreach collection="dateList" index="index" item="item" open="(" separator="union" close=")">
        select #{item} as temp_date
    </foreach>
         ) A
</select>