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

原创已于 2024-08-28 11:19:08 修改 · 480 阅读

1 ·

CC 4.0 BY-SA版权

文章标签：

#mysql #数据库

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

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'