有一张表结构如下:
create table DEPOSIT_ACCUMULATE_TEMP
(
ACNO VARCHAR2(40) not null,
DPNO VARCHAR2(20),
BAL NUMBER(16,2),
BALY NUMBER(16,2),
AMR NUMBER(16,2),
CRDID VARCHAR2(20),
CNAME VARCHAR2(80),
SUBJ VARCHAR2(10),
DATEB VARCHAR2(10),
STAT VARCHAR2(1),
FLAG VARCHAR2(10),
CUR VARCHAR2(10),
CLITYP VARCHAR2(1)
)表记录数据如下,现暂以竖直分开:
111|330112 |1.00|0.00|-15988.7|2000731539 |测试数据111 |20101|20101215|0|0|01|1|
111|330116 |2.00|0.00|-39475.35|2000731609 |测试数据1111|20101|20101215|0|0|01|1|
222|330118 |1.00|0.01|-180.0|2000735511 |测试数据222|20101|20101216|0|0|01|1|
333|330101 |1.00|0.01|0.0|2000731870|测试数据333 |20101|20101217|0|0|14|1|
333|330117 |2.00|0.02|-0.3|2000732893|测试数据333 |20101|20101220|0|0|01|1|
444|330116 |1.00|0.37|-328970.55|2000584977 |测试数据333|20101|20101220|0|0|01|1|
444|330107 |2.00|2.5|-142612.5|2000739461|测试数据444|20101|20101220|0|0|01|1|
444|330112 |3.00|221.74|-3326.1|2000523635|测试数据444|20101|20101220|0|0|01|1|
444|330112 |4.00|0.0|0.0|2000523635 |测试数据444|20101 |20101220|0|0|14|1|
555|330112 |1.00|0.0|0.0|2000523635 |测试数据555|20101|20101220|0|0|14|1|
666|330108 |1.00|0.0|0.0|2000733265 |测试数据666|20101 |20101220|0|0|01|1|
666|330111 |2.00|0.0|0.0|2000733412|测试数据666|20101 |20101221|0|0|01|1|现在想得到如下结果:111|330112 |3.00|0.00|-15988.7|2000731539 |测试数据111 |20101|20101215|0|0|01|1|
222|330118 |1.00|0.01|-180.0|2000735511 |测试数据222|20101|20101216|0|0|01|1|
333|330117 |3.00|0.02|-0.3|2000732893|测试数据333 |20101|20101220|0|0|01|1|
444|330112 |10.00|0.0|0.0|2000523635 |测试数据444|20101 |20101220|0|0|14|1|
555|330112 |1.00|0.0|0.0|2000523635 |测试数据555|20101|20101220|0|0|14|1|
666|330108 |3.00|0.0|0.0|2000733265 |测试数据666|20101 |20101220|0|0|01|1|
也就是把红色的余额字段按帐号进行汇总,其他字段属性就随便取一条就行!
要求速度快一点,原来表记录有150万条差不多
create table DEPOSIT_ACCUMULATE_TEMP
(
ACNO VARCHAR2(40) not null,
DPNO VARCHAR2(20),
BAL NUMBER(16,2),
BALY NUMBER(16,2),
AMR NUMBER(16,2),
CRDID VARCHAR2(20),
CNAME VARCHAR2(80),
SUBJ VARCHAR2(10),
DATEB VARCHAR2(10),
STAT VARCHAR2(1),
FLAG VARCHAR2(10),
CUR VARCHAR2(10),
CLITYP VARCHAR2(1)
)表记录数据如下,现暂以竖直分开:
111|330112 |1.00|0.00|-15988.7|2000731539 |测试数据111 |20101|20101215|0|0|01|1|
111|330116 |2.00|0.00|-39475.35|2000731609 |测试数据1111|20101|20101215|0|0|01|1|
222|330118 |1.00|0.01|-180.0|2000735511 |测试数据222|20101|20101216|0|0|01|1|
333|330101 |1.00|0.01|0.0|2000731870|测试数据333 |20101|20101217|0|0|14|1|
333|330117 |2.00|0.02|-0.3|2000732893|测试数据333 |20101|20101220|0|0|01|1|
444|330116 |1.00|0.37|-328970.55|2000584977 |测试数据333|20101|20101220|0|0|01|1|
444|330107 |2.00|2.5|-142612.5|2000739461|测试数据444|20101|20101220|0|0|01|1|
444|330112 |3.00|221.74|-3326.1|2000523635|测试数据444|20101|20101220|0|0|01|1|
444|330112 |4.00|0.0|0.0|2000523635 |测试数据444|20101 |20101220|0|0|14|1|
555|330112 |1.00|0.0|0.0|2000523635 |测试数据555|20101|20101220|0|0|14|1|
666|330108 |1.00|0.0|0.0|2000733265 |测试数据666|20101 |20101220|0|0|01|1|
666|330111 |2.00|0.0|0.0|2000733412|测试数据666|20101 |20101221|0|0|01|1|现在想得到如下结果:111|330112 |3.00|0.00|-15988.7|2000731539 |测试数据111 |20101|20101215|0|0|01|1|
222|330118 |1.00|0.01|-180.0|2000735511 |测试数据222|20101|20101216|0|0|01|1|
333|330117 |3.00|0.02|-0.3|2000732893|测试数据333 |20101|20101220|0|0|01|1|
444|330112 |10.00|0.0|0.0|2000523635 |测试数据444|20101 |20101220|0|0|14|1|
555|330112 |1.00|0.0|0.0|2000523635 |测试数据555|20101|20101220|0|0|14|1|
666|330108 |3.00|0.0|0.0|2000733265 |测试数据666|20101 |20101220|0|0|01|1|
也就是把红色的余额字段按帐号进行汇总,其他字段属性就随便取一条就行!
要求速度快一点,原来表记录有150万条差不多
解决方案 »
- oracle函数创建的小问题
- 修改程序连接数据库的连接方式
- 高难度SQL请教
- dbms_random包,在普通的plsql中可以使用,但是在我的package中引用就不行
- ora-00604错误,大家帮我解决下
- 紧急!PL/SQL中对查询结果关联显示的问题!!!
- 求一个SQL语句,困扰半天了
- 帮我看下 ,存储过程我这里执行出错阿.
- 在redhat4.6 64位系统上安装oracle10g报错,请各位帮忙看看
- 求和出错??!!在线等候!!!
- java.sql.SQLException: Listener refused the connection with the following error:
- ado 连接unix oracle 数据库
--可以,用分析函数就可以了
--试试这个 未测试
select * from
(select t.acno,
sum(bal) over(partition by acno) bal,
row_number() over(partition by acno order by bal) rn,
...
from DEPOSIT_ACCUMULATE_TEMP)
where rn=1
select a.*,b.bal from DEPOSIT_ACCUMULATE_TEMP a, (select ACNO ,sum(BAL) bal from DEPOSIT_ACCUMULATE_TEMP group by ACNO) b
where a.ACNO = b.ACNO ;
怎么提示违反唯一索引