repeat result multiple times in mysql

Question

I have a table having id and no field, what I really want is the result raw will be repeated no filed times, if the no field is 2 then that raw must be repeated twice in result. this is my sample table structure:

     id  no   
     1   3
     2   2
     3   1

now I need to get a result like:

1  3
1  3 
1  3
2  2
2  2
3  1

I tried to write mysql query to get the result like above, but failed.

Answer 1

You need a table of numbers to accomplish this. For just three values, this is easy:

select t.id, t.no
from t join
     (select 1 as n union all select 2 union all select 3
     ) n
     on t.no <= n.no;