客户明细账是一个非常重要的报表，企业中所有和客户的往来资金都会在上面进行判断，那么，如何在数据库中做一个使用，且准确的客户明细账呢。

1.需求分析：

需要做一个客户明细账，重要字段：组织，单据号，本期贷方，本期借方，期末余额，客户，单据日期。

计算规则：当期期末余额 = 上期期末余额 - 本期贷方 + 本期借方 （同一个单据不会既是贷方又是借方）。

思路解析：

（1）根据计算规则，容易观察出是一个累加，可以使用sum() over(order by )来计算累加（减法可以理解为加对应值的负数）

（2）确定order by后面的字段，根据规则，order by后面的字段不能重复，否则，没有累加的效果，由于同一天会有多个单据，所以，单据日期被排除，

所以，我们采用rownum作为累加依据。除此之外，根据要求：对同一个组织下相同的客户进行账目明细统计。所以：sum(BB.money_cr) over(partition by BB.pk_org,BB.customer order by rownum )。

（3）期末余额计算完成后，我们需要确定下一个数据：期初数据，在每一个查询条件下，同一个组织同一个客户都应该有一条期初数据。在这里，通过使用rank()开窗函数进行判断

（4）为了更容易动态取值，我们把查询分为两部分，一部分用来查询期初数据，另一个部分用来查询其他的数据，同样，通过rk4来进行标记。

通过以上4步，就可以基本完成这个功能，之所以是基本完成，是因为有一种情况不能实现，想象这样一种情景，如果最早的单据日期为2018-12-01，但是客户在查询的

时候，把日期区间设置为此日期之前，由于我们使用union all来取期初数据，这样会造成没有期初数据，所以，只使用查询语句无法实现这种情况的查询。

效果如下：字段从左到右分别为：本期借方，本期贷方，期末余额，单据日期

查询效果展示

SQL及关键注释如下：

--日期唯一，根据日期逆序，加上时间筛选，rank为一的值即为期初

select * from (

select * from (

select FF.*,rank() over(partition by pk_org,customer order by billdate desc) qcrk from (

--筛选所有可以作为期初的数据

SELECT

EE.billdate as begin_billdate,

'期初' as s_billtype,

'' as s_billno,

EE.vbillcode as s_vbillcode,

EE.BQJF as s_BQJF,

EE.BQDF as s_BQDF,

EE.QMYE AS QMYE,EE.billdate AS BILLDATE,

case when EE.rk2 = 1 and rank() over(partition by EE.pk_org,EE.customer,EE.billdate order by EE.Brn desc) <> 1 then

EE.billdate else

EE.end_billdate end AS end_BILLDATE,

EE.pk_org AS PK_ORG,EE.pk_group AS PK_GROUP,EE.rk2,rank() over(partition by EE.pk_org,EE.customer,EE.billdate order by EE.Brn desc) as rk3,

rank() over(partition by EE.pk_org,EE.customer,EE.billdate order by EE.Brn desc) rk4,EE.ordercubasdoc AS ordercubasdoc,EE.customer as customer

FROM (

select DD.*,rank() over(partition by DD.pk_org,DD.customer order by DD.Brn),

rank() over(partition by DD.pk_org,DD.customer,DD.billdate order by Brn desc) as rk2,

case when DD.customer = DD.s_customer then

Lead(DD.billdate) over(order by DD.BRN)

else DD.billdate end as end_billdate

from(

select CC.*,Lead(CC.customer) over(order by CC.brn) as s_customer,

rownum as crn from(

select rownum as brn,BB.*,

case when BB.billtype = '期初' then BB.money_cr else sum(BB.money_cr) over(partition by BB.pk_org,BB.customer order by rownum ) end as QMYE,'00011A10000000000EYA' as pk_group

from (

select

AA.*

from (

--客户应收款期初

select substr(ar_recbill.billdate,0,10) as billdate,ar_recbill.pk_org as pk_org,ar_recitem.ordercubasdoc as ordercubasdoc,

ar_recitem.customer as customer,'期初' as billtype,'期初' as billno,'' as vbillcode,0 as BQJF,0 as BQDF,

sum(ar_recitem.money_de) as money_cr from ar_recbill ar_recbill,ar_recitem ar_recitem where

ar_recbill.pk_recbill = ar_recitem.pk_recbill and nvl(ar_recbill.dr,0) = 0

and nvl(ar_recitem.dr,0) = 0 and ar_recbill.isinit = 'Y'

group by ar_recitem.customer,ar_recitem.ordercubasdoc,ar_recbill.pk_org,'期初','','',0,0,substr(ar_recbill.billdate,0,10)

union all

--客户收款单期初

select substr(ar_gatherbill.billdate,0,10) as billdate,ar_gatherbill.pk_org as pk_org,ar_gatheritem.ordercubasdoc as ordercubasdoc,ar_gatheritem.customer as customer,'期初' as billtype,'期初' as billno,'' as vbillcode,0 as BQJF,0 as BQDF,-sum(ar_gatheritem.money_cr) as money_cr from ar_gatherbill ar_gatherbill,ar_gatheritem ar_gatheritem where ar_gatherbill.pk_gatherbill = ar_gatheritem.pk_gatherbill and nvl(ar_gatherbill.dr,0) = 0 and nvl(ar_gatheritem.dr,0) = 0 and ar_gatherbill.isinit = 'Y'

group by ar_gatheritem.customer,ar_gatheritem.ordercubasdoc,ar_gatherbill.pk_org,'期初','','',0,0,substr(ar_gatherbill.billdate,0,10)

union all

--查询客户收款单

select substr(ar_gatherbill.billdate,0,10) as billdate,ar_gatherbill.pk_org as pk_org,ar_gatheritem.ordercubasdoc as ordercubasdoc,ar_gatheritem.customer as customer,'收款单' as billtype,ar_gatherbill.billno as billno,ar_gatherbill.def1 as vbillcode,0 as BQJF,

ar_gatheritem.money_cr - nvl(arap_debtstransfer.money,0) as BQDF,

nvl(arap_debtstransfer.money,0)-ar_gatheritem.money_cr as money_cr

from ar_gatherbill ar_gatherbill left join ar_gatheritem ar_gatheritem on ar_gatherbill.pk_gatherbill = ar_gatheritem.pk_gatherbill

left join (select sum(money) as money,pk_item pk_item,outobj outobj from arap_debtstransfer where nvl(dr,0) = 0 group by pk_item,outobj)

arap_debtstransfer on ar_gatheritem.pk_gatheritem = arap_debtstransfer.pk_item

where nvl(ar_gatherbill.dr,0) = 0 and nvl(ar_gatheritem.dr,0) = 0 and ar_gatherbill.isinit = 'N' union all

--查询销售出库金额 销售出库表体

select substr(ic_saleout_b.dbizdate,0,10) as billdate,ic_saleout_h.cfanaceorgoid as pk_org,

ic_saleout_b.casscustid as ordercubasdoc,ic_saleout_b.cinvoicecustid as customer,'销售出库单' as billtype,ic_saleout_h.vbillcode as billno,ic_saleout_b.vfirstbillcode as vbillcode,SUM(ic_saleout_b.norigtaxmny)as BQJF,0 as BQDF,SUM(ic_saleout_b.norigtaxmny) as money_cr from ic_saleout_h ic_saleout_h, ic_saleout_b ic_saleout_b where ic_saleout_h.cgeneralhid = ic_saleout_b.cgeneralhid and nvl(ic_saleout_b.dr,0) = 0 and nvl(ic_saleout_h.dr,0) = 0

group by substr(ic_saleout_b.dbizdate,0,10),ic_saleout_h.cfanaceorgoid,ic_saleout_b.casscustid,ic_saleout_b.cinvoicecustid,'销售出库单',ic_saleout_h.vbillcode,ic_saleout_b.vfirstbillcode

union all

--查询服务类物料

select substr(so_saleorder.dbilldate,0,10) as billdate,so_saleorder_b.csettleorgid as pk_org,so_saleorder.ccustomerid as ccustomerid,so_saleorder.cinvoicecustid as customer,'服务费' as billtype,''as billno,so_saleorder.vbillcode as vbillcode,sum(so_saleorder_b.nqtorigtaxnetprc) as BQJF,0 as BQDF,sum(so_saleorder_b.nqtorigtaxnetprc) as money_cr from so_saleorder ,so_saleorder_b so_saleorder_b,bd_material bd_material where so_saleorder.csaleorderid = so_saleorder_b.csaleorderid and nvl(so_saleorder.dr,0) = 0 and nvl(so_saleorder_b.dr,0) = 0 and bd_material.pk_material = so_saleorder_b.cmaterialvid and bd_material.fee = 'Y'

and so_saleorder_b.nqtorigtaxnetprc <> 0

group by so_saleorder.ccustomerid,so_saleorder.cinvoicecustid,substr(so_saleorder.dbilldate,0,10),so_saleorder_b.csettleorgid,'服务费','',so_saleorder.vbillcode,0

) AA

--where AA.customer = '10011A1000000000048D'

left join bd_customer bd_customer on bd_customer.pk_customer = AA.customer

where bd_customer.pk_custclass = '10011A100000000002S4'

order by AA.pk_org,AA.customer,AA.billdate,AA.billno

)BB

)CC

)DD

)EE

where customer = (select pk_customer from bd_customer where code = parameter('param3')) and pk_org = parameter('param4')--(select pk_customer from bd_customer where code = '')

order by EE.brn desc

)FF

where rk4 = 1 --and billdate < '2019-04-09' --添加初始日期参数，添加客户参数

) where qcrk = 1

union all

select FF.*,rank() over(partition by pk_org,customer order by billdate desc) qcrk from (

SELECT

EE.billdate as begin_billdate,

EE.billtype as s_billtype,

EE.billno as s_billno,

EE.vbillcode as s_vbillcode,

EE.BQJF as s_BQJF,

EE.BQDF as s_BQDF,

EE.QMYE AS QMYE,EE.billdate AS BILLDATE,

case when EE.rk2 = 1 and rank() over(partition by EE.pk_org,EE.customer,EE.billdate order by EE.Brn desc) <> 1 then

EE.billdate else

EE.end_billdate end AS end_BILLDATE,

EE.pk_org AS PK_ORG,EE.pk_group AS PK_GROUP,EE.rk2,rank() over(partition by EE.pk_org,EE.customer,EE.billdate order by EE.Brn desc) as rk3,

rank() over(partition by EE.pk_org,EE.customer order by EE.Brn),EE.ordercubasdoc AS ordercubasdoc,EE.customer as customer

FROM (

select DD.*,rank() over(partition by DD.pk_org,DD.customer order by DD.Brn),

rank() over(partition by DD.pk_org,DD.customer,DD.billdate order by Brn desc) as rk2,

case when DD.customer = DD.s_customer then

Lead(DD.billdate) over(order by DD.BRN)

else DD.billdate end as end_billdate

from(

select CC.*,Lead(CC.customer) over(order by CC.brn) as s_customer,

rownum as crn from(

select rownum as brn,BB.*,

case when BB.billtype = '期初' then BB.money_cr else sum(BB.money_cr) over(partition by BB.pk_org,BB.customer order by rownum ) end as QMYE,'00011A10000000000EYA' as pk_group

from (

select

AA.*

from (

--客户应收款期初

select substr(ar_recbill.billdate,0,10) as billdate,ar_recbill.pk_org as pk_org,ar_recitem.ordercubasdoc as ordercubasdoc,

ar_recitem.customer as customer,'期初' as billtype,'期初' as billno,'' as vbillcode,0 as BQJF,0 as BQDF,

sum(ar_recitem.money_de) as money_cr from ar_recbill ar_recbill,ar_recitem ar_recitem where

ar_recbill.pk_recbill = ar_recitem.pk_recbill and nvl(ar_recbill.dr,0) = 0

and nvl(ar_recitem.dr,0) = 0 and ar_recbill.isinit = 'Y'

group by ar_recitem.customer,ar_recitem.ordercubasdoc,ar_recbill.pk_org,'期初','','',0,0,substr(ar_recbill.billdate,0,10)

union all

--客户收款单期初

select substr(ar_gatherbill.billdate,0,10) as billdate,ar_gatherbill.pk_org as pk_org,ar_gatheritem.ordercubasdoc as ordercubasdoc,ar_gatheritem.customer as customer,'期初' as billtype,'期初' as billno,'' as vbillcode,0 as BQJF,0 as BQDF,-sum(ar_gatheritem.money_cr) as money_cr from ar_gatherbill ar_gatherbill,ar_gatheritem ar_gatheritem where ar_gatherbill.pk_gatherbill = ar_gatheritem.pk_gatherbill and nvl(ar_gatherbill.dr,0) = 0 and nvl(ar_gatheritem.dr,0) = 0 and ar_gatherbill.isinit = 'Y'

group by ar_gatheritem.customer,ar_gatheritem.ordercubasdoc,ar_gatherbill.pk_org,'期初','','',0,0,substr(ar_gatherbill.billdate,0,10)

union all

--查询客户收款单

select substr(ar_gatherbill.billdate,0,10) as billdate,ar_gatherbill.pk_org as pk_org,ar_gatheritem.ordercubasdoc as ordercubasdoc,ar_gatheritem.customer as customer,'收款单' as billtype,ar_gatherbill.billno as billno,ar_gatherbill.def1 as vbillcode,0 as BQJF,

ar_gatheritem.money_cr - nvl(arap_debtstransfer.money,0) as BQDF,

nvl(arap_debtstransfer.money,0)-ar_gatheritem.money_cr as money_cr

from ar_gatherbill ar_gatherbill left join ar_gatheritem ar_gatheritem on ar_gatherbill.pk_gatherbill = ar_gatheritem.pk_gatherbill

left join (select sum(money) as money,pk_item pk_item,outobj outobj from arap_debtstransfer where nvl(dr,0) = 0 group by pk_item,outobj)

arap_debtstransfer on ar_gatheritem.pk_gatheritem = arap_debtstransfer.pk_item

where nvl(ar_gatherbill.dr,0) = 0 and nvl(ar_gatheritem.dr,0) = 0 and ar_gatherbill.isinit = 'N' union all

--查询销售出库金额 销售出库表体

select substr(ic_saleout_b.dbizdate,0,10) as billdate,ic_saleout_h.cfanaceorgoid as pk_org,

ic_saleout_b.casscustid as ordercubasdoc,ic_saleout_b.cinvoicecustid as customer,'销售出库单' as billtype,ic_saleout_h.vbillcode as billno,ic_saleout_b.vfirstbillcode as vbillcode,SUM(ic_saleout_b.norigtaxmny)as BQJF,0 as BQDF,SUM(ic_saleout_b.norigtaxmny) as money_cr from ic_saleout_h ic_saleout_h, ic_saleout_b ic_saleout_b where ic_saleout_h.cgeneralhid = ic_saleout_b.cgeneralhid and nvl(ic_saleout_b.dr,0) = 0 and nvl(ic_saleout_h.dr,0) = 0

group by substr(ic_saleout_b.dbizdate,0,10),ic_saleout_h.cfanaceorgoid,ic_saleout_b.casscustid,ic_saleout_b.cinvoicecustid,'销售出库单',ic_saleout_h.vbillcode,ic_saleout_b.vfirstbillcode

union all

--查询服务类物料

select substr(so_saleorder.dbilldate,0,10) as billdate,so_saleorder_b.csettleorgid as pk_org,so_saleorder.ccustomerid as ccustomerid,so_saleorder.cinvoicecustid as customer,'服务费' as billtype,''as billno,so_saleorder.vbillcode as vbillcode,sum(so_saleorder_b.nqtorigtaxnetprc) as BQJF,0 as BQDF,sum(so_saleorder_b.nqtorigtaxnetprc) as money_cr from so_saleorder ,so_saleorder_b so_saleorder_b,bd_material bd_material where so_saleorder.csaleorderid = so_saleorder_b.csaleorderid and nvl(so_saleorder.dr,0) = 0 and nvl(so_saleorder_b.dr,0) = 0 and bd_material.pk_material = so_saleorder_b.cmaterialvid and bd_material.fee = 'Y'

and so_saleorder_b.nqtorigtaxnetprc <> 0

group by so_saleorder.ccustomerid,so_saleorder.cinvoicecustid,substr(so_saleorder.dbilldate,0,10),so_saleorder_b.csettleorgid,'服务费','',so_saleorder.vbillcode,0

) AA

--where AA.customer = '10011A1000000000048D'

left join bd_customer bd_customer on bd_customer.pk_customer = AA.customer

where bd_customer.pk_custclass = '10011A100000000002S4'

order by AA.pk_org,AA.customer,AA.billdate,AA.billno

)BB

)CC

)DD

)EE

where EE.billdate between parameter('param1') and parameter('param2') and customer = (select pk_customer from bd_customer where code = parameter('param3')) and pk_org = parameter('param4')

order by EE.brn

)FF

)GGG

where s_billno is not null

order by pk_org,customer,billdate,qcrk desc

查询语句多，但是思路非常简单：先计算，再排序即可。在本地运行是，先把其中的param进行替换，在这里表示动态参数的意思。

Oracle相关文章

Oracle学习日记——时间日期范围处理

Oracle查询基础

Oracle学习日记——应用字符串

MySQL相关文章

MySQL学习系列之十五——MySQL插入数据

MySQL学习系列之十二——组合查询