Fill up rows to count-CodePudding

I got the result of a query, and want to join it with a mockup table to fill the rows up to a certain number. this is an example where the query has two rows as result, but i always want that to fill it up to 5 (see column slot)

SELECT * FROM foo WHERE …

foo	bar
jhkl	jol
das	das

result:

slot	foo	bar
1	jhkl	jol
2	das	das
3	NULL	NULL
4	NULL	NULL
5	NULL	NULL

You help is highly appreciated!

CodePudding user response：

You can use a recursive CTE to produce exactly 5 rows every time. For example:

with recursive g (n) as (select 1 union all select n   1 from g where n < 5)
select g.n as slot, x.foo, x.bar
from g
left join (select t.*, row_number() over() as rn from t) x on x.rn = g.n

Result:

 slot  foo   bar  
 ----- ----- ---- 
 1     abc   def  
 2     ghi   jkl  
 3     null  null 
 4     null  null 
 5     null  null

See running example at db<>fiddle.

CodePudding user response：

This should work on MySQL 5.7:

select s.slot, f.foo, f.bar
from 
(select @row:=0) as _init
cross join (
  select 1 as slot union select 2 union select 3 union select 4 union select 5
) as s
left outer join
(
  select @row:=@row 1 as slot, foo, bar from foo
) as f using (slot);

Output:

 ------ ------ ------ 
| slot | foo  | bar  |
 ------ ------ ------ 
|    1 | jhkl | jol  |
|    2 | das  | das  |
|    3 | NULL | NULL |
|    4 | NULL | NULL |
|    5 | NULL | NULL |
 ------ ------ ------