表结构及数据如下:
ADDRESS CUSTOMER DRDL01 IMSRP1 Amount
1 10003051 ALPHA START LIMITED POLY COVERED CORE PC 1425501.63
2 10003051 ALPHA START LIMITED SPUN POLYESTER SP 404370.41
3 10003051 ALPHA START LIMITED TRILOBAL AIR ENTANGLED POLY TL 9700
4 10003051 ALPHA START LIMITED SAK COTTON CS 38.3
5 10003051 ALPHA START LIMITED FILAMENT POLYESTER FP 3960
6 10003051 ALPHA START LIMITED COTTON COVERED CORE CC 3715.84
7 10000373 AMANN ASIA LIMITED POLY COVERED CORE PC 5246563.21
8 10000373 AMANN ASIA LIMITED TEXTURED POLYESTER TP 693569.06
9 10000373 AMANN ASIA LIMITED SPUN POLYESTER SP 39873.6结果为下面才是对的:
1 10003051 ALPHA START LIMITED POLY COVERED CORE PC 1425501.63
2 10003051 ALPHA START LIMITED SPUN POLYESTER SP 404370.41
3 10003051 ALPHA START LIMITED Others Others 17414.14
4 10000373 AMANN ASIA LIMITED POLY COVERED CORE PC 5246563.21
5 10000373 AMANN ASIA LIMITED TEXTURED POLYESTER TP 693569.06
6 10000373 AMANN ASIA LIMITED Others Others 39873.6脚本:
CREATE TABLE test3(
address VARCHAR2(20),
customer VARCHAR2(30),
drdl01 VARCHAR2(10),
imsrp1 VARCHAR2(10),
amount NUMBER(18,2) );INSERT INTO test3(address,customer,drdl01,imsrp1,amount) VALUES('10003051', 'ALPHA','POLY COVERED CORE','PC', 1425501.63);
INSERT INTO test3(address,customer,drdl01,imsrp1,amount) VALUES('10003051', 'ALPHA','SPUN POLYESTER ','SP', 404370.41);
INSERT INTO test3(address,customer,drdl01,imsrp1,amount) VALUES('10003051', 'ALPHA','TRILOBAL AIR ENTANGLEd','TL', 9700);
INSERT INTO test3(address,customer,drdl01,imsrp1,amount) VALUES('10003051', 'ALPHA','SAK COTTON ','CS', 38.3);
INSERT INTO test3(address,customer,drdl01,imsrp1,amount) VALUES('10003051', 'ALPHA','FILAMENT POLYESTER ','FP', 3960);
INSERT INTO test3(address,customer,drdl01,imsrp1,amount) VALUES('10003051', 'ALPHA', 'COTTON COVERED CORE ','CC', 3715.84);INSERT INTO test3(address,customer,drdl01,imsrp1,amount) VALUES('10000373', 'AMANN', 'POLY COVERED CORE','PC', 5246563.21);
INSERT INTO test3(address,customer,drdl01,imsrp1,amount) VALUES('10000373', 'AMANN', 'TEXTURED POLYESTER','TP', 693569.06);
INSERT INTO test3(address,customer,drdl01,imsrp1,amount) VALUES('10000373', 'AMANN', 'SPUN POLYESTER','SP', 39873.6);COMMIT;
ADDRESS CUSTOMER DRDL01 IMSRP1 Amount
1 10003051 ALPHA START LIMITED POLY COVERED CORE PC 1425501.63
2 10003051 ALPHA START LIMITED SPUN POLYESTER SP 404370.41
3 10003051 ALPHA START LIMITED TRILOBAL AIR ENTANGLED POLY TL 9700
4 10003051 ALPHA START LIMITED SAK COTTON CS 38.3
5 10003051 ALPHA START LIMITED FILAMENT POLYESTER FP 3960
6 10003051 ALPHA START LIMITED COTTON COVERED CORE CC 3715.84
7 10000373 AMANN ASIA LIMITED POLY COVERED CORE PC 5246563.21
8 10000373 AMANN ASIA LIMITED TEXTURED POLYESTER TP 693569.06
9 10000373 AMANN ASIA LIMITED SPUN POLYESTER SP 39873.6结果为下面才是对的:
1 10003051 ALPHA START LIMITED POLY COVERED CORE PC 1425501.63
2 10003051 ALPHA START LIMITED SPUN POLYESTER SP 404370.41
3 10003051 ALPHA START LIMITED Others Others 17414.14
4 10000373 AMANN ASIA LIMITED POLY COVERED CORE PC 5246563.21
5 10000373 AMANN ASIA LIMITED TEXTURED POLYESTER TP 693569.06
6 10000373 AMANN ASIA LIMITED Others Others 39873.6脚本:
CREATE TABLE test3(
address VARCHAR2(20),
customer VARCHAR2(30),
drdl01 VARCHAR2(10),
imsrp1 VARCHAR2(10),
amount NUMBER(18,2) );INSERT INTO test3(address,customer,drdl01,imsrp1,amount) VALUES('10003051', 'ALPHA','POLY COVERED CORE','PC', 1425501.63);
INSERT INTO test3(address,customer,drdl01,imsrp1,amount) VALUES('10003051', 'ALPHA','SPUN POLYESTER ','SP', 404370.41);
INSERT INTO test3(address,customer,drdl01,imsrp1,amount) VALUES('10003051', 'ALPHA','TRILOBAL AIR ENTANGLEd','TL', 9700);
INSERT INTO test3(address,customer,drdl01,imsrp1,amount) VALUES('10003051', 'ALPHA','SAK COTTON ','CS', 38.3);
INSERT INTO test3(address,customer,drdl01,imsrp1,amount) VALUES('10003051', 'ALPHA','FILAMENT POLYESTER ','FP', 3960);
INSERT INTO test3(address,customer,drdl01,imsrp1,amount) VALUES('10003051', 'ALPHA', 'COTTON COVERED CORE ','CC', 3715.84);INSERT INTO test3(address,customer,drdl01,imsrp1,amount) VALUES('10000373', 'AMANN', 'POLY COVERED CORE','PC', 5246563.21);
INSERT INTO test3(address,customer,drdl01,imsrp1,amount) VALUES('10000373', 'AMANN', 'TEXTURED POLYESTER','TP', 693569.06);
INSERT INTO test3(address,customer,drdl01,imsrp1,amount) VALUES('10000373', 'AMANN', 'SPUN POLYESTER','SP', 39873.6);COMMIT;
解决方案 »
- 有关韩顺平oracle视频教程问题
- 服务器CPU,内存占用率高
- 归档日志自动删除的问题
- 请教把BLOB的图片字段是单独分成一个表和原表关联好,还是在原表增加一个BLOB字段好!
- 关于index range scan的问题
- 问题急!各位大虾请进阿!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
- 有了客户的license号,怎么才能在metalink.oracle.com注册成功呢?让输入CSI号,是否就是许可号呢?
- 如何写程序将access里的数据导入到oracle里?
- 关于Orale8i server 8.1.5 for Solaris 8 x86安装
- ROW_NUMBER()OVER()函数
- SQL查询not in,或是同一字段多个!=效率超低,怎样优化??
- OEM问题
然后取前两条记录,后面的用others 代替,汇总金额成第三条记录,
也就是说不管原始记录有多少条,我只能取金额最高的两条和其它的汇总成一条记录,DRDL01变为others
就是按order by ADDRESS,Amount desc--这里和address排序有什么关系
你说的每组取2条,是按什么分组,ADDRESS还是前2列?3列?4列?
从你的测试数据能看出来吗
你都不清楚规则,问你也不回答,一个劲发新帖。别人这么问你能看懂吗
题本身不难,你问得不对。看看老大的帖
http://topic.csdn.net/u/20100308/09/460d2b6e-4f38-4829-b82c-b818b729fa6d.html?78795p.s.别再新开帖,否则作删除处理
09年开始的结帖率很低哦
min(CUSTOMER) CUSTOMER,
min(decode(rn, 1, DRDL01, 2, DRDL01, 'START LIMITED OTHERS')) DRDL01,
decode(rn, 1, IMSRP1, 2, IMSRP1, 'OTHERS') IMSRP1,
sum(Amount) Amount
from (select ADDRESS, CUSTOMER, DRDL01, IMSRP1, Amount, row_number()over(partition by ADDRESS order by ADDRESS desc,Amount desc) rn
from test3 t)
group by ADDRESS,decode(rn, 1, IMSRP1, 2, IMSRP1, 'OTHERS')
order by ADDRESS,min(rn)结果:
1 10000373 AMANN POLY COVERED CORE PC 5246563.21
2 10000373 AMANN TEXTURED POLYESTER TP 693569.06
3 10000373 AMANN START LIMITED OTHERS OTHERS 39873.6
4 10003051 ALPHA POLY COVERED CORE PC 1425501.63
5 10003051 ALPHA SPUN POLYESTER SP 404370.41
6 10003051 ALPHA START LIMITED OTHERS OTHERS 17414.14