(
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
)
sql执行时间过长,求帮忙看看,能不能优化
- 写回答
- 好问题 0 提建议
- 追加酬金
- 关注问题
- 邀请回答
-