例如 dual 查询结果为下
sql :select * from dual;
result:
c1 count
a 2
b 3需求:根据count数量,查询此表多条重复数据,如a列的count为2查出来的纪录则为2条纪录,b列则为3条重复纪录,那么结果集就是
a 2
a 2
b 3
b 3
b 3求大神帮忙
sql :select * from dual;
result:
c1 count
a 2
b 3需求:根据count数量,查询此表多条重复数据,如a列的count为2查出来的纪录则为2条纪录,b列则为3条重复纪录,那么结果集就是
a 2
a 2
b 3
b 3
b 3求大神帮忙
SQL>
SQL> create table test(c1 varchar(10), c int);
Table created
SQL> begin
2 insert into test values('a', 2);
3 insert into test values('b', 3);
4 end;
5 /
PL/SQL procedure successfully completed
SQL> select c1, c
2 from test
3 connect by level <= c
4 and prior c1 = c1
5 and prior dbms_random.value is not null;
C1 C
---------- ---------------------------------------
a 2
a 2
b 3
b 3
b 3
SQL> drop table test purge;
Table droppedSQL>
result:
c1 count
a 2
b 3这种情况是不可能存在的。
with tmp as
(
select 'a' as c1, 2 c2 from dual
union all
select 'b' as c1, 3 c2 from dual
)
select c1, c2
from tmp
connect by prior c2 = c2
and level <= c2
and prior dbms_random.value is not null;
union all
select 'b' , 3 from dual )
select * from a a connect by prior c1=c1 and prior dbms_random.value is not null and level<=
count
1 a 2
2 a 2
3 b 3
4 b 3
5 b 3
一条语句就搞出来了,如果你想搞明白,就去研究 PRIOR ROWID=ROWID