Question 215461 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – how to query gap data with sql

I have this data in mysql 5.7

drop table if exists t_test;
create table t_test (
  dates date,
  num int
);
insert into t_test values('2022-02-01', 10), ('2022-02-10', 20);

How to get this data with sql? date_add('2022-02-01', interval 30 day) = 2023-03-03

dates num
2022-02-01 10
2022-02-02 10
… …
2022-02-09 10
2022-02-10 20
2022-02-11 20
… …
2022-03-03 20
Tags:

2

Answers


  1. 0

    This can be done using a recursive CTE :

    WITH RECURSIVE cte AS
(
  SELECT num, dates
  FROM t_test
  UNION ALL
  SELECT num,  dates + INTERVAL 1 day
  FROM cte
  WHERE dates + INTERVAL 1 DAY <= '2022-03-03'
)
SELECT dates, max(num) as num
FROM cte
group by dates
order by num, dates

    Demo here

    Login or Signup to reply.
  2. 0

    It’s not pretty but you could use something like the following:

    SELECT seq.dt AS `dates`, (SELECT num FROM t_test t WHERE t.dates <= seq.dt ORDER BY t.dates DESC LIMIT 1) AS `num`
FROM (
    -- generate date sequence from min_date to min_date + INTERVAL 39 DAY
    SELECT t1.min_dt + INTERVAL (seq2.n * 10) + seq1.n DAY AS dt
    FROM ( SELECT 0 AS n 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 ) seq1
    CROSS JOIN ( SELECT 0 AS n UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 ) seq2
    JOIN (SELECT MIN(dates) AS min_dt FROM t_test) t1
) seq
WHERE seq.dt <= '2022-03-03'
ORDER BY seq.dt ASC;

    If you want more than 39 days then you can increase the size of seq2.

    Here’s a db<>fiddle

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search