drop table test;
create table test (Start_id number,end_id number ,vse varchar2);insert into test (Start_id ,end_id,vse) values (1,5,x);
insert into test (Start_id ,end_id,vse) values (12,15,e);
insert into test (Start_id ,end_id,vse) values (2,4,r); commit;
请教大家一个问题,我想用SQL查出来如下结果,怎么取?ee vse
1 x
2 x
3 x
4 x
5 x
12 e
13 e
14 e
15 e
2 r
3 r
4 r
create table test (Start_id number,end_id number ,vse varchar2);insert into test (Start_id ,end_id,vse) values (1,5,x);
insert into test (Start_id ,end_id,vse) values (12,15,e);
insert into test (Start_id ,end_id,vse) values (2,4,r); commit;
请教大家一个问题,我想用SQL查出来如下结果,怎么取?ee vse
1 x
2 x
3 x
4 x
5 x
12 e
13 e
14 e
15 e
2 r
3 r
4 r
(select rownum rn from dual connect by rownum <= 20) t,
(select 1 start_id, 5 end_id, 'x' vse from dual
union all select 12,15,'e' from dual
union all select 2,4,'r' from dual) test
where t.rn between test.start_id and test.end_id
order by vse desc,rn;
SELECT DISTINCT sn, vse
FROM (SELECT LEVEL + a.start_id - 1 sn, a.*
FROM dual, test a
CONNECT BY LEVEL <= a.end_id - a.start_id + 1)
ORDER BY vse, sn
drop table test;
create table test (Start_id number,end_id number ,vse varchar2(10));insert into test (Start_id ,end_id,vse) values (1,5,'x');
insert into test (Start_id ,end_id,vse) values (12,15,'e');
insert into test (Start_id ,end_id,vse) values (2,4,'r'); commit;
SELECT A.START_ID + RN - 1 start_id, A.VSE
FROM (SELECT ROWNUM RN
FROM DUAL
CONNECT BY LEVEL <= (SELECT MAX(END_ID - START_ID + 1) FROM TEST)),
TEST A
WHERE START_ID + RN - 1 BETWEEN START_ID AND END_ID
ORDER BY 2, 1;
输出:
12 e
13 e
14 e
15 e
2 r
3 r
4 r
1 x
2 x
3 x
4 x
5 x