例如 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
求大神幫忙
uj5u.com熱心網友回復：
有人嗎！！！uj5u.com熱心網友回復：
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 dropped
SQL>
uj5u.com熱心網友回復：
sql :select * from dual;result:
c1 count
a 2
b 3
這種情況是不可能存在的。
uj5u.com熱心網友回復：
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;
uj5u.com熱心網友回復：
查詢 SELECThttp://www.verejava.com/?id=17173779389953
uj5u.com熱心網友回復：
6#，注意審題！uj5u.com熱心網友回復：
with a as (select 'a' c1, 2 count from dual
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
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/60634.html
標籤：開發