17,377
社区成员
发帖
与我相关
我的任务
分享
tmp AS(
SELECT ROWNUM rn FROM dual
CONNECT BY ROWNUM<=(SELECT MAX(fcount) FROM tt))
SELECT a.*,b.rn FROM tt a,tmp b
WHERE a.fcount>=b.rn
ORDER BY a.code,b.rn;
SQL> select * from test;
CODE NAME FCOUNT
---------- ---------- ---------------------
1 A 2
2 B 1
3 C 3
SQL> SELECT DISTINCT code,NAME,LEVEL FROM test CONNECT BY code=code AND LEVEL<=fcount order by code,level;
CODE NAME LEVEL
---------- ---------- ----------
1 A 1
1 A 2
2 B 1
3 C 1
3 C 2
3 C 3
6 rows selected
SQL>