我有以下这样一张表：
SQL> SELECT * FROM st1;
SQL> SELECT * FROM st1;
A START1 END1 D
---------- ---------- ---------- ----------
USA 2 10 8
CHINA 7 12 5
---------- ---------- ---------- ----------
USA 2 10 8
CHINA 7 12 5
现在我想实现一个稠化功能，得到如下的结果
A START1 END1 D RN
---------- ---------- ---------- ---------- ----------
CHINA 7 12 5 7
CHINA 7 12 5 8
CHINA 7 12 5 9
CHINA 7 12 5 10
CHINA 7 12 5 11
CHINA 7 12 5 12
USA 2 10 8 2
USA 2 10 8 3
USA 2 10 8 4
USA 2 10 8 5
USA 2 10 8 6
A START1 END1 D RN
---------- ---------- ---------- ---------- ----------
CHINA 7 12 5 7
CHINA 7 12 5 8
CHINA 7 12 5 9
CHINA 7 12 5 10
CHINA 7 12 5 11
CHINA 7 12 5 12
USA 2 10 8 2
USA 2 10 8 3
USA 2 10 8 4
USA 2 10 8 5
USA 2 10 8 6
A START1 END1 D RN
---------- ---------- ---------- ---------- ----------
USA 2 10 8 7
USA 2 10 8 8
USA 2 10 8 9
USA 2 10 8 10
---------- ---------- ---------- ---------- ----------
USA 2 10 8 7
USA 2 10 8 8
USA 2 10 8 9
USA 2 10 8 10
方法一：构造一个序列，然后左外连接；
SQL> WITH t AS
2 (SELECT rownum rn FROM dual CONNECT BY rownum<=
3 (SELECT MAX(st1.D)+1 FROM st1))
4 SELECT st1.A,st1.start1,st1.end1,st1.D,st1.start1+t.rn-1 n
5 FROM st1,t WHERE t.rn<=st1.D+1
6 ORDER BY st1.A,rn
7 /
A START1 END1 D N
---------- ---------- ---------- ---------- ----------
CHINA 7 12 5 7
CHINA 7 12 5 8
CHINA 7 12 5 9
CHINA 7 12 5 10
CHINA 7 12 5 11
CHINA 7 12 5 12
USA 2 10 8 2
USA 2 10 8 3
USA 2 10 8 4
USA 2 10 8 5
USA 2 10 8 6
---------- ---------- ---------- ---------- ----------
CHINA 7 12 5 7
CHINA 7 12 5 8
CHINA 7 12 5 9
CHINA 7 12 5 10
CHINA 7 12 5 11
CHINA 7 12 5 12
USA 2 10 8 2
USA 2 10 8 3
USA 2 10 8 4
USA 2 10 8 5
USA 2 10 8 6
A START1 END1 D N
---------- ---------- ---------- ---------- ----------
USA 2 10 8 7
USA 2 10 8 8
USA 2 10 8 9
USA 2 10 8 10
---------- ---------- ---------- ---------- ----------
USA 2 10 8 7
USA 2 10 8 8
USA 2 10 8 9
USA 2 10 8 10
已选择15行。
已用时间: 00: 00: 00.05
方法二：用partition join 的方法
SQL> WITH t AS
2 (SELECT rownum rn FROM dual CONNECT BY rownum<=
3 (SELECT MAX(end1) FROM st1))
4 SELECT tt.*,t.rn FROM t LEFT JOIN st1 tt
5 partition BY (tt.a,tt.d,tt.start1,tt.end1)
6 ON 1=1
7 WHERE t.rn BETWEEN tt.start1 AND tt.end1
8 /
2 (SELECT rownum rn FROM dual CONNECT BY rownum<=
3 (SELECT MAX(end1) FROM st1))
4 SELECT tt.*,t.rn FROM t LEFT JOIN st1 tt
5 partition BY (tt.a,tt.d,tt.start1,tt.end1)
6 ON 1=1
7 WHERE t.rn BETWEEN tt.start1 AND tt.end1
8 /
A START1 END1 D RN
---------- ---------- ---------- ---------- ----------
CHINA 7 12 5 7
CHINA 7 12 5 8
CHINA 7 12 5 9
CHINA 7 12 5 10
CHINA 7 12 5 11
CHINA 7 12 5 12
USA 2 10 8 2
USA 2 10 8 3
USA 2 10 8 4
USA 2 10 8 5
USA 2 10 8 6
---------- ---------- ---------- ---------- ----------
CHINA 7 12 5 7
CHINA 7 12 5 8
CHINA 7 12 5 9
CHINA 7 12 5 10
CHINA 7 12 5 11
CHINA 7 12 5 12
USA 2 10 8 2
USA 2 10 8 3
USA 2 10 8 4
USA 2 10 8 5
USA 2 10 8 6
A START1 END1 D RN
---------- ---------- ---------- ---------- ----------
USA 2 10 8 7
USA 2 10 8 8
USA 2 10 8 9
USA 2 10 8 10
---------- ---------- ---------- ---------- ----------
USA 2 10 8 7
USA 2 10 8 8
USA 2 10 8 9
USA 2 10 8 10
已选择15行。
已用时间: 00: 00: 00.04
SQL>
SQL>
点评：方法一比较容易理解，方法二效率比较好！
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/26977915/viewspace-733878/ ，如需转载，请注明出处，否则将追究法律责任。
请登录后发表评论
登录
全部评论
