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

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>