with t as(
select 1 id, 2 count, 't1' user_name from dual
)
select id, count, user_name, rownum rn from t connect by rownum <= t.count;
这样只能将一行数据按照count复制为多行,但是有多行数据的情况下,需要将每一行的数据按照count复制多行,然后放在一个结果集中,怎么办,请各位高手帮忙看看
select 1 id, 2 count, 't1' user_name from dual
)
select id, count, user_name, rownum rn from t connect by rownum <= t.count;
这样只能将一行数据按照count复制为多行,但是有多行数据的情况下,需要将每一行的数据按照count复制多行,然后放在一个结果集中,怎么办,请各位高手帮忙看看
with t as(
select 1 id, 2 count, 't1' user_name from dual
union all
select 2 id, 6 count, 't2' user_name from dual
)
select id, count, user_name, rownum rn from t connect by rownum <= t.count;
select 1 id, 2 count, 't1' user_name,10 n from dual
)
select id, count, user_name, rownum rn from t connect by rownum <= t.n;
select 1 id, 2 count, 't1' user_name from dual
union all
select 1,3,'t2' from dual
)
select * from t,
(select rownum rn from t connect by rownum <= count) b
where count>=rnID COUNT USER_NAME RN
---------------------- ---------------------- --------- ----------------------
1 3 t2 3
1 3 t2 2
1 3 t2 1
1 2 t1 2
1 2 t1 1