账期间隔问题（脚本写法）

表A有这样的数据
BANK_ID BILL     charge
304521 200705 20
304521 200706 20
304521 200707 20
304521 200708 20
304521 200710 20
304521 200711 20
304521 200712 20
304521 200801 20
304523 200803 10
304523 200805 10
304523 200809 10
304523 200810 10
304523 200811 10
304523 200812 10
304523 200901 10
304523 200902 10
我要得到这样的数据
BANK_ID  BILL                                                                      CHARGE      
304521    200705-200708,200710-200801                           160
304523    200801,200803,200805,200809-200902                  80

 

create table HSK_TEST_TABLE
(
  BANK_ID     NUMBER,
  BILL            VARCHAR2(6),
  CHARGE      NUMBER
);


insert into HSK_TEST_TABLE (BANK_ID, BILL, CHARGE)
values (304521, '200705', 20);
insert into HSK_TEST_TABLE (BANK_ID, BILL, CHARGE)
values (304521, '200706', 20);
insert into HSK_TEST_TABLE (BANK_ID, BILL, CHARGE)
values (304521, '200707', 20);
insert into HSK_TEST_TABLE (BANK_ID, BILL, CHARGE)
values (304521, '200708', 20);
insert into HSK_TEST_TABLE (BANK_ID, BILL, CHARGE)
values (304521, '200710', 20);
insert into HSK_TEST_TABLE (BANK_ID, BILL, CHARGE)
values (304521, '200711', 20);
insert into HSK_TEST_TABLE (BANK_ID, BILL, CHARGE)
values (304521, '200712', 20);
insert into HSK_TEST_TABLE (BANK_ID, BILL, CHARGE)
values (304521, '200801', 20);
insert into HSK_TEST_TABLE (BANK_ID, BILL, CHARGE)
values (304523, '200803', 10);
insert into HSK_TEST_TABLE (BANK_ID, BILL, CHARGE)
values (304523, '200805', 10);
insert into HSK_TEST_TABLE (BANK_ID, BILL, CHARGE)
values (304523, '200809', 10);
insert into HSK_TEST_TABLE (BANK_ID, BILL, CHARGE)
values (304523, '200810', 10);
insert into HSK_TEST_TABLE (BANK_ID, BILL, CHARGE)
values (304523, '200811', 10);
insert into HSK_TEST_TABLE (BANK_ID, BILL, CHARGE)
values (304523, '200812', 10);
insert into HSK_TEST_TABLE (BANK_ID, BILL, CHARGE)
values (304523, '200901', 10);
insert into HSK_TEST_TABLE (BANK_ID, BILL, CHARGE)
values (304523, '200902', 10);
commit;

 

SELECT bank_id,wmsys.wm_concat(min_bill||'-'||max_bill),SUM(charge) AS charge
  FROM (SELECT bank_id,MIN(bill) AS min_bill,MAX(bill) AS max_bill,SUM(charge) AS charge
         FROM (SELECT t.*, ROW_NUMBER() OVER(PARTITION BY bank_id ORDER BY bill) AS RN
                 FROM HSK_TEST_TABLE t
              )
        GROUP BY bank_id,MONTHS_BETWEEN(TO_DATE(bill,'YYYYMM'),DATE '2000-1-1') - rn
        )
GROUP BY bank_id
;

 

改进版本：

1.ROW_NUMBER() 改为DENSE_RANK()更好，可以有两个月相同也可分为一组
2.查出结果相同的合并，所以我做如下改进

SELECT bank_id,wmsys.wm_concat(nvl2(nullif(min_bill,max_bill),min_bill||'-'||max_bill,max_bill)),
SUM(charge) AS charge
  FROM (SELECT bank_id,MIN(bill) AS min_bill,MAX(bill) AS max_bill,SUM(charge) AS charge
         FROM (SELECT t.*, DENSE_RANK() OVER(PARTITION BY bank_id ORDER BY bill) AS RN
                 FROM HSK_TEST_TABLE t
              )
        GROUP BY bank_id,MONTHS_BETWEEN(TO_DATE(bill,'YYYYMM'),DATE '2000-1-1') - rn
        )
GROUP BY bank_id
;

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/21220558/viewspace-588696/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/21220558/viewspace-588696/