关于Rack()自增长的一则双表关联更新

A表(tb_abc):                                     B表(tb_abcc):
    a     b     c                                    a     b
1 aa  0200    xx                                 1 aa (0201)
2 bb  0300    xx                                 2 aa (0202)
3 cc  0500    xx                                 3 bb (0301)
4 dd  1800    xx                                 4 bb (0302)
5 ee  2200    yy                                 5 bb (0303)
6 ff  3300    yy                                 6 cc (0501)

括号里是预期值

规则: 通过B表的a字段匹配A表的a字段,并读取A表的b字段,按照该值依次增数写入B表b字段
条件: A.c='xx'

[@more@]

实现:

代码:

---

如下：

SQL> select * from tb_abc;

A B---- ------ aa 0200
bb 0300
cc 0500
dd 1800

SQL> select * from tb_abcc;

A B---- ------aa
aa
bb
bb
bb
cc

6 rows selected

SQL> SQL> update tb_abcc c
2 set c.b = (select tmp.str
3 from (select b.rowid rd,4 b.a,5 substr(a.b, 1, 2) ||6 lpad((rank()7 over(partition by b.a order by b.rowid)),8 2,9 0) str
10 from tb_abc a, tb_abcc b
11 where a.a = b.a and a.c='xx') tmp
12 where c.rowid = tmp.rd)13 where exists
14 (select 'x'15 from (select b.rowid rd,16 b.a,17 substr(a.b, 1, 2) ||18 lpad((rank() over(partition by b.a order by b.rowid)),19 2,20 0) str
21 from tb_abc a, tb_abcc b
22 where a.a = b.a and a.c='xx') tmp
23 where c.rowid = tmp.rd);6 rows updated

SQL> select * from tb_abcc;

A B---- ------aa 0201
aa 0202
bb 0301
bb 0302
bb 0303
cc 0501

6 rows selected

SQL>

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

转载于:http://blog.itpub.net/13934116/viewspace-1016567/