repeat the result several times in MySQL

Question

I have a table with an identifier and without a field, what I really want is that the raw result will be repeated without registration, if the field no is 2, then this raw repeat should be repeated twice as a result. this is my sample table structure:

     id  no   
     1   3
     2   2
     3   1

Now I need to get this result:

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

I tried to write a MySQL query to get the result as above, but failed.

Answer 1

For this you need a table of numbers. These are just three meanings:

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;

Answer 2

This query should do what you want to achieve:

select t.id, t.no from  test t cross join  test y where t.id>=y.id

Answer 3

,

set @i=0;

select 
    test_table.*
from 
     test_table
join 
    (select
         @i:=@i+1 as i
     from 
         any_table_with_number_of_rows_greater_than_max_no_of_test_table
     where 
         @i < (select max(no) from test_table)) tmp on no >= i
order by
     id desc