【mysql】生成一年的连续日期数据，补全日期

阿来小同学

已于 2024-08-28 11:19:08 修改

阅读量69

点赞数 3

文章标签： mysql 数据库

于 2024-08-28 11:17:44 首次发布

本文链接：https://blog.csdn.net/skybboy/article/details/141636115

版权

SELECT order_date FROM (
SELECT
  @cdate := date_add( @cdate, INTERVAL - 1 DAY ) AS order_date 
FROM
  (
  SELECT
    @cdate := date_add( DATE_FORMAT( order_date, '%Y-%m-%d' ), INTERVAL + 1 DAY ) 
  FROM
    (
    SELECT
      @xi := @xi + 1 AS xc,
      '2024-12-31' AS order_date 
    FROM
      ( SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 ) xc1,
      ( SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 ) xc2,
      ( SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 ) xc3,
      ( SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 ) xc4,
      ( SELECT @xi := 0 ) xc0 
    ) tmp1 
  ) AS t
) AS a WHERE order_date BETWEEN '2024-01-01' AND '2024-12-31'