ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 用两种方法实现新增列的稠化功能

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

原创 Linux操作系统 作者:0x0x0x 时间:2012-06-26 17:07:13 0 删除 编辑
我有以下这样一张表:
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
已选择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  /
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
已选择15行。
已用时间:  00: 00: 00.04
SQL>

点评:方法一比较容易理解,方法二效率比较好!

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/26977915/viewspace-733878/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2012-06-26

  • 博文量
    35
  • 访问量
    91839