SQL请教(急''''')

Question

表结构及数据如下:
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就是按order by ADDRESS,Amount desc ,
然后取前两条记录,后面的用others 代替,汇总金额成第三条记录,
也就是说不管原始记录有多少条,我只能取金额最高的两条和其它的汇总成一条记录,DRDL01变为others 即可.

Answer 1

发建表脚本和插入数据的SQL脚本

Answer 2

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;

Answer 3

使用over()函数和rank()函数，参考语句：
SELECT B.ADDRESS,B.CUSTOMER, CASE WHEN B.TOP <3 THEN B.DRDL01 ELSE 'Others' end  DRDL01,
CASE WHEN B.TOP <3 THEN B.IMSRP1 ELSE 'Others' end  IMSRP1,sum(B.AMOUNT) AMOUNT
FROM
(select A.ADDRESS,A.CUSTOMER,A.DRDL01,A.IMSRP1,A.AMOUNT, rank()OVER(partition by A.ADDRESS,A.CUSTOMER ORDER BY AMOUNT DESC) AS TOP
FROM  tmp_test A) B
group by B.ADDRESS,B.CUSTOMER, CASE WHEN B.TOP <3 THEN B.DRDL01 ELSE 'Others' end  ,
CASE WHEN B.TOP <3 THEN b.IMSRP1 ELSE 'Others' end 

Answer 4

到底发了几帖呀。
select min(ADDRESS) ADDRESS,
       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

Answer 5

WITH tt AS
 (SELECT t.*, row_number() over(PARTITION BY t.address, t.customer ORDER BY ADDRESS, Amount DESC) rn
    FROM test3 t)
SELECT *
  FROM (SELECT address, customer, drdl01, imsrp1, amount FROM tt WHERE rn <= 2
        UNION ALL
        SELECT address, customer, 'OTHERS', 'OTHERS', SUM(amount) amount FROM tt WHERE rn > 2 
        GROUP BY address, customer)
 ORDER BY ADDRESS, Amount DESC