CODEWITHSUNDEEP
mysqlsql
abhi
abhi

Reputation: 818

repeat result multiple times in mysql

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.

Upvotes: 5

Views: 1066

Answers (4)

KrazzyNefarious
KrazzyNefarious

Reputation: 3230

EDIT : This is on SQL Server. I checked online and see that CTEs work on MySQL too. Just couldn't get them to work on SQLFiddle

Try this, remove unwanted columns

create table #temp (id int, no int)

insert into #temp values (1, 2),(2, 3),(3, 5)

select * from #temp

;with cte as
    (
        select id, no, no-1 nom from #temp
        union all
        select c.id, c.no, c.nom-1 from cte c inner join #temp t on t.id = c.id and c.nom < t.no and c.nom > 0
    )

select * from cte order by 1

drop table #temp

Upvotes: 0

Hieu Vo
Hieu Vo

Reputation: 3274

not completely solve your problem, but this one can help

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

Upvotes: 0

Olesya Razuvayevskaya
Olesya Razuvayevskaya

Reputation: 1168

This query must do what you want to achieve:

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

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269773

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;

Upvotes: 4

Related Questions