环境(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>