因业务原因,ERP系统里面的一些流水账查询经常是一个大麻烦,或者说让大家都头痛的问题。最近因之前的麻烦没处理好,再次进入调整的步伐。
还好,之前已经让开发通过业务逻辑来把SQL已经调的很简单了。现在我就只需要从写SQL技巧方面改良一下就差不多了。
原SQL:
select b.SEQNO,FK,LINEID,LASTMODIFYTIME,CREATETIME,b.BRANCHID,b.CUSTOMERID,b.BILLINGDATE, BILLID,ABSTRACT,AMTOFDEBITSIDE,AMTOFCREDITSIDE,
BALANCE,PREPAIDBAL,CARRYOVERNO,DELETEFLAG,NOTE,VERSION,p_billingDate from tb_gos_account_o_custrun b
/* inner join (select max(SEQNO) SEQNO,branchid,customerid
From tb_gos_account_o_custrun a where a.billingdate <= p_billingDate
and a.branchid=p_branchid
group by branchid,customerid) a on a.seqno = b.seqno and a.customerid = b.customerid and a.branchid=b.branchid
*/
where b.branchid=p_branchid and (seqno,branchid,customerid) in
(select max(SEQNO) SEQNO,branchid,customerid
From tb_gos_account_o_custrun a where a.billingdate <= p_billingDate
and a.branchid=p_branchid
group by branchid,customerid)
;
上面的语句就是从join 换到in ,从in 换到JOIN,换汤不换药,用起来差别没什么区别。两个传入参数p_branchid、p_billingDate。
改后的SQL:
select * from (
select b.SEQNO,FK,LINEID,LASTMODIFYTIME,CREATETIME,b.BRANCHID,b.CUSTOMERID,b.BILLINGDATE,
BILLID,ABSTRACT,AMTOFDEBITSIDE,AMTOFCREDITSIDE,
BALANCE,PREPAIDBAL,CARRYOVERNO,DELETEFLAG,NOTE,VERSION,p_billingDate,
row_number() OVER(PARTITION BY branchid,customerid ORDER BY seqno desc) as row_flg
from tb_gos_account_o_custrun b
where B.branchid=p_branchid and B.billingdate<=p_billingDate
) temp
where temp.row_flg=1
改后的SQL从执行计划层面较原来的要简单很多。避免了表的重复扫。使用了窗口函数代替原来的聚合函数。可读性提高、代码精简。后面的查询条件基本上不能过滤多少数据,而此流水账表里面的数据是比较大的。所以目前用第二种代替了前面的一种。从业务逻辑上,要实现的是,在流水账目录中查询某公司所有客户最新的一笔流水账。
改写后,比对了2种查询性能速度的差别。。。着实第2种赞啊~~