开发日记-Oracle数据库语句实现：借用归还公式

开发背景：
        最近公司的一个仓库系统中有新需求如下：借用样品后根据借用单查询借用数量、归还数量并计算剩余未归还数量。

        借用数量在建立借用单的时候就以及确定，归还是可以分开多次部分归还，这个查询要做到查询到同一张借用单的所有归还数据，同时每一条数据要显示根据每次归还的数量计算剩余未归还的数量。

        我的借用单是由样品表（主表、副表）、项目表、归还表（主表、副表）、用户表、和字典表关联起来的。

        主要难点在于：借用样品的剩余数量等于上一条剩余数量减去这条部分归还后的数据，而有些样品是有唯一码的一次只会被借用一个，没条码则不会限制数量。抛开查询信息，主要公式如下：

• 第一条：

        剩余数量 = 借用数量 - 本次归还数量

• 第二条：

        剩余数量 = 上一条剩余数量 - 本次归还数量

解决过程

        关联查询不是重点我就不详细说了，本次主要要解决的是如何将上面说的计算公式写入到SQL中。

        首先我尝试了在编写sql函数变量define公式，oracle不像mysql一样可以在sql语句中直接定义并使用变量，而函数也只有在plsql工具中查询使用，无法写入程序代码中。所以不能用这种方式。

        然后我找到oracle中lag()关键字可以取出上一条中某列的结果值，公式如下：

lag(‘列名’) over(order by ‘排序方式’) as ‘字段命名’

        通过此方式写入我的sql中后查询发现，第一条无结果，而且最后一条的剩余数量不会减去归还数量。所以这个方法也不行。

        最后，我尝试了在归还的数量上使用sum()关键字，在加上over(order by())分条叠加，然后使用case...when...then...else...end...判断此样品的借用数量是一个还是多个，再用借用数量减去归还数量或归还叠加的sum0和就是剩余数量了。这样做也不需要根据是否是第一条做判断了。关键SQL语句如下：

case
   when ‘借用数量’ > 1  
   	 then (‘借用数量’ - sum(NVL(‘归还数量’, 0))
       over(order by ‘创建时间’))
   else
     (‘借用数量’ - NVL(‘归还数量’, 0))
end as ‘剩余数量’,

运行结果：

        成功根据每次归还的数量查询出剩余的数量，上图排序问题先忽略了，反正结果是成功了~

================================================================================

        然而。。好景不长，在我以为此sql已经通过测试可以完美解决需求问题的时候，出现了bug。。（这就是测试永远猜不到用户使用使用出现的bug。。/捂脸）

        此bug出现的情况为：用户查看自己所有的借用单时，列表中不同的借用单借用数量大于1且借用单归还数量为0时，出现了归还数量为负的情况。

运行截图：

        然后我赶紧审视了代码，确定是这句sql的问题。因为借用单不同，而sql中未作判断，sum()...over()只是按分条查询结果的进行叠加，使不同的借用单的归还数量加到了一起。这就导致上图借用数量为5减去归还数量0（所有的归还数量和为20）= -15的结果。

        在case...when...的判断中根据借用数量是否为1区分后归还数量的sum()叠加和，然后我将判断归还数量是否>0也加入到判断中，测试通过。

运行截图：

SQL更新部分代码如下：

over(      
  partition by  ‘借用单号’                      
  order by ‘创建时间’)

        这句就是重点 ！重点 ！重点！over()用于sum()连续求和，partition by区分了单号，使求和只根据同一单号进行，order by规定了叠加的排序方式。上述需求和bug完美解决！

写好程序后还是得多方面测试啊。。（只有你想不到，没有你测不到。。）