insert into gl_rms_lagtest_tv ( data_date, ... (字段很多,这里不全部列出来了) counterparty) select t.data_date, ... t.counterparty , a.trade_version FROM v_brz_rms_exodus_pipe t, v_brz_rms_exodus_pipe a where t.system_id = a.system_id and a.trade_version = (select max(trade_version) from v_brz_rms_exodus_pipe b where b.system_id = a.system_id)insert into gl_rms_lagtest_tv ( data_date, ... (字段很多,这里不全部列出来了) counterparty) select t.data_date, ... t.counterparty , a.trade_version FROM v_brz_rms_exodus_pipe t, v_brz_rms_exodus_pipe a where t.system_id = a.system_id and not exists (select 1 from v_brz_rms_exodus_pipe b where b.system_id = a.system_id and b.trade_version > a.trade_version)
不知道你是否需要更换为如下: insert into gl_rms_lagtest_tv ( data_date, ... (字段很多,这里不全部列出来了) counterparty) select t.data_date, ... t.counterparty , a.trade_version FROM v_brz_rms_exodus_pipe t, v_brz_rms_exodus_pipe a where t.system_id = a.system_id and t.trade_version = a.trade_version and a.trade_version = (select max(trade_version) from v_brz_rms_exodus_pipe b where b.system_id = a.system_id)insert into gl_rms_lagtest_tv ( data_date, ... (字段很多,这里不全部列出来了) counterparty) select t.data_date, ... t.counterparty , a.trade_version FROM v_brz_rms_exodus_pipe t, v_brz_rms_exodus_pipe a where t.system_id = a.system_id and t.trade_version = a.trade_version not exists (select 1 from v_brz_rms_exodus_pipe b where b.system_id = a.system_id and b.trade_version > a.trade_version)
我看不懂你的SQL,不过估计是要求最大列. 我写个大概,你自己参考更改.假设A,B表,通过ID连接,去B表对应ID的最大时间列date所在行的数据.select a.* , t.* from a , b t where a.id = t.id and t.date = (select max(date) from b where id = t.id)select a.* , t.* from a , b t where a.id = t.id and not exists (select 1 from b where id = t.id and date > t.date)--如果是多个字段关联,例如:a.id1 = t.id1 , a.id2 = t.id2 则如下: select a.* , t.* from a , b t where a.id1 = t.id1 and a.id2 = t.id2 and t.date = (select max(date) from b where id1 = t.id1 and id2 = t.id2)select a.* , t.* from a , b t where a.id1 = t.id1 and a.id2 = t.id2 and not exists (select 1 from b where id1 = t.id1 and id2 = t.id2 and date > t.date)
你这个SQL我运行了,比我的SQL还费时间.怎么办???? 求解决.
sql1:INSERT INTO gl_rms_lagtest_tv (data_date, counterparty) SELECT m.data_date, m.counterparty FROM ( SELECT t.data_date, t.counterparty, RANK () OVER(PARTITION BY t.system_id ORDER BY t.trade_version DESC) rn FROM v_brz_rms_exodus_pipe t ) m WHERE m.rn = 1
insert into gl_rms_lagtest_tv ( data_date, ... (字段很多,这里不全部列出来了) counterparty, trade_version ) select t.data_date, ...(字段很多,这里不全部列出来了) t.counterparty, t.trade_version FROM ( select a.data_date, ... (字段很多,这里不全部列出来了) a.system_id, ...(字段很多,这里不全部列出来了) a.counterparty, max(a.trade_version) over(partition by a.system_id order by trade_version) as trade_version, count(a.system_id) over(partition by a.system_id order by trade_version) as rn, row_number() over(order by a.system_id ) as rm, from v_brz_rms_exodus_pipe a ) t where rn = rm;
1、首先建立会话临时表保存DWH_GFIS_ACC_ENTRIES表中有用的数据。 create global temporary table dwh_gfis_acc_entries_temp ( ...., ...., .... ) on commit preserve rows;
2、向临时表插入DWH_GFIS_ACC_ENTRIES表中有效的数据
insert into dwh_gfis_acc_entries_temp ( xxxxx, xxxx, xxxxx, xxxxxx, xxxxx, .... ) select xxxxx, xxxx, xxxxx, xxxxxx, xxxxx, .... from DWH_GFIS_ACC_ENTRIES a where a.src_system = 'ACSF' and a.src_system_ac_number like '042%' and a.module = 'AGE' and a.linkage_level = 'CO' commit;
3、再建立3个会话临时表保存gl_rms_lagtest_tv表中有用的数据。 create global temporary gl_rms_lagtest_tv_temp ( ...., ...., .... ) on commit preserve rows;
create global temporary gl_rms_lagtest_tv_temp1 ( ...., ...., .... ) on commit preserve rows;
create global temporary gl_rms_lagtest_tv_temp2 ( ...., ...., .... ) on commit preserve rows;
4、左连接插入数据 insert into gl_rms_lagtest_tv_temp ( ...., ...., .... ) select tb1.xxx, tbl.xxxx, ..... nvl(tb1.high_level_cpty_cdrmnemonic,tb1.cpty_cdrmnemonic) as tb1_cpty_cdr from gl_rms_lagtest_tv tb1; commit;
insert into gl_rms_lagtest_tv_temp1 ( ...., ...., ...., loccpyid ) select a.xxx, a.xxxx, ..... tb2.loccpyid from gl_rms_lagtest_tv_temp a left join v_brz_idms_paragon_feed tbl2 on a.ami_code = tb2.le_code and a.tb1_cpty_cdr = tb2.cpty_cdr ; commit;
insert into gl_rms_lagtest_tv_temp2 ( ...., ...., ...., master_num ) select b.xxx, b.xxxx, ..... to_char() nvl(b.loccpyid,tb3.master_number) as master_num from gl_rms_lagtest_tv_temp1 b left join mdr_cust tb3 on b.ami_code = tb3.ami_code and b.counterparty = tb3.cust; commit;
我在加班,明天这些存储过程都要上UAT了,目前正在修改中,如果有
高手在,请一定给点帮助.
data_date,
... (字段很多,这里不全部列出来了)
counterparty)
select t.data_date,
...
t.counterparty , a.trade_version
FROM v_brz_rms_exodus_pipe t, v_brz_rms_exodus_pipe a
where t.system_id = a.system_id and a.trade_version = (select max(trade_version) from v_brz_rms_exodus_pipe b where b.system_id = a.system_id)insert into gl_rms_lagtest_tv (
data_date,
... (字段很多,这里不全部列出来了)
counterparty)
select t.data_date,
...
t.counterparty , a.trade_version
FROM v_brz_rms_exodus_pipe t, v_brz_rms_exodus_pipe a
where t.system_id = a.system_id and not exists (select 1 from v_brz_rms_exodus_pipe b where b.system_id = a.system_id and b.trade_version > a.trade_version)
insert into gl_rms_lagtest_tv (
data_date,
... (字段很多,这里不全部列出来了)
counterparty)
select t.data_date,
...
t.counterparty , a.trade_version
FROM v_brz_rms_exodus_pipe t, v_brz_rms_exodus_pipe a
where t.system_id = a.system_id and t.trade_version = a.trade_version and a.trade_version = (select max(trade_version) from v_brz_rms_exodus_pipe b where b.system_id = a.system_id)insert into gl_rms_lagtest_tv (
data_date,
... (字段很多,这里不全部列出来了)
counterparty)
select t.data_date,
...
t.counterparty , a.trade_version
FROM v_brz_rms_exodus_pipe t, v_brz_rms_exodus_pipe a
where t.system_id = a.system_id and t.trade_version = a.trade_version not exists (select 1 from v_brz_rms_exodus_pipe b where b.system_id = a.system_id and b.trade_version > a.trade_version)
我写个大概,你自己参考更改.假设A,B表,通过ID连接,去B表对应ID的最大时间列date所在行的数据.select a.* , t.* from a , b t where a.id = t.id and t.date = (select max(date) from b where id = t.id)select a.* , t.* from a , b t where a.id = t.id and not exists (select 1 from b where id = t.id and date > t.date)--如果是多个字段关联,例如:a.id1 = t.id1 , a.id2 = t.id2
则如下:
select a.* , t.* from a , b t where a.id1 = t.id1 and a.id2 = t.id2 and t.date = (select max(date) from b where id1 = t.id1 and id2 = t.id2)select a.* , t.* from a , b t where a.id1 = t.id1 and a.id2 = t.id2 and not exists (select 1 from b where id1 = t.id1 and id2 = t.id2 and date > t.date)
你这个SQL我运行了,比我的SQL还费时间.怎么办???? 求解决.
(data_date,
counterparty)
SELECT m.data_date,
m.counterparty
FROM (
SELECT t.data_date,
t.counterparty,
RANK () OVER(PARTITION BY t.system_id ORDER BY t.trade_version DESC) rn
FROM v_brz_rms_exodus_pipe t
) m
WHERE m.rn = 1
insert into gl_rms_lagtest_tv
(
data_date,
... (字段很多,这里不全部列出来了)
counterparty,
trade_version
)
select t.data_date,
...(字段很多,这里不全部列出来了)
t.counterparty,
t.trade_version
FROM
(
select a.data_date,
... (字段很多,这里不全部列出来了)
a.system_id,
...(字段很多,这里不全部列出来了)
a.counterparty,
max(a.trade_version) over(partition by a.system_id order by trade_version) as trade_version,
count(a.system_id) over(partition by a.system_id order by trade_version) as rn,
row_number() over(order by a.system_id ) as rm,
from v_brz_rms_exodus_pipe a
) t
where rn = rm;
1、首先建立会话临时表保存DWH_GFIS_ACC_ENTRIES表中有用的数据。
create global temporary table dwh_gfis_acc_entries_temp
(
....,
....,
....
)
on commit preserve rows;
2、向临时表插入DWH_GFIS_ACC_ENTRIES表中有效的数据
insert into dwh_gfis_acc_entries_temp
(
xxxxx,
xxxx,
xxxxx,
xxxxxx,
xxxxx,
....
)
select xxxxx,
xxxx,
xxxxx,
xxxxxx,
xxxxx,
....
from DWH_GFIS_ACC_ENTRIES a
where a.src_system = 'ACSF'
and a.src_system_ac_number like '042%'
and a.module = 'AGE'
and a.linkage_level = 'CO'
commit;
3、再建立3个会话临时表保存gl_rms_lagtest_tv表中有用的数据。
create global temporary gl_rms_lagtest_tv_temp
(
....,
....,
....
)
on commit preserve rows;
create global temporary gl_rms_lagtest_tv_temp1
(
....,
....,
....
)
on commit preserve rows;
create global temporary gl_rms_lagtest_tv_temp2
(
....,
....,
....
)
on commit preserve rows;
4、左连接插入数据
insert into gl_rms_lagtest_tv_temp
(
....,
....,
....
)
select tb1.xxx,
tbl.xxxx,
.....
nvl(tb1.high_level_cpty_cdrmnemonic,tb1.cpty_cdrmnemonic) as tb1_cpty_cdr
from gl_rms_lagtest_tv tb1;
commit;
insert into gl_rms_lagtest_tv_temp1
(
....,
....,
....,
loccpyid
)
select a.xxx,
a.xxxx,
.....
tb2.loccpyid
from gl_rms_lagtest_tv_temp a
left join v_brz_idms_paragon_feed tbl2
on a.ami_code = tb2.le_code
and a.tb1_cpty_cdr = tb2.cpty_cdr ;
commit;
insert into gl_rms_lagtest_tv_temp2
(
....,
....,
....,
master_num
)
select b.xxx,
b.xxxx,
.....
to_char()
nvl(b.loccpyid,tb3.master_number) as master_num
from gl_rms_lagtest_tv_temp1 b
left join mdr_cust tb3
on b.ami_code = tb3.ami_code
and b.counterparty = tb3.cust;
commit;
execute immediate 'truncate table gl_rms_lagtest_tv_temp';
execute immediate 'truncate table gl_rms_lagtest_tv_temp1';
insert into gl_fx_gfis_rs1
(
exercised_from_src_sys_id,
data_date,
effective_date,
...
counterparty
)
select t2.exercised_from_src_sys_id,
...
t2.trans_id,
...
t2.counterparty
from dwh_gfis_acc_entries_temp t1
left join gl_rms_lagtest_tv_temp2 t2
on t1.linkage_id = to_char(t2.system_id) ;
commit;