SQL数据分析—查询银行账户余额明细列表

从系统导出了公司银卡的初期信息和收款付款明细数据,接下来要用SQL去对它做查询和分析。

一. 已有数据图示:

账户期初金额

收款明细

SQL数据分析—查询银行账户余额明细列表

付款明细

二. 查询目标数据列表图示:

SQL数据分析—查询银行账户余额明细列表

想要实现的查询结果

三. 银行余额算法逻辑说明:

  1. 上期结转金额(查询结果第一行的银行余额)= 查询日前一日的余额,本处业务发生日为2022/07/01,所以上期结转金额等于期初金额。若查询开始日为2022/08/01,则上期结转金额为235500;
  2. 收付款单据对应的银行余额(查询结果第二行开始的所有数据行的银行余额)=上一笔的银行余额+收款金额-付款金额

四. 解决方案演示:

1. 设计两张表,分别存放账户期初金额数据表和收付款明细表,表结构见下图:

SQL数据分析—查询银行账户余额明细列表

数据库表结构图示

2. 导入数据到数据库表内,结果见下图:

账户期初金额表数据

收付款明细表数据

3. 写SQL对数据进行查询和统计:

with temp1 as (select a.余额,'2022/7/1' as 日期,a.`账户名称`,'2022/8/30' as 结束日期
from `账户期初金额` a
where a.`账户名称`='农行8751'),
temp2 as (
select sum(if(ISNULL(b.`收款金额`),0,b.收款金额))-sum(if(ISNULL(b.`付款金额`),0,b.`付款金额`)) as 收付款结余
from 收付款明细表 b inner join temp1 on b.`银行账户`=temp1.`账户名称`
where b.`日期`<temp1.`日期`
),
temp3 as (
select temp1.余额+if(isnull(temp2.收付款结余),0,temp2.收付款结余) as 银行余额
from temp1,temp2
),
temp4 as (select row_number() over(order by c.`日期`) as num,c.`日期`,c.`单据类型`,c.`单据号`,c.`摘要`,c.收款金额,c.`付款金额`
from 收付款明细表 c inner join temp1 on c.`银行账户` = temp1.`账户名称`
where c.日期>=temp1.`日期` and c.日期<=temp1.结束日期)
select null as 日期,null as 单据类型,null as 单据号,'上期转结' as 摘要,null as 收款金额,null as 付款金额, temp3.银行余额
from temp3
union all
select temp4.`日期`,temp4.`单据类型`,temp4.`单据号`,temp4.`摘要`,temp4.收款金额,temp4.`付款金额`,temp3.银行余额+(select sum(if(ISNULL(t5.`收款金额`),0,t5.收款金额))-sum(if(ISNULL(t5.`付款金额`),0,t5.`付款金额`))
from temp4 as t5
where t5.num<=temp4.num
) as 银行余额
from temp4 inner join temp3

上述SQL实现了查询2022/7/1日到200/8/30日之间的银行余额变化明细列表,执行结果如下:

SQL数据分析—查询银行账户余额明细列表

2022/7/1-2022/8/30银行余额变动明细

Ps. 上述SQL使用了低版本不支持的语法,需在MySQL8.0以上版本才能顺利执行。

五. 案例素材文档下载

链接:https://pan.baidu.com/s/1UbIqVlvdQNcnOfS4AYhyUA

提取码:wmkt

SQL数据分析—查询银行账户余额明细列表

​希望文本能帮助到更多有同类需求的使用数据库做数据分析的学员。

如需系统学习使用SQL进行数据分析工作,可以查看吴明老师的​​《数据分析之SQL零基础到实战应用高阶课》​​课程。​

该课程可以使学员以最少的时间,全面系统学习用SQL做数据分析的技能。