数据库连续区间汇总(求和)一例，rownum伪列利用(原创)

     环境(Oracle Database 10g Enterprise Edition Release 10.2.0.1.0)

     工作中碰到如此问题，报表汇总统计，从单表中获取连续区间内某单值的汇总和值，研究发现，通过Oracle一些内部函数以及伪劣Rownum可以初步解决类似问题，或者通过转换形成类似数据再行生成能够借鉴Rownum参与差值比较获取分组区间区域值。

 

下面为一典型SQL示例：

Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 
Connected as hr
 
SQL> 
SQL> create table T_contGroupSum(
  2  deptno  varchar2(10),
  3  groupno varchar2(10),
  4  val     number(10,2)
  5  );
 
Table created
 
SQL> 
SQL> begin
  2  insert into T_contGroupSum (DEPTNO, GROUPNO, VAL) values ('D01', 'A', 10.00);
  3  insert into T_contGroupSum (DEPTNO, GROUPNO, VAL) values ('D01', 'B', 10.00);
  4  insert into T_contGroupSum (DEPTNO, GROUPNO, VAL) values ('D01', 'C', 10.00);
  5  insert into T_contGroupSum (DEPTNO, GROUPNO, VAL) values ('D01', 'E', 20.00);
  6  insert into T_contGroupSum (DEPTNO, GROUPNO, VAL) values ('D02', 'F', 20.00);
  7  insert into T_contGroupSum (DEPTNO, GROUPNO, VAL) values ('D03', 'G', 20.00);
  8  insert into T_contGroupSum (DEPTNO, GROUPNO, VAL) values ('D03', 'H', 20.00);
  9  insert into T_contGroupSum (DEPTNO, GROUPNO, VAL) values ('D03', 'I', 20.00);
 10  insert into T_contGroupSum (DEPTNO, GROUPNO, VAL) values ('D03', 'M', 40.00);
 11  insert into T_contGroupSum (DEPTNO, GROUPNO, VAL) values ('D03', 'N', 40.00);
 12  insert into T_contGroupSum (DEPTNO, GROUPNO, VAL) values ('D04', 'R', 60.00);
 13  insert into T_contGroupSum (DEPTNO, GROUPNO, VAL) values ('D05', 'X', 80.00);
 14  insert into T_contGroupSum (DEPTNO, GROUPNO, VAL) values ('D05', 'Y', 80.00);
 15  insert into T_contGroupSum (DEPTNO, GROUPNO, VAL) values ('D05', 'Z', 80.00);
 16  COMMIT;
 17  end;
 18  /
 
PL/SQL procedure successfully completed
 
SQL> select * from T_contGroupSum;
 
DEPTNO     GROUPNO             VAL
---------- ---------- ------------
D01        A                 10.00
D01        B                 10.00
D01        C                 10.00
D01        E                 20.00
D02        F                 20.00
D03        G                 20.00
D03        H                 20.00
D03        I                 20.00
D03        M                 40.00
D03        N                 40.00
D04        R                 60.00
D05        X                 80.00
D05        Y                 80.00
D05        Z                 80.00
 
14 rows selected
 
SQL> 
SQL> select t.deptno,min(t.groupno) mingroupno,max(t.groupno) maxgroupno,sum(t.val) from T_contGroupSum t
  2  where 1=1 group by t.deptno,ascii(t.groupno)-64-rownum order by 1;
 
DEPTNO     MINGROUPNO MAXGROUPNO SUM(T.VAL)
---------- ---------- ---------- ----------
D01        A          C                  30
D01        E          E                  20
D02        F          F                  20
D03        G          I                  60
D03        M          N                  80
D04        R          R                  60
D05        X          Z                 240
 
7 rows selected
 
SQL>