现在我想要查出2023-07-01到2023-07-31这个时间段内的所有数据，但是还是可以查出来starttime字段为2023-07-01，result固定成0

现在有表time，字段为starttime和result

如果直接用查询语句的话能查出2023-07-16和2023-07-31的数据

SELECT * FROM `time`
where DATE_FORMAT(starttime,'%Y-%m-%d') between '2023-07-01' and '203-07-31'

现在我想要查出2023-07-01到2023-07-31这个时间段内的所有数据，比如2023-07-01没有数据，但是还是可以查出来starttime字段为2023-07-01，result固定成0，这样有可能实现吗

null也行吧？

表数据：

查出的数据：

先创建一个虚拟表只包含月份字段，然后和原表进行连接，再分组

SELECT 
    DATE_FORMAT(dates.date, '%Y-%m-%d') AS starttime,
    IFNULL(time.result, 0) AS result
FROM
    (SELECT DATE_FORMAT(ADDDATE('2023-07-01', INTERVAL n5.num*10000 + n4.num*1000 + n3.num*100 + n2.num*10 + n1.num DAY), '%Y-%m-%d') AS DATE
     FROM 
         (SELECT 0 AS num UNION ALL SELECT 1 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) n1,
         (SELECT 0 AS num UNION ALL SELECT 1 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) n2,
         (SELECT 0 AS num UNION ALL SELECT 1 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) n3,
         (SELECT 0 AS num UNION ALL SELECT 1 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) n4,
         (SELECT 0 AS num UNION ALL SELECT 1 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) n5
     WHERE ADDDATE('2023-07-01', INTERVAL n5.num*10000 + n4.num*1000 + n3.num*100 + n2.num*10 + n1.num DAY) BETWEEN '2023-07-01' AND '2023-07-31'
    ) dates
LEFT JOIN TIME ON DATE_FORMAT(dates.date, '%Y-%m-%d') = DATE_FORMAT(time.starttime, '%Y-%m-%d')
ORDER BY dates.date;

不知道你这个问题是否已经解决, 如果还没有解决的话:

• 你可以看下这个问题的回答https://ask.csdn.net/questions/745086

如果你已经解决了该问题, 非常希望你能够分享一下解决方案, 写成博客, 将相关链接放在评论区, 以帮助更多的人 ^-^