前言:
今天看官们对“oracle期末考试简答题”都比较重视,看官们都需要学习一些“oracle期末考试简答题”的相关资讯。那么小编也在网摘上汇集了一些有关“oracle期末考试简答题””的相关资讯,希望同学们能喜欢,兄弟们快快来学习一下吧!客户明细账是一个非常重要的报表,企业中所有和客户的往来资金都会在上面进行判断,那么,如何在数据库中做一个使用,且准确的客户明细账呢。
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学习系列之十二——组合查询
标签: #oracle期末考试简答题