MySQL 动态获取每日的时间

最新推荐文章于 2022-03-17 17:18:07 发布

嗨皮螃

最新推荐文章于 2022-03-17 17:18:07 发布

阅读量385

点赞数

分类专栏： MySQL

本文链接：https://blog.csdn.net/shark_pang/article/details/113053145

版权

MySQL 专栏收录该内容

18 篇文章 1 订阅

订阅专栏

动态获取每日时间常用于按时间汇总的时候使用，将时间作为左连接的条件进行多表左连接汇总


-- @startDate 这是开始时间戳
-- @endDate 这是结束时间戳
              SELECT ADDDATE( FROM_UNIXTIME(1612108800,'%Y-%m-%d'), INTERVAL @i:=@i+1 DAY) AS daytime
              FROM (
              SELECT a.a
              FROM (SELECT 0 AS a 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) AS a
              CROSS JOIN (SELECT 0 AS a 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) AS b
              CROSS JOIN (SELECT 0 AS a 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) AS c
              ) a
              JOIN (SELECT @i := -1) r1
              WHERE 
              --                         结束时间              ,               开始时间
              @i < DATEDIFF(FROM_UNIXTIME(1614528000,'%Y-%m-%d'), FROM_UNIXTIME(1612108800,'%Y-%m-%d'))