Search code examples
sqlpercona-xtradb-clustermysql-8.4

SQL - Reply a single record in DB as mulitple records given two INT Fields and all values between the two

Lets assume table A

  id  min  max
#### #### ####
   1    1    3

from above there is one record. The query i perform is more complex but it boils down to one thing i try to do, which i cant imagine how to do like in this pseudo SQL

SELECT id, [BETWEEN(min,max)] AS val FROM table A;

Which should yield a reply with 3 records

 id   val
#### ####
  1     1
  1     2
  1     3

thx in advance if anyone could help out P.S.: the only thing i can imagine is having a ref table that holds every possible value and join on this table (or select it)

Solution

  • Use (controlled!) recursivity:

    with recursive v as
(
    select id, min val, max from a where min <= max
    union
    select id, val + 1, max from v where val < max
)
select id, val from v;

    See it running live.