有一张表结构如下:
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表列管理更简单
- 有几种?各是什么呢?
- 问一个简单的问题,在线等
- 关于checkpoint
- 学习网上写的存储过程和函数,不知道该如何调用了,请各位高手帮忙看一下,需要用到数组.
- 执行函数,发生“字符串缓冲区太小”的错误,请求帮忙!
- sql语句,真心请教大家了!!!
- 请问哪儿有完整详细的SQL92,SQL99标准 的SQL语法?
- Oracle 9i太难安装了,太不友好了!谁能提供完整的安装和设置步骤给满分!
- 刚才的分数已经送出,还有一个问题!
- 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 ;
怎么提示违反唯一索引