(
SELECT
b.itemcode,
b.directionidx,
c.printname,
a.closedate,
a.serialno,
a.itemtype,
a.branchcode,
a.centercode,
a.yearmonth,
a.accbooktype,
a.accbookcode,
a.closedate,
a.accountcode,
a.checktype,
a.checkno,
a.currency,
a.itemcode,
a.directionidx,
b.remark,
b.debitsource,
b.creditsource,
b.debitdest,
b.creditdest,
a.balancesource,
a.balancedest,
a.operatecode,
a.operatebranch,
a.operatedate,
a.approvecode,
a.approvebranch,
a.approvedate,
a.checkflag,
a.checkyearmonth,
a.checknum,
a.dailytype,
a.voucherno,
a.receflag,
a.detailidx,
a.flag
FROM
AccDailyRec AS a,
accsubvoucherhis AS b,
(
(
SELECT
printname,
itemcode,
directionidx,
centercode
FROM
accitemvalidate
)
UNION ALL
(
SELECT
itemname AS printname,
itemcode,
'00' AS directionidx,
centercode
FROM
accitemdefine
)
) c
WHERE
a.centercode = b.centercode
AND a.yearmonth = b.yearmonth
AND a.voucherno = b.voucherno
AND c.itemcode = b.itemcode
AND c.directionidx = b.directionidx
AND c.centercode = b.centercode
AND b.itemcode <> '1001'
AND EXISTS (
SELECT
1
FROM
accmainvoucherhis er
WHERE
er.centercode = a.centercode
AND er.yearmonth = a.yearmonth
AND er.voucherno = a.voucherno
AND er.voucherflag = '4'
)
AND a.AccBookType = '02'
AND a.CenterCode = '1000000000'
AND a.AccBookCode = '11'
AND a.BranchCode = '1000000000'
AND a.ItemType = '0'
AND a.AccountCode = 'ZZZ'
AND 1 = 1
AND a.CloseDate >= '2017-03-01'
AND a.CloseDate <= '2017-03-31'
ORDER BY
voucherdate,
voucherno
)
UNION ALL
(
SELECT
b.itemcode,
b.directionidx,
c.printname,
a.closedate,
a.serialno,
a.itemtype,
a.branchcode,
a.centercode,
a.yearmonth,
a.accbooktype,
a.accbookcode,
a.closedate,
a.accountcode,
a.checktype,
a.checkno,
a.currency,
a.itemcode,
a.directionidx,
b.remark,
b.debitsource,
b.creditsource,
b.debitdest,
b.creditdest,
a.balancesource,
a.balancedest,
a.operatecode,
a.operatebranch,
a.operatedate,
a.approvecode,
a.approvebranch,
a.approvedate,
a.checkflag,
a.checkyearmonth,
a.checknum,
a.dailytype,
a.voucherno,
a.receflag,
a.detailidx,
a.flag
FROM
AccDailyRec AS a,
accsubvoucher AS b,
(
(
SELECT
printname,
itemcode,
directionidx,
centercode
FROM
accitemvalidate
)
UNION ALL
(
SELECT
itemname AS printname,
itemcode,
'00' AS directionidx,
centercode
FROM
accitemdefine
)
) c
WHERE
a.centercode = b.centercode
AND a.yearmonth = b.yearmonth
AND a.voucherno = b.voucherno
AND c.itemcode = b.itemcode
AND c.directionidx = b.directionidx
AND c.centercode = b.centercode
AND b.itemcode <> '1001'
AND EXISTS (
SELECT
1
FROM
accmainvoucher er
WHERE
er.centercode = a.centercode
AND er.yearmonth = a.yearmonth
AND er.voucherno = a.voucherno
AND er.voucherflag <> '4'
)
AND a.AccBookType = '02'
AND a.CenterCode = '1000000000'
AND a.AccBookCode = '11'
AND a.BranchCode = '1000000000'
AND a.ItemType = '0'
AND a.AccountCode = 'ZZZ'
AND 1 = 1
AND a.CloseDate >= '2017-03-01'
AND a.CloseDate <= '2017-03-31'
GROUP BY
b.voucherno,
b.itemcode
ORDER BY
voucherdate,
voucherno
)
uj5u.com熱心網友回復：
求來個大神幫忙看看，這段sql要執行好幾次，date區間越大，執行次數越多uj5u.com熱心網友回復：
重構代碼，這種寫法肯定慢uj5u.com熱心網友回復：
可以是 * 代替uj5u.com熱心網友回復：
這種看都不想看的= = 這換行，滿分。。。。
uj5u.com熱心網友回復：
可以用with tab_name as(select ....)后面的就可以直接用select * from tab_name 代替了uj5u.com熱心網友回復：
創建執行視圖試試。uj5u.com熱心網友回復：
臨時表，with tab_name as(select ....)，
程序
你選吧
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/92146.html
標籤：開發
上一篇：oracle 游標 使用后再回傳