现在有两张表一张是历史表info,记录有5000万条,一张临时表记录是100万条,表结构及模拟数据如下
历史表info的数据是
acno acdate acbal
62211 2010/01/01 200.00
62212 2010/01/01 300.0062211 2010/01/05 500.00
62212 2010/01/05 600.00
62213 2010/01/05 600.0062211 2010/01/06 700.00
62212 2010/01/06 800.0062213 2010/01/07 900.00假设今天是2010/01/08临时表数据是:
acno acbal
62211 700.00
62212 800.00
62213 1200.00
62214 400.00
62215 500.00要求把临时表中的数据插入历史表中,如果临时表的余额与历史表最新的余额相同不插入,不相同就插入
最后历史数据变成:acno acdate acbal
62211 2010/01/01 200.00
62212 2010/01/01 300.0062211 2010/01/05 500.00
62212 2010/01/05 600.00
62213 2010/01/05 600.0062211 2010/01/06 700.00
62212 2010/01/06 800.0062213 2010/01/07 900.0062213 2010/01/08 1200.00
62214 2010/01/08 400.00
62215 2010/01/08 500.00或者求另外一条SQL语句,先把临时表中的相同的余额数据删掉,变成:
acno acbal
62213 1200.00
62214 400.00
62215 500.00然后插入历史表中也不知道这两种哪个更有效率,再求这样两条语句!!!!!请大家一定要帮忙一下啊:eek:
历史表info的数据是
acno acdate acbal
62211 2010/01/01 200.00
62212 2010/01/01 300.0062211 2010/01/05 500.00
62212 2010/01/05 600.00
62213 2010/01/05 600.0062211 2010/01/06 700.00
62212 2010/01/06 800.0062213 2010/01/07 900.00假设今天是2010/01/08临时表数据是:
acno acbal
62211 700.00
62212 800.00
62213 1200.00
62214 400.00
62215 500.00要求把临时表中的数据插入历史表中,如果临时表的余额与历史表最新的余额相同不插入,不相同就插入
最后历史数据变成:acno acdate acbal
62211 2010/01/01 200.00
62212 2010/01/01 300.0062211 2010/01/05 500.00
62212 2010/01/05 600.00
62213 2010/01/05 600.0062211 2010/01/06 700.00
62212 2010/01/06 800.0062213 2010/01/07 900.0062213 2010/01/08 1200.00
62214 2010/01/08 400.00
62215 2010/01/08 500.00或者求另外一条SQL语句,先把临时表中的相同的余额数据删掉,变成:
acno acbal
62213 1200.00
62214 400.00
62215 500.00然后插入历史表中也不知道这两种哪个更有效率,再求这样两条语句!!!!!请大家一定要帮忙一下啊:eek:
解决方案 »
- java程序界面验证用户登录,连接oracle数据库查询用户名及密码,登录时老是说用户名不对
- 那位有oracle8和oracle9的安装包
- oracle 中怎么用一条update 语句同时 更新两个表
- 揭开CDP面纱!
- 写一个oracle分页存储过程遇到的问题,项目中要用到,请高手指教!
- oracle 9i 群集问题,两台服务器+磁盘阵列?
- 大公司大项目的数据库表格文档怎么管理?
- TOAD安装时的问题
- 这几个LINUX下的命令怎么转换成WINDOWS命令??
- 请问有谁知道google的后台数据库是用什么数据库?
- 特难SQL 跪求告诉,急,求高手解答
- XMLP 做EBS报表的时候报了一个莫名奇妙的错
--1.临时表中的数据插入历史表中
merge into hisinfo a
using (select acno, acbal
from tmp b
)c
on(a.acno = c.acno and a.acbal = c.acbal)
when not matched then
update set a.acno = c.acno,
a.acdate = to_char(sysdate,'yyyy/mm/dd')
a.acbal = c.acbal;--2.把临时表中的相同的余额数据删掉
delete from tmp a where exists(select 1 from hisinfo b where b.acno = a.acno and b.acbal = a.acbal);
merge into hisinfo a
using (select acno, acbal
from tmp b
)c
on(a.acno = c.acno and a.acbal = c.acbal)
when not matched then
update set a.acno = c.acno,
a.acdate = to_char(sysdate,'yyyy/mm/dd'),
a.acbal = c.acbal;
-- drop table info_temp purge;create table info(
acno number(18,0),
acdate date,
acbal number(18,2)
);
insert into info(acno,acdate,acbal) values(62211, to_date('2010/01/01','YYYY/MM/DD'), 200.00);
insert into info(acno,acdate,acbal) values(62212, to_date('2010/01/01','YYYY/MM/DD'), 300.00);
insert into info(acno,acdate,acbal) values(62211, to_date('2010/01/05','YYYY/MM/DD'), 500.00);
insert into info(acno,acdate,acbal) values(62212, to_date('2010/01/05','YYYY/MM/DD'), 600.00);
insert into info(acno,acdate,acbal) values(62213, to_date('2010/01/05','YYYY/MM/DD'), 600.00);
insert into info(acno,acdate,acbal) values(62211, to_date('2010/01/06','YYYY/MM/DD'), 700.00);
insert into info(acno,acdate,acbal) values(62212, to_date('2010/01/06','YYYY/MM/DD'), 800.00);
insert into info(acno,acdate,acbal) values(62213, to_date('2010/01/07','YYYY/MM/DD'), 900.00);
COMMIT;create table info_temp
(
acno number(18,0),
acbal number(18,2)
);
insert into info_temp(acno,acbal) values(62211, 700.00);
insert into info_temp(acno,acbal) values(62212, 800.00);
insert into info_temp(acno,acbal) values(62213, 1200.00);
insert into info_temp(acno,acbal) values(62214, 400.00);
insert into info_temp(acno,acbal) values(62215, 500.00);
commit;-- 5000万数据的表,是使用的分区表吗?应该用分区表才对啊!否则其操作效率会相当低的!insert into info
select t1.acno, to_date('2010-01-08','yyyy-mm-dd') as acdate, t1.acbal
from info_temp t1
where not exists (select 1
from info t2
where t2.acno=t1.acno
and t2.acdate<to_date('2010-01-08','yyyy-mm-dd')-- 先删除
delete from info_temp t1
where exists (select t2.acno, t2.acdate, t2.acbal
from info t2
where exists (select 1
from info t3
where t3.acno=t2.acno
and t3.acdate<to_date('2010-01-08','yyyy-mm-dd')
group by t3.acno
having max(t3.acdate)=t2.acdate)
and t2.acno=t1.acno
and t2.acbal=t1.acbal );-- 再插入
insert into info
select t1.acno, to_date('2010-01-08','yyyy-mm-dd') as acdate, t1.acbal
from info_temp t1;-- 建议1:如果info表不是分区表,最好先将其转换成按时间分区(例如:按月分区)的分区表!
-- 建议2:可以在info表的acno和acdate字段创建索引;在info_temp表的acno字段创建索引!
delete from tmp a where exists(select 1 from hisinfo b where b.acno = a.acno and b.acbal = a.acbal) grop by b.acdate having a.acdate=max(b.orcdate))
说难听的,还真不知道什么是分区表
-- drop table info_temp purge;create table info(
acno number(18,0),
acdate date,
acbal number(18,2)
);
insert into info(acno,acdate,acbal) values(62211, to_date('2010/01/01','YYYY/MM/DD'), 200.00);
insert into info(acno,acdate,acbal) values(62212, to_date('2010/01/01','YYYY/MM/DD'), 300.00);
insert into info(acno,acdate,acbal) values(62211, to_date('2010/01/05','YYYY/MM/DD'), 500.00);
insert into info(acno,acdate,acbal) values(62212, to_date('2010/01/05','YYYY/MM/DD'), 600.00);
insert into info(acno,acdate,acbal) values(62213, to_date('2010/01/05','YYYY/MM/DD'), 600.00);
insert into info(acno,acdate,acbal) values(62211, to_date('2010/01/06','YYYY/MM/DD'), 700.00);
insert into info(acno,acdate,acbal) values(62212, to_date('2010/01/06','YYYY/MM/DD'), 800.00);
insert into info(acno,acdate,acbal) values(62213, to_date('2010/01/07','YYYY/MM/DD'), 900.00);
COMMIT;create table info_temp
(
acno number(18,0),
acbal number(18,2)
);
insert into info_temp(acno,acbal) values(62211, 700.00);
insert into info_temp(acno,acbal) values(62212, 800.00);
insert into info_temp(acno,acbal) values(62213, 1200.00);
insert into info_temp(acno,acbal) values(62214, 400.00);
insert into info_temp(acno,acbal) values(62215, 500.00);
commit;-- 5000万数据的表,是使用的分区表吗?应该用分区表才对啊!否则其操作效率会相当低的!-- 先删除
delete from info_temp t1
where exists (select t2.acno, t2.acdate, t2.acbal
from info t2
where exists (select 1
from info t3
where t3.acno=t2.acno
and t3.acdate<to_date('2010-01-08','yyyy-mm-dd')
group by t3.acno
having max(t3.acdate)=t2.acdate)
and t2.acno=t1.acno
and t2.acbal=t1.acbal );-- 再插入
insert into info
select t1.acno, to_date('2010-01-08','yyyy-mm-dd') as acdate, t1.acbal
from info_temp t1;
eygle@SZTYORA> create table info(
2 acno number(18,0),
3 acdate date,
4 acbal number(18,2)
5 );表已创建。eygle@SZTYORA> insert into info(acno,acdate,acbal) values(62211, to_date('2010/01/01','YYYY/MM/DD'), 200.00);已创建 1 行。eygle@SZTYORA> insert into info(acno,acdate,acbal) values(62212, to_date('2010/01/01','YYYY/MM/DD'), 300.00);已创建 1 行。eygle@SZTYORA> insert into info(acno,acdate,acbal) values(62211, to_date('2010/01/05','YYYY/MM/DD'), 500.00);已创建 1 行。eygle@SZTYORA> insert into info(acno,acdate,acbal) values(62212, to_date('2010/01/05','YYYY/MM/DD'), 600.00);已创建 1 行。eygle@SZTYORA> insert into info(acno,acdate,acbal) values(62213, to_date('2010/01/05','YYYY/MM/DD'), 600.00);已创建 1 行。eygle@SZTYORA> insert into info(acno,acdate,acbal) values(62211, to_date('2010/01/06','YYYY/MM/DD'), 700.00);已创建 1 行。eygle@SZTYORA> insert into info(acno,acdate,acbal) values(62212, to_date('2010/01/06','YYYY/MM/DD'), 800.00);已创建 1 行。eygle@SZTYORA> insert into info(acno,acdate,acbal) values(62213, to_date('2010/01/07','YYYY/MM/DD'), 900.00);已创建 1 行。eygle@SZTYORA> COMMIT;提交完成。eygle@SZTYORA>
eygle@SZTYORA> create table info_temp
2 (
3 acno number(18,0),
4 acbal number(18,2)
5 );表已创建。eygle@SZTYORA> insert into info_temp(acno,acbal) values(62211, 700.00);已创建 1 行。eygle@SZTYORA> insert into info_temp(acno,acbal) values(62212, 800.00);已创建 1 行。eygle@SZTYORA> insert into info_temp(acno,acbal) values(62213, 1200.00);已创建 1 行。eygle@SZTYORA> insert into info_temp(acno,acbal) values(62214, 400.00);已创建 1 行。eygle@SZTYORA> insert into info_temp(acno,acbal) values(62215, 500.00);已创建 1 行。eygle@SZTYORA> commit;提交完成。eygle@SZTYORA> delete from info_temp t1
2 where exists (select t2.acno, t2.acdate, t2.acbal
3 from info t2
4 where exists (select 1
5 from info t3
6 where t3.acno=t2.acno
7 and t3.acdate<to_date('2010-01-08','yyyy-mm-dd')
8 group by t3.acno
9 having max(t3.acdate)=t2.acdate)
10 and t2.acno=t1.acno
11 and t2.acbal=t1.acbal );已删除2行。eygle@SZTYORA> select * from info_temp; ACNO ACBAL
---------- ----------
62213 1200
62214 400
62215 500eygle@SZTYORA> insert into info
2 select t1.acno, to_date('2010-01-08','yyyy-mm-dd') as acdate, t1.acbal
3 from info_temp t1;已创建3行。eygle@SZTYORA> select * from info; ACNO ACDATE ACBAL
---------- ------------------- ----------
62211 2010-01-01 00:00:00 200
62212 2010-01-01 00:00:00 300
62211 2010-01-05 00:00:00 500
62212 2010-01-05 00:00:00 600
62213 2010-01-05 00:00:00 600
62211 2010-01-06 00:00:00 700
62212 2010-01-06 00:00:00 800
62213 2010-01-07 00:00:00 900
62213 2010-01-08 00:00:00 1200
62214 2010-01-08 00:00:00 400
62215 2010-01-08 00:00:00 500已选择11行。