【熊说星空银企】SQL简单账表开发：电子回单下载完整度与匹配度统计表

1.相关课程知识帖

SQL账表：企业会有需求实现一些针对于本企业自定义的报表数据展示，如通过插件开发的形式处理，会增加成本，而通过使用数据库SQL语言编写实现，则可以快速实现需求。

报表平台-新建直接SQL账表、如何制作“直接SQL账表”

（推荐官方课程！引导式课件，先SQL取数表写下SQL表、再写下关键字筛选项、设置汇总项、权限设置与发布）

直接SQL账表基础资料类型过滤条件支持多选

（支持关键字筛选项多选方式，where 字段 in (#关键字#)）

直接SQL账表、简单账表过滤行功能

（在设置的基础上添加过滤行功能）

【笔记】如何制作“直接SQL账表”

（小技巧：1. 关键字：可以把SQL语句中，需要由最终用户输入的条件，定位为关键字；
2. 排序：不能使用ORDER BY子句指定排序字段，请SELECT中添加一列row_number() over (order by t0.FBillNo desc) as FIDENTITYID实现）

直接SQL账表

（SQL设计器的使用，可以使用SQL设计器关联表）

2.需求说明

每次客户问我：为啥付款单关联查询不到回单？

为啥回单打印不出来了？

上线回单功能的客户很经常遇到的问题

出于回复客户问题和运维的工作量还要判断说到底是哪边的问题，

特此专门做了一张SQL数据表以便数据查询，方便运维。

需求对应SQL数据表

SQL拆建过程及小熊个人思考过程拆建（见本文附件）

本文将我自己写的SQL表放到下面供大家参考

关键核心就是把SQL取数表写出来

我使用的是SQL SERVER的

SELECT

t6.fbankacntid AS '银行账号内码', t6.faccnum AS '银行账号', t6.fbankid AS '银行内码', t6.fbanknum AS '银行编码', t6.fname AS '银行', t6.fdated AS '交易日期', t6.flowcount AS '交易明细数', ISNULL(t8.receiptnum, 0) AS '电子回单数',

CONCAT(CONVERT(VARCHAR(50), ISNULL(t8.receiptnum, 0)* 100 / t6.flowcount), '%') AS '回单下载完整度', ISNULL(t8.receiptflownum, 0) AS '已匹配交易明细回单数', 

CONCAT(CONVERT(VARCHAR(50), ISNULL(t8.receiptflownum, 0)* 100 / t6.flowcount),  '%') AS '回单匹配完整度', 

ROW_NUMBER() OVER(order by t6.fbankacntid, t6.fdated) as '序号列' 

--写这句的目的是因为不能使用Order By子句指定排序字段

FROM 

(SELECT t3.fbankacntid, t3.faccnum, t3.fbanknum, t3.fbankid, t3.fname, t5.fdated, t5.fdaten, t5.flowcount FROM (SELECT t1.fbankacntid, t1.fnumber AS faccnum, t1.fbankid, t21.fname, t22.fnumber AS fbanknum FROM T_CN_BANKACNT t1 LEFT JOIN T_BD_BANK_L t21 ON t1.fbankid = t21.fbankid LEFT JOIN T_BD_BANK t22 ON t1.fbankid = t22.fbankid WHERE t1.fissupbank = '1') t3 LEFT JOIN ( SELECT faccountid, fdated, fdaten, count(FID) AS FLOWCOUNT FROM ( SELECT FACCOUNTID, CONVERT( nvarchar(10), ftransdate, 120) AS fdated, cast( convert(varchar, ftransdate, 112) as int ) AS fdaten, FID FROM T_CN_BANKCASHFLOW ) t4 GROUP BY faccountid, fdated, fdaten ) t5 ON t3.fbankacntid = t5.faccountid ) t6 

LEFT JOIN

 ( SELECT faccountid, fdated, fdaten, count(fid) AS receiptnum, count( nullif(fsrcbillno, '') ) AS receiptflownum FROM ( SELECT faccountid, CONVERT( nvarchar(10), fdate, 120 ) AS fdated, cast( convert(varchar, fdate, 112) as int ) AS fdaten, FID, FSRCBILLNO FROM T_WB_RECEIPT ) t7 GROUP BY faccountid, fdated, fdaten ) t8 

ON t6.fdated = t8.fdated and t6.fbankacntid = t8.faccountid

3.操作步骤

BOS开发平台中选择网上银行模块

3.1新增-直接SQL账表

编号：report_receiptmatch

名称：电子回单下载完整度与匹配度统计表

3.2添加关键字

3.3SQL取数表

SELECT t6.fbankacntid AS '银行账号内码',t6.faccnum AS '银行账号',t6.fbankid AS '银行内码',t6.fbanknum AS '银行编码',t6.fname AS '银行',
t6.fdated AS '交易日期',t6.flowcount AS '交易明细数',ISNULL(t8.receiptnum,0) AS '电子回单数',
CONCAT(CONVERT(VARCHAR(50),ISNULL(t8.receiptnum,0)*100/t6.flowcount),'%') AS '回单下载完整度'
,ISNULL(t8.receiptflownum,0) AS '已匹配交易明细回单数',
CONCAT(CONVERT(VARCHAR(50),ISNULL(t8.receiptflownum,0)*100/t6.flowcount),'%') AS '回单匹配完整度',
ROW_NUMBER() OVER(order by t6.fbankacntid,t6.fdated) as '序号列' --增加序号列
FROM
(
SELECT t3.fbankacntid,t3.faccnum,t3.fbanknum,t3.fbankid,t3.fname,t5.fdated,t5.fdaten,t5.flowcount FROM 
   (SELECT t1.fbankacntid,t1.fnumber AS faccnum,t1.fbankid,t21.fname,t22.fnumber AS fbanknum FROM T_CN_BANKACNT t1 
   LEFT JOIN T_BD_BANK_L t21 ON t1.fbankid=t21.fbankid 
   LEFT JOIN T_BD_BANK t22 ON t1.fbankid=t22.fbankid
   WHERE t1.fissupbank ='1') 
   t3 
LEFT JOIN 
   (SELECT faccountid,fdated,fdaten,count(FID) AS FLOWCOUNT FROM 
    (SELECT FACCOUNTID,CONVERT(nvarchar(10),ftransdate, 120) AS fdated,cast(convert(varchar,ftransdate,112) as int) AS fdaten,FID FROM T_CN_BANKCASHFLOW ) t4  
    GROUP BY faccountid,fdated,fdaten) 
   t5 
ON t3.fbankacntid=t5.faccountid
) t6
LEFT JOIN
(SELECT faccountid,fdated,fdaten,count(fid) AS receiptnum,count(nullif(fsrcbillno,'')) AS receiptflownum  FROM (SELECT faccountid,CONVERT(nvarchar(10),fdate, 120) AS fdated,cast(convert(varchar,fdate,112) as int) AS fdaten,FID,FSRCBILLNO FROM T_WB_RECEIPT) t7 GROUP BY faccountid,fdated,fdaten
) t8
ON
t6.fdated=t8.fdated and t6.fbankacntid=t8.faccountid
WHERE 
faccnum in (#FBANKACCNUM#)
AND
t6.fdated >= '#FBeginDate#'
AND
t6.fdated <='#FEndDate#'

3.4定义显示列信息

3.5合计列

3.6列表条件格式化

3.7显示效果

3.8单据体添加过滤行

3.9新增权限对象

3.10发布

3.11分配权限

3.12预览查看