示例
ID NAME QTY
1 A 3
1 B 2
2 A 0
2 B 1
如何通过一条SQL得出以下结果呢?
ID NAME QTY ROWNUM
1 A 3 1
1 A 3 2
1 A 3 3
1 B 2 1
1 B 2 2
2 B 1 1
即根据QTY产生重复记录,产生的记录数=QTY值,如果为0不产生记录,QTY数值大小不固定[@more@]
解决方法:下面的方法只是假设重复的最大记录为10条
SQL> with test as
2 (
3 select 1 ID, 'A' NAME , 3 QTY from dual union all
4 select 1 , 'B' , 2 from dual union all
5 select 2 , 'A' , 0 from dual union all
6 select 2 , 'B' , 1 from dual
7 )
8 select id,name, qty ,l from test,
9 (select level l from dual connect by level<=10)
10 where l<=qty
11 order by 1,2,3;
ID N QTY L
---------- - ---------- ----------
1 A 3 1
1 A 3 3
1 A 3 2
1 B 2 1
1 B 2 2
2 B 1 1
已选择6行。
SQL>