表Act
Act_id A B C
01 7 1 3
02 2 1 2
03 4 2 1表pro_act
是inset into pro_act select A_sequence.nextval,act_id, act.a,act.b from act where act.b=1 插入多次得到的.Pro_act_ID ACT_ID A B
04 01 7 1
05 02 2 1
06 01 7 1
07 02 2 1
表rea
Rea.Act_id对应act.act_id,但是每个数都是重复过的.
Rea_id Act_id a b
08 01 w hf
09 01 ad H1
10 02 fg Da1
11 02 cv ada
现在,想得到这样一张表 表Pro_reaPro_rea_id Act_id Pro_act_id a b
14 01 04 w hf
15 01 04 ad H1
16 02 05 fg Da1
17 02 05 cv adaPro_rea也是从rea选取数据插入到pro_rea产生的.
想问,怎么才能正确的取到pro_act_id的值,pro_act_id是用序列产生的,并且,取到值要是最近一次插入到pro_act中的,比如06、07,而不是04、05.
而且pro_act_id在pro_act表中和对应的act_id 的值在rea.act_id是存在的.先谢谢各位高手了.
Act_id A B C
01 7 1 3
02 2 1 2
03 4 2 1表pro_act
是inset into pro_act select A_sequence.nextval,act_id, act.a,act.b from act where act.b=1 插入多次得到的.Pro_act_ID ACT_ID A B
04 01 7 1
05 02 2 1
06 01 7 1
07 02 2 1
表rea
Rea.Act_id对应act.act_id,但是每个数都是重复过的.
Rea_id Act_id a b
08 01 w hf
09 01 ad H1
10 02 fg Da1
11 02 cv ada
现在,想得到这样一张表 表Pro_reaPro_rea_id Act_id Pro_act_id a b
14 01 04 w hf
15 01 04 ad H1
16 02 05 fg Da1
17 02 05 cv adaPro_rea也是从rea选取数据插入到pro_rea产生的.
想问,怎么才能正确的取到pro_act_id的值,pro_act_id是用序列产生的,并且,取到值要是最近一次插入到pro_act中的,比如06、07,而不是04、05.
而且pro_act_id在pro_act表中和对应的act_id 的值在rea.act_id是存在的.先谢谢各位高手了.
select Pro_act_ID,ACT_ID,A,B
from (
select a.*,row_number() over(partition by ACT_ID,A,B order by Pro_act_ID desc ) rn
from pro_act a)
where rn=1
SELECT seq_pro_act_id.NEXTVAL, b.act_id, b.pro_act_id, a.a, a.b
FROM rea a,
(SELECT pro_act_id, act_id, a, b
FROM (SELECT a.*,
ROW_NUMBER () OVER (PARTITION BY act_id, a, b ORDER BY pro_act_id DESC)
rn
FROM pro_act a)
WHERE rn = 1) b
WHERE a.act_id = b.act_id
SQL> SELECT SEQ_PRO_REA_ID.NEXTVAL,
2 RE.ACT_ID,
3 PRO_ACT_ID,
4 PRO_ACT.A,
5 PRO_ACT.B
6 FROM (SELECT MAX(PRO_ACT_ID) PRO_ACT_ID,
7 ACT_ID,A,B
8 FROM REA
9 GROUP BY ACT_ID,A,B)RE,
10 PRO_ACT
11 WHERE RE.ACT_ID = PRO_ACT.ACT_ID;REA_ID ACT_ID PRO_ACT_ID A B
------ ------ ---------- -- ---
14 01 06 W HF
15 01 06 AD H1
16 02 07 FG DA1
17 02 07 CV ADA
SQL> SELECT SEQ_PRO_REA_ID.NEXTVAL,
2 RE.ACT_ID,
3 PRO_ACT_ID,
4 PRO_ACT.A,
5 PRO_ACT.B
6 FROM (SELECT MAX(PRO_ACT_ID) PRO_ACT_ID,
7 ACT_ID,A,B
8 FROM REA
9 GROUP BY ACT_ID,A,B)RE,
10 PRO_ACT
11 WHERE RE.ACT_ID = PRO_ACT.ACT_ID;REA_ID ACT_ID PRO_ACT_ID A B
------ ------ ---------- -- ---
14 01 06 W HF
15 01 06 AD H1
16 02 07 FG DA1
17 02 07 CV ADA
(Act_id, Pro_act_id, a, b)
select r.rea_id, b.pro_act_id, r.a, r.b
from rea r,
(select a.*,
row_number() over(partition by act_id, a, b order by pro_act_id desc) rn
from pro_act a) b
where r.act_id = b.act_id
and b.rn = 1
下面是测试数据
SQL> select r.rea_id, b.pro_act_id, r.a, r.b
2 from rea r,
3 (select a.*,
4 row_number() over(partition by act_id, a, b order by pro_act_id desc) rn
5 from pro_act a) b
6 where r.act_id = b.act_id
7 and b.rn = 1
8 ; REA_ID PRO_ACT_ID A B
---------- ---------- ---------- ----------
9 6 ad h1
8 6 w hf
11 7 cv ada
10 7 fg da1SQL>
insert into Pro_rea
(Act_id, Pro_act_id, a, b)
select b.act_id,b.pro_act_id, r.a, r.b
from rea r,
(select a.*,
row_number() over(partition by act_id, a, b order by pro_act_id desc) rn
from pro_act a) b
where r.act_id = b.act_id
and b.rn = 1
SQL> select b.act_id,b.pro_act_id, r.a, r.b
2 from rea r,
3 (select a.*,
4 row_number() over(partition by act_id, a, b order by pro_act_id desc) rn
5 from pro_act a) b
6 where r.act_id = b.act_id
7 and b.rn = 1
8 ; ACT_ID PRO_ACT_ID A B
---------- ---------- ---------- ----------
1 6 ad h1
1 6 w hf
2 7 cv ada
2 7 fg da1