# 用两种方法实现新增列的稠化功能

SQL>  SELECT * FROM st1;
A              START1       END1 D
---------- ---------- ---------- ----------
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
---------- ---------- ---------- ---------- ----------
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
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

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  /
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

SQL>

• 博文量
35
• 访问量
96615