sinat_39871578
Richard.Y
采纳率11.1%
2020-06-28 09:35

小白一枚,麻烦各位大佬帮忙看一下这个sql怎么优化啊!

create or replace view SO_MONEY_DETAIL as
select substr(snrh.dbilldate || snih.dbilldate, 0, 10) dbilldate,
substr(snrh.vorderbillcode || snih.vorderbillcode, 0, 40) ddh,
'' bah, snih.ntotalmny fpzje, snrh.total skje,
snih.retainage wk,0 rnreceivedmny,0 inreceivedmny,snih.vbillcode fph,
substr(snrh.cdept || snih.cdept, 0, 20) cdept,
snrh.billmaker ||','|| snih.billmaker billmaker,substr(snrh.sohbillmaker || snih.sohbillmaker, 0, 20) sohbillmaker
from (select srh.dbilldate, srh.effectbillcode, sum(srh.total) total,
srh.vorderbillcode, srh.cdept, srh.billmaker,soh.nreceivedmny,soh.billmaker sohbillmaker
from so_receipt_h srh left join so_order_h soh
on srh.vsrcid = soh.id
where srh.dr = 0
group by srh.dbilldate,srh.effectbillcode, vorderbillcode, srh.cdept,
srh.billmaker,soh.nreceivedmny,soh.billmaker) snrh
full outer join (select sih.dbilldate,sih.effectbillcode, sum(sih.ntotalmny) ntotalmny,
sih.vorderbillcode, sum(sih.retainage) retainage,
wm_concat(sih.vbillcode) vbillcode, sih.cdept,
sih.billmaker,soh.nreceivedmny,soh.billmaker sohbillmaker
from so_invoice_h sih left join so_order_h soh
on sih.vsrcid = soh.id
where sih.dr = 0
group by sih.vorderbillcode,sih.effectbillcode,sih.dbilldate, sih.cdept,
sih.billmaker,soh.nreceivedmny,soh.billmaker) snih
on snrh.vorderbillcode = snih.vorderbillcode
and snrh.dbilldate = snih.dbilldate
order by fph
/


  • 点赞
  • 写回答
  • 关注问题
  • 收藏
  • 复制链接分享
  • 邀请回答

1条回答

  • qq_37971212 顺流而上.. 9月前

    这种得拆分了吧,太长了,没见过这么长的sql

    点赞 评论 复制链接分享