MySQL query to find the missing numbers from a sequence-CodePudding

Write a MySQL query to get the gap between two range of sequence of numbers. Eg: if 18,19,21,24 in db, and if we select a range like 15 to 25, it need to return 15,16,17,20,22,23,25. Current logic not return 15-17 and 25, need to include that also..

CodePudding user response：

You need a calendar table approach here. Maintain a sequence table containing the range of values which you want to appear in your report. Then left join from that sequence table to your table.

SELECT n.val
FROM
(
    SELECT 15 AS val UNION ALL SELECT 16 UNION ALL SELECT 17 UNION ALL
    SELECT 18 UNION ALL SELECT 19 UNION ALL SELECT 20 UNION ALL
    SELECT 21 UNION ALL SELECT 22 UNION ALL SELECT 23 UNION ALL
    SELECT 24 UNION ALL SELECT 25
    -- more values here
) n
LEFT JOIN yourTable t
    ON t.val = n.val
WHERE n.val BETWEEN 15 AND 25 AND t.val IS NULL;

Note that in practice that the subquery above aliased as n may instead be a bona fide sequence table.

CodePudding user response：

You can use a recursive cte to generate a table with the numbers in the sequence you need, then do a left join with your table:

with recursive numbers as
(select 15 as n
union all select n   1 from numbers
where n < 25)
(select numbers.n
from numbers left join yourtable
on numbers.n = yourtable.n
where yourtable.n is null)

Fiddle