在业务需要中，尤其是在日常的结算中，会进行累积和和累计差的求值，那么遇到该类业务该如何处理呢？

首先看一下累积和的问题。 

在Oracle中，有表emp表，记录了员工的详细信息，其中有sal一列，那么 

需求来了， 

需求， 

我们要累计各个部门员工总工资的姓名(ename)和员工编号(empno) 

在Oracle中，对于此类问题oracle提供了分析函数。 

累计求和的当然需要使用到sum函数。首先来看以下官方文档中的sum



SUM

SUM([ DISTINCT | ALL ] expr)
   [ OVER (analytic_clause) ]

可以看到distinct |all|expr都是可选参数 over也是可选参数，其中的这个over就是oracle提供的分析函数。现在来做这个需求。



SQL> select ename 员工姓名,empno 员工编号,sum(sal) over(order by empno) 累积工资
  2  from emp
  3  order by empno;

员工姓名       员工编号       累积工资
---------- -------- ----------
SMITH          7369        800
ALLEN          7499       2400
WARD           7521       3650
JONES          7566       6625
MARTIN         7654       7875
BLAKE          7698      10725
CLARK          7782      13175
SCOTT          7788      16175
KING           7839      21175
TURNER         7844      22675
ADAMS          7876      23775
JAMES          7900      24725
FORD           7902      27725
MILLER         7934      29025

14 rows selected

SQL> 

这样就可以看到具体的累积工资之和了。但是这么看还不清晰，我们使用Oracle提供的listagg函数可以处理一下。



SQL> select ename 员工姓名,empno 员工编号,sum(sal) over(order by empno) 累积工资，
  2  (select listagg(sal,'+') within group(order by empno) from emp b where b.empno<=a.empno) 计算公式,
  3  (select listagg(ename,'+') within group(order by empno) from emp c where c.empno<=a.empno) 员工累计信息
  4  from emp a
  5  order by empno;

员工姓名       员工编号       累积工资 计算公式                                                                         员工累计信息
---------- -------- ---------- -------------------------------------------------------------------------------- --------------------------------------------------------------------------------
SMITH          7369        800 800                                                                              SMITH
ALLEN          7499       2400 800+1600                                                                         SMITH+ALLEN
WARD           7521       3650 800+1600+1250                                                                    SMITH+ALLEN+WARD
JONES          7566       6625 800+1600+1250+2975                                                               SMITH+ALLEN+WARD+JONES
MARTIN         7654       7875 800+1600+1250+2975+1250                                                          SMITH+ALLEN+WARD+JONES+MARTIN
BLAKE          7698      10725 800+1600+1250+2975+1250+2850                                                     SMITH+ALLEN+WARD+JONES+MARTIN+BLAKE
CLARK          7782      13175 800+1600+1250+2975+1250+2850+2450                                                SMITH+ALLEN+WARD+JONES+MARTIN+BLAKE+CLARK
SCOTT          7788      16175 800+1600+1250+2975+1250+2850+2450+3000                                           SMITH+ALLEN+WARD+JONES+MARTIN+BLAKE+CLARK+SCOTT
KING           7839      21175 800+1600+1250+2975+1250+2850+2450+3000+5000                                      SMITH+ALLEN+WARD+JONES+MARTIN+BLAKE+CLARK+SCOTT+KING
TURNER         7844      22675 800+1600+1250+2975+1250+2850+2450+3000+5000+1500                                 SMITH+ALLEN+WARD+JONES+MARTIN+BLAKE+CLARK+SCOTT+KING+TURNER
ADAMS          7876      23775 800+1600+1250+2975+1250+2850+2450+3000+5000+1500+1100                            SMITH+ALLEN+WARD+JONES+MARTIN+BLAKE+CLARK+SCOTT+KING+TURNER+ADAMS
JAMES          7900      24725 800+1600+1250+2975+1250+2850+2450+3000+5000+1500+1100+950                        SMITH+ALLEN+WARD+JONES+MARTIN+BLAKE+CLARK+SCOTT+KING+TURNER+ADAMS+JAMES
FORD           7902      27725 800+1600+1250+2975+1250+2850+2450+3000+5000+1500+1100+950+3000                   SMITH+ALLEN+WARD+JONES+MARTIN+BLAKE+CLARK+SCOTT+KING+TURNER+ADAMS+JAMES+FORD
MILLER         7934      29025 800+1600+1250+2975+1250+2850+2450+3000+5000+1500+1100+950+3000+1300              SMITH+ALLEN+WARD+JONES+MARTIN+BLAKE+CLARK+SCOTT+KING+TURNER+ADAMS+JAMES+FORD+MIL

14 rows selected

SQL> 

这样看起来就比较明白了。 

除了分析函数，还可以使用开窗函数和标量来写，不过代价会比分析函数高，因为需要访问两次表，这里就不做了。

计算累计差



SQL> create or replace view v_detail
  2  as
  3  select rownum seq,a.*
  4  from (select 编号,项目,金额 from detail order by 编号) a;

View created

SQL> select * from v_detail;

       SEQ         编号 项目                 金额
---------- ---------- ------------ ----------
         1       1000 预交费用          30000
         2       7782 支出1              3450
         3       7839 支出2              6000
         4       7934 支出3              2300

SQL> select 编号,项目,金额,
  2  (case when seq=1 then 金额 else -金额 end ) 转换后的值 from v_detail;

        编号 项目                 金额      转换后的值
---------- ------------ ---------- ----------
      1000 预交费用          30000      30000
      7782 支出1              3450      -3450
      7839 支出2              6000      -6000
      7934 支出3              2300      -2300

SQL> with x as
  2  (select rownum seq ,a.*
  3  from (select 编号,项目,金额 from detail order by 编号) a)
  4  select 编号,项目,金额,
  5  (case when seq=1 then 金额 else -金额 end ) 转换后的值 from x;

        编号 项目                 金额      转换后的值
---------- ------------ ---------- ----------
      1000 预交费用          30000      30000
      7782 支出1              3450      -3450
      7839 支出2              6000      -6000
      7934 支出3              2300      -2300


现在开始计算累计差。 

在这里说一下with x的用法，如果使用with x的写法，那就完全没有必要建立视图v_detail了，因为with x中的x就是一个视图，只是这个视图是临时的，如果查询结束，这个视图也就跟着消失了。



SQL> with x as
  2  (select rownum seq,a.*
  3  from (select 编号,项目,金额 from detail order by 编号) a)
  4  select 编号,项目,金额,
  5  sum(case when seq = 1 then 金额 else -金额 end ) over(order by seq) 余额
  6  from x;

        编号 项目                 金额         余额
---------- ------------ ---------- ----------
      1000 预交费用          30000      30000
      7782 支出1              3450      26550
      7839 支出2              6000      20550
      7934 支出3              2300      18250

SQL> 

以上就是累计和和差的业务解析。

阶乘累计求和
描述
获得用户输入的整数n，输出 1!+2!+...+n!的值。
如果输入数值为0、负数、非数字或非整数，输出提示信息：输入有误，请输入正整数。
 
 
输入格式
使用input()获得系统输入，不增加额外的提示信息。
 
 
输入输出示例
 
 
输入
输出
示例 1
1
1
示例 2
0
输入有误，请输入正整数
示例 3
"A"
输入有误，请输入正整数

a=input()
sum=0
if a.isdigit():
    n=eval(a)
    if n>0:
        fact=1
        for i in range(1,n+1):
            fact=fact*i
            sum=sum+fact
        print(sum)
    else:
        print("输入有误，请输入正整数")
        
else:
    print("输入有误，请输入正整数")
a.isdigital 判断a是否为数字

在一知半解的情况下，竟也写出存储过程完成任务，不得不感叹人是逼出来的。。
虽然比较粗糙，不过也记着留作纪念，毕竟第一次嘛～

--从系统中提取上述个人累计保额超过100万元[险种为红利发两全保险（分红型）、保得利两全保险（分红型）、红利发两全保险C款(分红型)及红利宝两全保险(分红型)]业务的被保险人清单，该清单应包括如下信息：投保单号、保单号、被保险人的身份证号、投保险种、投保份数、保单生效日期、被保险人姓名、年龄，此次新保保额、累计保额

Drop procedure sp_million;
CREATE PROCEDURE sp_million(i_start_date date,i_end_date date,i_addprecount decimal(12,2))
RETURNING char (15),
          char(15),
          char(6),
          decimal(12,3),
          date,
          char(60),
          integer,
          decimal(12,2);
          
define v_pid char (18);                --身份证号
define v_appno char (15);            --投保单号
define v_policyno char(15);            --保单号
define v_classcode char(6);          --投保险种
define v_pieces decimal(12,3);        --投保分数
define v_begdate date;                --保单生效日期
define v_name char(60);                --被保人姓名
define v_age integer;                --被保人年龄
define v_addpre decimal(12,2);        --新保保额
define v_addpresum decimal(12,2);    --累计保额
define v_nowyear integer;
define v_bthyear integer;


--创建临时表temp_2
create temp table temp_2(
pid char (18),
appno char (15),
policyno char(15),
classcode char(6),
pieces decimal(12,3),
begdate date,
name char(60),
age integer,
addpre decimal(12,2)
) with no log;

--创建临时表temp_3
create temp table temp_3(
pid char (18),
appno char (15),
policyno char(15),
classcode char(6),
pieces decimal(12,3),
begdate date,
name char(60),
age integer,
addpre decimal(12,2)
) with no log;

--从riskcon表中获得被保人身份证pid?
select pid from riskcon 
where begdate between i_start_date and i_end_date and polist=2 and (classcode ='EL4611' or classcode ='EL4612' or classcode ='EL4613' or classcode ='EL4614' or classcode ='EL4615' or classcode ='EL4711' or classcode ='EL4712' or classcode ='EL6102')
into temp temp_1 with no log;

--遍历pid，取累计保额超过v_addpresum的被保人投保单号、保单号、投保险种、投保分数、保单生效日期、被保人姓名、被保人年龄、新保保额、累计保额
foreach 

select pid into v_pid from temp_1

--被保人姓名
select name into v_name from custmatl where id=v_pid;

--从custmatl表中取生日后计算被保人年龄
let v_nowyear=YEAR(today);
let v_bthyear=(select YEAR(bthdate) from custmatl where id=v_pid);

--如果被保人生日为空，取1900年为生日
if v_bthyear is null or v_bthyear='' then
let v_bthyear=1900;
end if

let v_age=v_nowyear-v_bthyear;

--从chkpreacct1999-chkpreacct2007表中取所需信息并存入临时表temp_2?
foreach
select appno,policyno,classcode,pieces,begdate,addpre 
into v_appno,v_policyno,v_classcode,v_pieces,v_begdate,v_addpre from chkpreacct1999 
where pid=v_pid and movedate is null and precode='001' and (classcode ='EL4611' or classcode ='EL4612' or classcode ='EL4613' or classcode ='EL4614' or classcode ='EL4615' or classcode ='EL4711' or classcode ='EL4712' or classcode ='EL6102')

insert into temp_2(pid,appno,policyno,classcode,pieces,begdate,name,age,addpre) 
values(v_pid,v_appno,v_policyno,v_classcode,v_pieces,v_begdate,v_name,v_age,v_addpre);
end foreach

foreach
select appno,policyno,classcode,pieces,begdate,addpre 
into v_appno,v_policyno,v_classcode,v_pieces,v_begdate,v_addpre from chkpreacct2000
where pid=v_pid and movedate is null and precode='001' and (classcode ='EL4611' or classcode ='EL4612' or classcode ='EL4613' or classcode ='EL4614' or classcode ='EL4615' or classcode ='EL4711' or classcode ='EL4712' or classcode ='EL6102')

insert into temp_2(pid,appno,policyno,classcode,pieces,begdate,name,age,addpre) 
values(v_pid,v_appno,v_policyno,v_classcode,v_pieces,v_begdate,v_name,v_age,v_addpre);
end foreach

foreach
select appno,policyno,classcode,pieces,begdate,addpre 
into v_appno,v_policyno,v_classcode,v_pieces,v_begdate,v_addpre from chkpreacct2001
where pid=v_pid and movedate is null and precode='001' and (classcode ='EL4611' or classcode ='EL4612' or classcode ='EL4613' or classcode ='EL4614' or classcode ='EL4615' or classcode ='EL4711' or classcode ='EL4712' or classcode ='EL6102')

insert into temp_2(pid,appno,policyno,classcode,pieces,begdate,name,age,addpre) 
values(v_pid,v_appno,v_policyno,v_classcode,v_pieces,v_begdate,v_name,v_age,v_addpre);
end foreach

foreach
select appno,policyno,classcode,pieces,begdate,addpre 
into v_appno,v_policyno,v_classcode,v_pieces,v_begdate,v_addpre from chkpreacct2002
where pid=v_pid and movedate is null and precode='001' and (classcode ='EL4611' or classcode ='EL4612' or classcode ='EL4613' or classcode ='EL4614' or classcode ='EL4615' or classcode ='EL4711' or classcode ='EL4712' or classcode ='EL6102')

insert into temp_2(pid,appno,policyno,classcode,pieces,begdate,name,age,addpre) 
values(v_pid,v_appno,v_policyno,v_classcode,v_pieces,v_begdate,v_name,v_age,v_addpre);
end foreach

foreach
select appno,policyno,classcode,pieces,begdate,addpre 
into v_appno,v_policyno,v_classcode,v_pieces,v_begdate,v_addpre from chkpreacct2003
where pid=v_pid and movedate is null and precode='001' and (classcode ='EL4611' or classcode ='EL4612' or classcode ='EL4613' or classcode ='EL4614' or classcode ='EL4615' or classcode ='EL4711' or classcode ='EL4712' or classcode ='EL6102')

insert into temp_2(pid,appno,policyno,classcode,pieces,begdate,name,age,addpre) 
values(v_pid,v_appno,v_policyno,v_classcode,v_pieces,v_begdate,v_name,v_age,v_addpre);
end foreach

foreach
select appno,policyno,classcode,pieces,begdate,addpre 
into v_appno,v_policyno,v_classcode,v_pieces,v_begdate,v_addpre from chkpreacct2004
where pid=v_pid and movedate is null and precode='001' and (classcode ='EL4611' or classcode ='EL4612' or classcode ='EL4613' or classcode ='EL4614' or classcode ='EL4615' or classcode ='EL4711' or classcode ='EL4712' or classcode ='EL6102')

insert into temp_2(pid,appno,policyno,classcode,pieces,begdate,name,age,addpre) 
values(v_pid,v_appno,v_policyno,v_classcode,v_pieces,v_begdate,v_name,v_age,v_addpre);
end foreach

foreach
select appno,policyno,classcode,pieces,begdate,addpre 
into v_appno,v_policyno,v_classcode,v_pieces,v_begdate,v_addpre from chkpreacct2005
where pid=v_pid and movedate is null and precode='001' and (classcode ='EL4611' or classcode ='EL4612' or classcode ='EL4613' or classcode ='EL4614' or classcode ='EL4615' or classcode ='EL4711' or classcode ='EL4712' or classcode ='EL6102')

insert into temp_2(pid,appno,policyno,classcode,pieces,begdate,name,age,addpre) 
values(v_pid,v_appno,v_policyno,v_classcode,v_pieces,v_begdate,v_name,v_age,v_addpre);
end foreach

foreach
select appno,policyno,classcode,pieces,begdate,addpre 
into v_appno,v_policyno,v_classcode,v_pieces,v_begdate,v_addpre from chkpreacct2006
where pid=v_pid and movedate is null and precode='001' and (classcode ='EL4611' or classcode ='EL4612' or classcode ='EL4613' or classcode ='EL4614' or classcode ='EL4615' or classcode ='EL4711' or classcode ='EL4712' or classcode ='EL6102')

insert into temp_2(pid,appno,policyno,classcode,pieces,begdate,name,age,addpre) 
values(v_pid,v_appno,v_policyno,v_classcode,v_pieces,v_begdate,v_name,v_age,v_addpre);
end foreach

foreach
select appno,policyno,classcode,pieces,begdate,addpre 
into v_appno,v_policyno,v_classcode,v_pieces,v_begdate,v_addpre from chkpreacct2007
where pid=v_pid and movedate is null and precode='001' and (classcode ='EL4611' or classcode ='EL4612' or classcode ='EL4613' or classcode ='EL4614' or classcode ='EL4615' or classcode ='EL4711' or classcode ='EL4712' or classcode ='EL6102')

insert into temp_2(pid,appno,policyno,classcode,pieces,begdate,name,age,addpre) 
values(v_pid,v_appno,v_policyno,v_classcode,v_pieces,v_begdate,v_name,v_age,v_addpre);
end foreach

--从temp_2表中取保额超过i_addprecount的信息存入temp_3
select sum(addpre) into v_addpresum from temp_2;

if v_addpresum>i_addprecount then
insert into temp_3 select * from temp_2;
end if

--清空temp_2
delete from temp_2 where 1=1;

end foreach

foreach

--取所需字段
select appno,policyno,classcode,pieces,begdate,name,age,addpre 
into v_appno,v_policyno,v_classcode,v_pieces,v_begdate,v_name,v_age,v_addpre
from temp_3

return v_appno,
        v_policyno,
        v_classcode,
        v_pieces,
        v_begdate,
        v_name,
        v_age,
        v_addpre with resume;

end foreach

drop table temp_1;
drop table temp_2;
drop table temp_3;

END PROCEDURE;

Employee 表保存了一年内的薪水信息。
请你编写 SQL 语句，来查询一个员工三个月内的累计薪水，但是不包括最近一个月的薪水。
结果请按 ‘Id’ 升序，然后按 ‘Month’ 降序显示。
示例：

输入：
| Id | Month | Salary |
|----|-------|--------|
| 1  | 1     | 20     |
| 2  | 1     | 20     |
| 1  | 2     | 30     |
| 2  | 2     | 30     |
| 3  | 2     | 40     |
| 1  | 3     | 40     |
| 3  | 3     | 60     |
| 1  | 4     | 60     |
| 3  | 4     | 70     |

输出：
| Id | Month | Salary |
|----|-------|--------|
| 1  | 3     | 90     |
| 1  | 2     | 50     |
| 1  | 1     | 20     |
| 2  | 1     | 20     |
| 3  | 3     | 100    |
| 3  | 2     | 40     |

解释：
员工 ‘1’ 除去最近一个月（月份 ‘4’），有三个月的薪水记录：月份 ‘3’ 薪水为 40，月份 ‘2’ 薪水为 30，月份 ‘1’ 薪水为 20。
所以近 3 个月的薪水累计分别为 (40 + 30 + 20) = 90，(30 + 20) = 50 和 20。
| Id | Month | Salary |
|----|-------|--------|
| 1  | 3     | 90     |
| 1  | 2     | 50     |
| 1  | 1     | 20     |

员工 ‘2’ 除去最近的一个月（月份 ‘2’）的话，只有月份 ‘1’ 这一个月的薪水记录。
| Id | Month | Salary |
|----|-------|--------|
| 2  | 1     | 20     |

员工 ‘3’ 除去最近一个月（月份 ‘4’）后有两个月，分别为：月份 ‘4’ 薪水为 60 和 月份 ‘2’ 薪水为 40。所以各月的累计情况如下：
| Id | Month | Salary |
|----|-------|--------|
| 3  | 3     | 100    |
| 3  | 2     | 40     |

语句
select
    count_salary.id id,
    month,
    salary
from
--第一部分来算近三个月工资的总和
(select
    e1.id,
    e1.month,
    sum(e2.salary) as salary
from
    employee e1, employee e2
where
    e1.id = e2.id
    and e1.month-e2.month <= 2
    and e1.month-e2.month>=0
group by
    e1.id,
    e1.month) count_salary
left join
--排除掉最近一个月
(select
    id,
    max(month) as mmonth
from
    employee
group by
    id) mmonth
on
    count_salary.id = mmonth.id
where
    month <> mmonth
order by
    id,
    month desc