SELECT T5.FACCOUNTID,
T5.FDATE,
T5.FNUMBER,
T6.FNUMBER,
T5.FEXPLANATION,
NULL,
NULL,
NULL,
NULL,
T5.FDATE,
T5.FDC,
T5.FAMOUNT,
NVL(T6.FSTRAMOUNT, 0) AS FSTRAMOUNT,
T5.FENTRYID
FROM (SELECT T3.FCMP,T3.FACCOUNTID,T3.FTRANSNO,T3.FDATE,T3.FNUMBER,T3.FDC,T3.FENTRYID,T3.FEXPLANATION,T3.FAMOUNT,NVL(T4.FC, 0) AS FC
FROM (SELECT B.FCMP,
B.FACCOUNTID,
B.FTRANSNO,
A.FDATE,
A.FNUMBER,
B.FDC,
B.FENTRYID,
B.FEXPLANATION,
B.FAMOUNT
FROM PL_VOUCHER A, PL_VOUCHERITEM B, PL_ACCOUNT C
WHERE A.FCMP = B.FCMP AND A.FVOUCHERID = B.FVOUCHERID AND
B.FCMP = C.FCMP AND B.FACCOUNTID = C.FACCOUNTID AND
(A.FDFLAG <> 'Y' OR A.FDFLAG IS NULL) AND
B.FDC = C.FDC AND C.FISACAGE = 'Y' AND
C.FISDETAIL = 'Y' AND A.FCMP = 'RW' AND
B.FACCOUNTID >= '1118' AND B.FACCOUNTID <= '1118' and
(A.FDATE >= '20090515' and A.FDATE <= '20090515')) T3,
(SELECT B.FCMP, B.FACCOUNTID, B.FTRANSNO, SUM(B.FAMOUNT) AS FC
FROM PL_VOUCHER A, PL_VOUCHERITEM B, PL_ACCOUNT C
WHERE A.FCMP = B.FCMP AND A.FVOUCHERID = B.FVOUCHERID AND
B.FCMP = C.FCMP AND B.FACCOUNTID = C.FACCOUNTID AND
(A.FDFLAG <> 'Y' OR A.FDFLAG IS NULL) AND
B.FDC <> C.FDC AND C.FISACAGE = 'Y' AND
C.FISDETAIL = 'Y' AND B.FTRANSNO IS NOT NULL AND
B.FTRANSNO <> 'null' AND A.FCMP = 'RW' AND
B.FACCOUNTID >= '1118' AND B.FACCOUNTID <= '1118' and
(A.FDATE >= '20090515' and A.FDATE <= '20090515')
GROUP BY B.FCMP, B.FACCOUNTID, B.FTRANSNO) T4
WHERE T3.FCMP = T4.FCMP(+) AND T3.FACCOUNTID = T4.FACCOUNTID(+) AND
T3.FTRANSNO = T4.FTRANSNO(+)) T5,
(SELECT B.FCMP,
B.FACCOUNTID,
B.FTRANSNO,
A.FDATE,
A.FNUMBER,
B.FEXPLANATION,
B.FAMOUNT AS FSTRAMOUNT
FROM PL_VOUCHER A, PL_VOUCHERITEM B, PL_ACCOUNT C
WHERE A.FCMP = B.FCMP AND A.FVOUCHERID = B.FVOUCHERID AND
B.FCMP = C.FCMP AND B.FACCOUNTID = C.FACCOUNTID AND
(A.FDFLAG <> 'Y' OR A.FDFLAG IS NULL) AND B.FDC <> C.FDC AND
C.FISACAGE = 'Y' AND C.FISDETAIL = 'Y' AND
B.FTRANSNO IS NOT NULL AND B.FTRANSNO <> 'null' AND
A.FCMP = 'RW' AND B.FACCOUNTID >= '1118' AND
B.FACCOUNTID <= '1118' and
(A.FDATE >= '20090515' and A.FDATE <= '20090515')) T6
WHERE T5.FCMP = T6.FCMP(+) AND T5.FACCOUNTID = T6.FACCOUNTID(+) AND
T5.FTRANSNO = T6.FTRANSNO(+) AND
T5.FC >= T5.FAMOUNT AND T5.FCMP = 'RW'
ORDER BY T5.FACCOUNTID, T5.FDATE, T5.FNUMBER, T5.FENTRYID
T5.FDATE,
T5.FNUMBER,
T6.FNUMBER,
T5.FEXPLANATION,
NULL,
NULL,
NULL,
NULL,
T5.FDATE,
T5.FDC,
T5.FAMOUNT,
NVL(T6.FSTRAMOUNT, 0) AS FSTRAMOUNT,
T5.FENTRYID
FROM (SELECT T3.FCMP,T3.FACCOUNTID,T3.FTRANSNO,T3.FDATE,T3.FNUMBER,T3.FDC,T3.FENTRYID,T3.FEXPLANATION,T3.FAMOUNT,NVL(T4.FC, 0) AS FC
FROM (SELECT B.FCMP,
B.FACCOUNTID,
B.FTRANSNO,
A.FDATE,
A.FNUMBER,
B.FDC,
B.FENTRYID,
B.FEXPLANATION,
B.FAMOUNT
FROM PL_VOUCHER A, PL_VOUCHERITEM B, PL_ACCOUNT C
WHERE A.FCMP = B.FCMP AND A.FVOUCHERID = B.FVOUCHERID AND
B.FCMP = C.FCMP AND B.FACCOUNTID = C.FACCOUNTID AND
(A.FDFLAG <> 'Y' OR A.FDFLAG IS NULL) AND
B.FDC = C.FDC AND C.FISACAGE = 'Y' AND
C.FISDETAIL = 'Y' AND A.FCMP = 'RW' AND
B.FACCOUNTID >= '1118' AND B.FACCOUNTID <= '1118' and
(A.FDATE >= '20090515' and A.FDATE <= '20090515')) T3,
(SELECT B.FCMP, B.FACCOUNTID, B.FTRANSNO, SUM(B.FAMOUNT) AS FC
FROM PL_VOUCHER A, PL_VOUCHERITEM B, PL_ACCOUNT C
WHERE A.FCMP = B.FCMP AND A.FVOUCHERID = B.FVOUCHERID AND
B.FCMP = C.FCMP AND B.FACCOUNTID = C.FACCOUNTID AND
(A.FDFLAG <> 'Y' OR A.FDFLAG IS NULL) AND
B.FDC <> C.FDC AND C.FISACAGE = 'Y' AND
C.FISDETAIL = 'Y' AND B.FTRANSNO IS NOT NULL AND
B.FTRANSNO <> 'null' AND A.FCMP = 'RW' AND
B.FACCOUNTID >= '1118' AND B.FACCOUNTID <= '1118' and
(A.FDATE >= '20090515' and A.FDATE <= '20090515')
GROUP BY B.FCMP, B.FACCOUNTID, B.FTRANSNO) T4
WHERE T3.FCMP = T4.FCMP(+) AND T3.FACCOUNTID = T4.FACCOUNTID(+) AND
T3.FTRANSNO = T4.FTRANSNO(+)) T5,
(SELECT B.FCMP,
B.FACCOUNTID,
B.FTRANSNO,
A.FDATE,
A.FNUMBER,
B.FEXPLANATION,
B.FAMOUNT AS FSTRAMOUNT
FROM PL_VOUCHER A, PL_VOUCHERITEM B, PL_ACCOUNT C
WHERE A.FCMP = B.FCMP AND A.FVOUCHERID = B.FVOUCHERID AND
B.FCMP = C.FCMP AND B.FACCOUNTID = C.FACCOUNTID AND
(A.FDFLAG <> 'Y' OR A.FDFLAG IS NULL) AND B.FDC <> C.FDC AND
C.FISACAGE = 'Y' AND C.FISDETAIL = 'Y' AND
B.FTRANSNO IS NOT NULL AND B.FTRANSNO <> 'null' AND
A.FCMP = 'RW' AND B.FACCOUNTID >= '1118' AND
B.FACCOUNTID <= '1118' and
(A.FDATE >= '20090515' and A.FDATE <= '20090515')) T6
WHERE T5.FCMP = T6.FCMP(+) AND T5.FACCOUNTID = T6.FACCOUNTID(+) AND
T5.FTRANSNO = T6.FTRANSNO(+) AND
T5.FC >= T5.FAMOUNT AND T5.FCMP = 'RW'
ORDER BY T5.FACCOUNTID, T5.FDATE, T5.FNUMBER, T5.FENTRYID
尽可能减少group、order by等时耗较高的sql;
尽可能控制可选择的结果集,能加到靠近表的里层,缩小结果集