with tt as (select 'a' A,1 min,3 max from dual union all select 'b' A,2 min,3 max from dual union all select 'c' A,1 min,1 max from dual ), nn as (select level lv from dual connect by level<100) select tt.a, nn.lv from tt, nn where nn.lv between tt.min and tt.max order by tt.a
虽然知道with,但是没用这么深
SELECT A,MIN FROM Table UNION SELECT A,MAX FROM Table UNION SELECT A,MIN+1 AS MIN FROM Table WHERE MIN<MAX
“SQL codewith tt as (select 'a' A,1 min,3 max from dual union all select 'b' A,2 min,3 max from dual union all select 'c' A,1 min,1 max from dual ), nn as (select level lv from dual connect by level<100) select tt.a, nn.lv from tt, nn where nn.lv between tt.min and tt.max order by tt.a”感觉一楼的正确
select t1.col1,t1.col2,t1.col3,t2.val from ttt t1,( SELECT t.MIN + rownum - 1 val FROM (SELECT MIN(col2) MIN, MAX(col3) MAX FROM ttt) t CONNECT BY rownum BETWEEN t.MIN AND t.MAX) t2 where t2.val between t1.col2 and t1.col3 order by 1
SQL> with tt as 2 (select 'a' A,1 min,3 max from dual 3 union all 4 select 'b' A,2 min,3 max from dual 5 union all 6 select 'c' A,1 min,1 max from dual 7 ) 8 select distinct a,(tt.min + level-1) length from tt 9 connect by level<= (tt.max - tt.min)+1 10 order by a 11 /
(select 'a' A,1 min,3 max from dual
union all
select 'b' A,2 min,3 max from dual
union all
select 'c' A,1 min,1 max from dual
),
nn as
(select level lv from dual connect by level<100)
select tt.a, nn.lv
from tt, nn
where nn.lv between tt.min and tt.max
order by tt.a
UNION
SELECT A,MAX FROM Table
UNION
SELECT A,MIN+1 AS MIN FROM Table WHERE MIN<MAX
(select 'a' A,1 min,3 max from dual
union all
select 'b' A,2 min,3 max from dual
union all
select 'c' A,1 min,1 max from dual
),
nn as
(select level lv from dual connect by level<100)
select tt.a, nn.lv
from tt, nn
where nn.lv between tt.min and tt.max
order by tt.a”感觉一楼的正确
SELECT t.MIN + rownum - 1 val
FROM (SELECT MIN(col2) MIN, MAX(col3) MAX FROM ttt) t
CONNECT BY rownum BETWEEN t.MIN AND t.MAX) t2
where t2.val between t1.col2 and t1.col3
order by 1
2 (select 'a' A,1 min,3 max from dual
3 union all
4 select 'b' A,2 min,3 max from dual
5 union all
6 select 'c' A,1 min,1 max from dual
7 )
8 select distinct a,(tt.min + level-1) length from tt
9 connect by level<= (tt.max - tt.min)+1
10 order by a
11 /
A LENGTH
- ----------
a 1
a 2
a 3
b 2
b 3
c 1
6 rows selected