高难度SQL请教

Question

下面的是SQL的原始数据,我需要下面的结果,怎么写SQL:
ADDRESS CUSTOMER                                   DRDL01                       IMSRP1        Amount
10003051 ALPHA START LIMITED                      POLY COVERED CORE              PC  1425501.63
10003051 ALPHA START LIMITED                      SPUN POLYESTER                 SP  404370.41
10003051 ALPHA START LIMITED                      TRILOBAL AIR ENTANGLED POLY    TL  9700
10003051 ALPHA START LIMITED                      SAK COTTON                     CS  38.3
10003051 ALPHA START LIMITED                      FILAMENT POLYESTER             FP  3960
10003051 ALPHA START LIMITED                      COTTON COVERED CORE            CC  3715.84
要得到的结果:
10003051 ALPHA START LIMITED                      POLY COVERED CORE              PC  1425501.63
10003051 ALPHA START LIMITED                      SPUN POLYESTER                 SP  404370.41
10003051 ALPHA START LIMITED                           OTHERS                               OTHERS   17414.14就是按order by ADDRESS,Amount desc ,
然后取前两条记录,后面的用others 代替,汇总金额成第三条记录,
也就是说不管原始记录有多少条,我只能去金额最高的两条和其它的汇总成一条记录,DRDL01变为others 即可.

Answer 1

-- 先把SQL语句贴出来，楼主：下次问问题，能不能自己把SQL语句贴出来呢？(你这样：很影响别人解决你这个问题的激情)
CREATE TABLE test3(
address VARCHAR2(20),
customer VARCHAR2(10),
drdl01 VARCHAR2(10),
imsrp1 VARCHAR2(50),
amount NUMBER(18,2) );-----------------------------------------------------------------------------
INSERT INTO test3(address,customer,drdl01,imsrp1,amount) VALUES('10003051', 'ALPHA', 'START LIMITED POLY COVERED CORE PC', 1425501.63);
INSERT INTO test3(address,customer,drdl01,imsrp1,amount) VALUES('10003051', 'ALPHA', 'START LIMITED SPUN POLYESTER SP', 404370.41);
INSERT INTO test3(address,customer,drdl01,imsrp1,amount) VALUES('10003051', 'ALPHA', 'START LIMITED TRILOBAL AIR ENTANGLED POLY TL', 9700);
INSERT INTO test3(address,customer,drdl01,imsrp1,amount) VALUES('10003051', 'ALPHA', 'START LIMITED SAK COTTON CS', 38.3);
INSERT INTO test3(address,customer,drdl01,imsrp1,amount) VALUES('10003051', 'ALPHA', 'START LIMITED FILAMENT POLYESTER FP', 3960);
INSERT INTO test3(address,customer,drdl01,imsrp1,amount) VALUES('10003051', 'ALPHA', 'START LIMITED COTTON COVERED CORE CC', 3715.84);

Answer 2

select min(ADDRESS) ADDRESS,
       min(CUSTOMER) CUSTOMER,
       decode(rownum, 1, DRDL01, 2, DRDL01, 'START LIMITED OTHERS') DRDL01,
       decode(rownum, 1, IMSRP1, 2, IMSRP1, 'OTHERS') IMSRP1,
       sum(Amount) Amount
  from (select ADDRESS, CUSTOMER, DRDL01, IMSRP1, Amount
          from tab t
         order by Amount desc)
 group by decode(rownum, 1, IMSRP1, 2, IMSRP1, 'OTHERS')
 order by min(rownum)

Answer 3

-- 刚才贴错了，再贴一下!
CREATE TABLE test3(
address VARCHAR2(20),
customer VARCHAR2(10),
drdl01 VARCHAR2(10),
imsrp1 VARCHAR2(50),
amount NUMBER(18,2) );-----------------------------------------------------------------------------
INSERT INTO test3(address,customer,drdl01,imsrp1,amount) VALUES('10003051', 'ALPHA', 'START', 'LIMITED POLY COVERED CORE PC', 1425501.63);
INSERT INTO test3(address,customer,drdl01,imsrp1,amount) VALUES('10003051', 'ALPHA', 'START', 'LIMITED SPUN POLYESTER SP', 404370.41);
INSERT INTO test3(address,customer,drdl01,imsrp1,amount) VALUES('10003051', 'ALPHA', 'START', 'LIMITED TRILOBAL AIR ENTANGLED POLY TL', 9700);
INSERT INTO test3(address,customer,drdl01,imsrp1,amount) VALUES('10003051', 'ALPHA', 'START', 'LIMITED SAK COTTON CS', 38.3);
INSERT INTO test3(address,customer,drdl01,imsrp1,amount) VALUES('10003051', 'ALPHA', 'START', 'LIMITED FILAMENT POLYESTER FP', 3960);
INSERT INTO test3(address,customer,drdl01,imsrp1,amount) VALUES('10003051', 'ALPHA', 'START', 'LIMITED COTTON COVERED CORE CC', 3715.84);COMMIT;

Answer 4

上面的做发只是一个地址可以实现,如果有多个地址就是错误的结果.例如记录为:
    ADDRESS_NUMBER CUSTOMER DRDL01 IMSRP1 BEFORETWOYEAR
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

Answer 5

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 6

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 7

CREATE TABLE test3(
address VARCHAR2(20),
customer VARCHAR2(10),
drdl01 VARCHAR2(10),
imsrp1 VARCHAR2(50),
amount NUMBER(18,2) );-----------------------------------------------------------------------------
INSERT INTO test3(address,customer,drdl01,imsrp1,amount) VALUES('10003051', 'ALPHA', 'START', 'LIMITED POLY COVERED CORE PC', 1425501.63);
INSERT INTO test3(address,customer,drdl01,imsrp1,amount) VALUES('10003051', 'ALPHA', 'START', 'LIMITED SPUN POLYESTER SP', 404370.41);
INSERT INTO test3(address,customer,drdl01,imsrp1,amount) VALUES('10003051', 'ALPHA', 'START', 'LIMITED TRILOBAL AIR ENTANGLED POLY TL', 9700);
INSERT INTO test3(address,customer,drdl01,imsrp1,amount) VALUES('10003051', 'ALPHA', 'START', 'LIMITED SAK COTTON CS', 38.3);
INSERT INTO test3(address,customer,drdl01,imsrp1,amount) VALUES('10003051', 'ALPHA', 'START', 'LIMITED FILAMENT POLYESTER FP', 3960);
INSERT INTO test3(address,customer,drdl01,imsrp1,amount) VALUES('10003051', 'ALPHA', 'START', 'LIMITED COTTON COVERED CORE CC', 3715.84);
INSERT INTO test3(address,customer,drdl01,imsrp1,amount) VALUES('10000373', 'AMANN', 'ASIA', 'LIMITED POLY COVERED CORE PC', 5246563.21);
INSERT INTO test3(address,customer,drdl01,imsrp1,amount) VALUES('10000373', 'AMANN', 'ASIA', 'LIMITED TEXTURED POLYESTER TP', 693569.06);
INSERT INTO test3(address,customer,drdl01,imsrp1,amount) VALUES('10000373', 'AMANN', 'ASIA', 'LIMITED SPUN POLYESTER SP', 39873.6);COLUMN address FOR A8
COLUMN customer FOR a6
COLUMN drdl01 FOR a6
COLUMN imsrp1 FOR a38
COLUMN amount FOR 9999999.99SELECT address, customer, drdl01, 
       decode(rcn,1,imsrp1,2,imsrp1,'Othres Othres') imsrp1,
       sum(amount) amount
FROM 
  (
   SELECT ROW_NUMBER() OVER (PARTITION BY address, customer, drdl01 ORDER BY amount desc) rcn, 
  address, customer, drdl01, imsrp1, amount
   FROM test3
  ) t
GROUP BY address, customer, drdl01, decode(rcn,1,imsrp1,2,imsrp1,'Othres Othres')
ORDER BY address, customer, drdl01, imsrp1;
---------------------------------------------------------------------------------------------------
ADDRESS  CUSTOM DRDL01 IMSRP1                                      AMOUNT
-------- ------ ------ -------------------------------------- -----------
10000373 AMANN  ASIA   LIMITED POLY COVERED CORE PC            5246563.21
10000373 AMANN  ASIA   LIMITED TEXTURED POLYESTER TP            693569.06
10000373 AMANN  ASIA   Othres Othres                             39873.60
10003051 ALPHA  START  LIMITED SPUN POLYESTER SP                404370.41
10003051 ALPHA  START  LIMITED TRILOBAL AIR ENTANGLED POLY TL     9700.00
10003051 ALPHA  START  Othres Othres                              7714.14

Answer 8

DROP TABLE test3;CREATE TABLE test3(
address VARCHAR2(20),
customer VARCHAR2(10),
drdl01 VARCHAR2(10),
imsrp1 VARCHAR2(50),
amount NUMBER(18,2) );-----------------------------------------------------------------------------
INSERT INTO test3(address,customer,drdl01,imsrp1,amount) VALUES('10003051', 'ALPHA', 'START', 'LIMITED POLY COVERED CORE PC', 1425501.63);
INSERT INTO test3(address,customer,drdl01,imsrp1,amount) VALUES('10003051', 'ALPHA', 'START', 'LIMITED SPUN POLYESTER SP', 404370.41);
INSERT INTO test3(address,customer,drdl01,imsrp1,amount) VALUES('10003051', 'ALPHA', 'START', 'LIMITED TRILOBAL AIR ENTANGLED POLY TL', 9700);
INSERT INTO test3(address,customer,drdl01,imsrp1,amount) VALUES('10003051', 'ALPHA', 'START', 'LIMITED SAK COTTON CS', 38.3);
INSERT INTO test3(address,customer,drdl01,imsrp1,amount) VALUES('10003051', 'ALPHA', 'START', 'LIMITED FILAMENT POLYESTER FP', 3960);
INSERT INTO test3(address,customer,drdl01,imsrp1,amount) VALUES('10003051', 'ALPHA', 'START', 'LIMITED COTTON COVERED CORE CC', 3715.84);
INSERT INTO test3(address,customer,drdl01,imsrp1,amount) VALUES('10000373', 'AMANN', 'ASIA', 'LIMITED POLY COVERED CORE PC', 5246563.21);
INSERT INTO test3(address,customer,drdl01,imsrp1,amount) VALUES('10000373', 'AMANN', 'ASIA', 'LIMITED TEXTURED POLYESTER TP', 693569.06);
INSERT INTO test3(address,customer,drdl01,imsrp1,amount) VALUES('10000373', 'AMANN', 'ASIA', 'LIMITED SPUN POLYESTER SP', 39873.6);COMMIT;COLUMN address FOR A8
COLUMN customer FOR a6
COLUMN drdl01 FOR a6
COLUMN imsrp1 FOR a38
COLUMN amount FOR 9999999.99SELECT address, customer, drdl01, 
       decode(rcn,1,imsrp1,2,imsrp1,'Othres Othres') imsrp1,
       sum(amount) amount
FROM 
  (
   SELECT ROW_NUMBER() OVER (PARTITION BY address, customer, drdl01 ORDER BY amount desc) rcn, 
  address, customer, drdl01, imsrp1, amount
   FROM test3
  ) t
GROUP BY address, customer, drdl01, decode(rcn,1,imsrp1,2,imsrp1,'Othres Othres')
ORDER BY address, customer, drdl01, imsrp1;
---------------------------------------------------------------------------------------------------
ADDRESS  CUSTOM DRDL01 IMSRP1                                      AMOUNT
-------- ------ ------ -------------------------------------- -----------
10000373 AMANN  ASIA   LIMITED POLY COVERED CORE PC            5246563.21
10000373 AMANN  ASIA   LIMITED TEXTURED POLYESTER TP            693569.06
10000373 AMANN  ASIA   Othres Othres                             39873.60
10003051 ALPHA  START  LIMITED POLY COVERED CORE PC            1425501.63
10003051 ALPHA  START  LIMITED SPUN POLYESTER SP                404370.41
10003051 ALPHA  START  Othres Othres                             17414.14

Answer 9

引用 5 楼 tian790317 的回复:上面的做发只是一个地址可以实现,如果有多个地址就是错误的结果.例如记录为:
ADDRESS_NUMBER CUSTOMER DRDL01 IMSRP1 BEFORETWOYEAR
1 10003051 ALPHA START LIMITED POLY COVERED CORE PC 1425501.63
2 10……贴上脚本是最好的