17,082
社区成员
发帖
与我相关
我的任务
分享
with tab1 as (
select 'aa1' name, 3000 score from dual
union all select 'aa2' name, 3000 score from dual
)
select name, 1000 as score,rownum, score, score/1000, level ,
lpad('--', level * 3) || level || '_' || name
from tab1
connect by rownum <= score / 1000
;
with tab1 as (
select 'aa1' name, 3000 score from dual
union all select 'aa2' name, 4000 score from dual
)
select name, 1000 as score,rownum, score, score/1000, level ,
lpad('--', level * 3) || level || '_' || name
from tab1
connect by rownum <= score / 1000
;
with tab1 as (
select 'aa1' name, 3000 score from dual
union all select 'aa2' name, 4000 score from dual
)
select name, 1000 as score,rownum, score, score/1000, level ,
lpad('--', level * 3) || level || '_' || name
from tab1
connect by rownum <= score / 1000
and level < 3
;
with tab1 as (
select 'aa1' name, 3000 score from dual
union all select 'aa2' name, 4000 score from dual
)
select name, 1000 as score,rownum, score, score/1000, level ,
lpad('--', level * 3) || level || '_' || name
from tab1
connect by rownum <= 6
and level < 3
;
select name,xb,1000 as score,rownum as rn from pn1 connect by rownum <= score/1000;
SQL> create table bjsj.pn1 (name varchar(8),xb varchar(2),n1 number);
SQL> insert into bjsj.pn1 values ('李四','男','5000');
SQL> select name,xb,1000 as n2,level from bjsj.pn1
2 connect by name=prior name and xb=prior xb and n1=prior n1
3 AND PRIOR DBMS_RANDOM.VALUE IS NOT NULL
4 and level <= n1/1000;
NAME XB N2 LEVEL
-------- -- ---------- ----------
李四 男 1000 1
李四 男 1000 2
李四 男 1000 3
李四 男 1000 4
李四 男 1000 5