IronMan1982
03-11-2010, 11:25
Chào các bạn!
Tôi có 2 truy vấn như sau. Các bạn giải thích giúp tôi truy vấn nào có tốc độ truy vấn nhanh hơn nhé.
---1. Truy vấn 1
SELECT
C_BPARTNER_ID
,TYPESERVICE
,INCREASEADJUST
,DECREASEADJUST
FROM
C_BPARTNER
INNER JOIN
(
SELECT
CCL.C_BPARTNER_ID
,RL.VALUE TYPESERVICE
,SUM(C.STATEMENTDIFFERENCE) INCREASEADJUST
FROM
BH1000CASHCOLLECTION CCL
LEFT JOIN
BH1000CASH C
ON
CCL.BH1000CASHCOLLECTION_ID = C.BH1000CASHCOLLECTION_ID
LEFT JOIN
AD_REF_LIST RL ON RL.VALUE = C.TYPESERVICE
WHERE ( 1 = 1 )
AND RL.ISACTIVE = 'Y'
AND CCL.DOCSTATUS = 'CO'
AND CCL.C_DOCTYPE_ID = (SELECT VALUE FROM BH1000CONFIG WHERE PARAMETERNAME = 'DIEUCHINHTANG')
AND RL.AD_REFERENCE_ID = (SELECT VALUE FROM BH1000CONFIG WHERE PARAMETERNAME = 'DICHVULISTID')
AND CCL.STATEMENTDATE BETWEEN TO_DATE('01/09/2010','DD/MM/YYYY') AND TO_DATE('30/10/2010','DD/MM/YYYY')
AND CCL.C_BPARTNER_ID = 1006830
GROUP BY
CCL.C_BPARTNER_ID
,RL.VALUE
) TABLE_DIEUCHINHTANG
ON
C_BPARTNER.C_BPARTNER_ID = TABLE_DIEUCHINHTANG.C_BPARTNER_ID
INNER JOIN
(
SELECT
CCL.C_BPARTNER_ID
,RL.VALUE TYPESERVICE
,SUM(C.STATEMENTDIFFERENCE) DECREASEADJUST
FROM
BH1000CASHCOLLECTION CCL
LEFT JOIN
BH1000CASH C
ON
CCL.BH1000CASHCOLLECTION_ID = C.BH1000CASHCOLLECTION_ID
LEFT JOIN
AD_REF_LIST RL ON RL.VALUE = C.TYPESERVICE
WHERE ( 1 = 1 )
AND RL.ISACTIVE = 'Y'
AND CCL.DOCSTATUS = 'CO'
AND CCL.C_DOCTYPE_ID = (SELECT VALUE FROM BH1000CONFIG WHERE PARAMETERNAME = 'DIEUCHINHGIAM')
AND RL.AD_REFERENCE_ID = (SELECT VALUE FROM BH1000CONFIG WHERE PARAMETERNAME = 'DICHVULISTID')
AND CCL.STATEMENTDATE BETWEEN TO_DATE('01/09/2010','DD/MM/YYYY') AND TO_DATE('30/10/2010','DD/MM/YYYY')
AND CCL.C_BPARTNER_ID = 1006830
GROUP BY
CCL.C_BPARTNER_ID
,RL.VALUE
) TABLE_DIEUCHINHGIAM
ON
C_BPARTNER.C_BPARTNER_ID = TABLE_DIEUCHINHGIAM.C_BPARTNER_ID
--2.Truy vấn 2
SELECT
C_BPartner_ID
,TypeService
,SUM(BeginningBal) BeginningBal
,SUM(MustPayAmountTK) MustPayAmountTK
,SUM(PaidAmountTK_GNT) PaidAmountTK_GNT
,SUM(PaidAmountTK_PT) PaidAmountTK_PT
,SUM(IncreaseAdjust) IncreaseAdjust
,SUM(DecreaseAdjust) DecreaseAdjust
FROM
(
-- BEGIN Dieu chinh tang
SELECT C_BPartner_ID,
0 AS BeginningBal, 0 AS MustPayAmountTK, 0 AS PaidAmountTK_GNT, 0 AS PaidAmountTK_PT, NVL(IncreaseAdjust, 0) AS IncreaseAdjust, 0 AS DecreaseAdjust,
rl.Value TypeService
FROM AD_Ref_List rl
LEFT JOIN
(
SELECT NVL(SUM(IncreaseAdjust), 0) IncreaseAdjust, C_BPartner_ID, TypeService
FROM
(
-- Dieu chinh tang
SELECT
SUM(c.StatementDifference) AS IncreaseAdjust, bp.PARTNERPARENT C_BPartner_ID, rl.Value TypeService
FROM BH1000CashCollection ccl
LEFT JOIN BH1000Cash c ON ccl.BH1000CashCollection_ID = c.BH1000CashCollection_ID
LEFT JOIN AD_Ref_List rl ON rl.Value = c.TypeService
INNER JOIN C_BPartner bp ON ccl.C_BPartner_ID = bp.C_BPartner_ID
WHERE ( 1 = 1 )
AND ccl.C_DocType_ID = (SELECT Value FROM BH1000Config WHERE ParameterName = 'DieuChinhTang')
AND rl.IsActive = 'Y'
AND rl.AD_Reference_ID = (SELECT Value FROM BH1000Config WHERE ParameterName = 'DichVuListID')
AND ccl.DocStatus = 'CO'
AND ccl.StatementDate BETWEEN to_date('01/09/2010','dd/MM/yyyy') AND to_date('30/10/2010','dd/MM/yyyy')
AND bp.PARTNERPARENT = 1006830
GROUP BY bp.PARTNERPARENT, rl.Value
)
GROUP BY C_BPartner_ID, TypeService
) dct
ON dct.TypeService = rl.Value
WHERE rl.AD_Reference_ID = (SELECT Value FROM BH1000Config WHERE ParameterName = 'DichVuListID')
AND rl.IsActive = 'Y'
-- END Dieu chinh tang
UNION ALL
-- BEGIN Dieu chinh giam
SELECT C_BPartner_ID,
0 AS BeginningBal, 0 AS MustPayAmountTK, 0 AS PaidAmountTK_GNT, 0 AS PaidAmountTK_PT, 0 AS IncreaseAdjust, NVL(DecreaseAdjust, 0) AS DecreaseAdjust,
rl.Value TypeService
FROM AD_Ref_List rl
LEFT JOIN
(
SELECT NVL(SUM(DecreaseAdjust), 0) DecreaseAdjust, C_BPartner_ID, TypeService
FROM
(
-- Dieu chinh giam
SELECT
SUM(c.StatementDifference) AS DecreaseAdjust, bp.PARTNERPARENT C_BPartner_ID, rl.Value TypeService
FROM BH1000CashCollection ccl
LEFT JOIN BH1000Cash c ON ccl.BH1000CashCollection_ID = c.BH1000CashCollection_ID
LEFT JOIN AD_Ref_List rl ON rl.Value = c.TypeService
INNER JOIN C_BPartner bp ON ccl.C_BPartner_ID = bp.C_BPartner_ID
WHERE ( 1 = 1 )
AND ccl.C_DocType_ID = (SELECT Value FROM BH1000Config WHERE ParameterName = 'DieuChinhGiam')
AND rl.IsActive = 'Y'
AND rl.AD_Reference_ID = (SELECT Value FROM BH1000Config WHERE ParameterName = 'DichVuListID')
AND ccl.DocStatus = 'CO'
AND ccl.StatementDate BETWEEN to_date('01/09/2010','dd/MM/yyyy') AND to_date('30/10/2010','dd/MM/yyyy')
AND bp.PARTNERPARENT = 1006830
GROUP BY bp.PARTNERPARENT, rl.Value
)
GROUP BY C_BPartner_ID, TypeService
) dcg
ON dcg.TypeService = rl.Value
WHERE rl.AD_Reference_ID = (SELECT Value FROM BH1000Config WHERE ParameterName = 'DichVuListID')
AND rl.IsActive = 'Y'
-- END Dieu chinh giam
)
GROUP BY
C_BPartner_ID
,TypeService
Tôi có 2 truy vấn như sau. Các bạn giải thích giúp tôi truy vấn nào có tốc độ truy vấn nhanh hơn nhé.
---1. Truy vấn 1
SELECT
C_BPARTNER_ID
,TYPESERVICE
,INCREASEADJUST
,DECREASEADJUST
FROM
C_BPARTNER
INNER JOIN
(
SELECT
CCL.C_BPARTNER_ID
,RL.VALUE TYPESERVICE
,SUM(C.STATEMENTDIFFERENCE) INCREASEADJUST
FROM
BH1000CASHCOLLECTION CCL
LEFT JOIN
BH1000CASH C
ON
CCL.BH1000CASHCOLLECTION_ID = C.BH1000CASHCOLLECTION_ID
LEFT JOIN
AD_REF_LIST RL ON RL.VALUE = C.TYPESERVICE
WHERE ( 1 = 1 )
AND RL.ISACTIVE = 'Y'
AND CCL.DOCSTATUS = 'CO'
AND CCL.C_DOCTYPE_ID = (SELECT VALUE FROM BH1000CONFIG WHERE PARAMETERNAME = 'DIEUCHINHTANG')
AND RL.AD_REFERENCE_ID = (SELECT VALUE FROM BH1000CONFIG WHERE PARAMETERNAME = 'DICHVULISTID')
AND CCL.STATEMENTDATE BETWEEN TO_DATE('01/09/2010','DD/MM/YYYY') AND TO_DATE('30/10/2010','DD/MM/YYYY')
AND CCL.C_BPARTNER_ID = 1006830
GROUP BY
CCL.C_BPARTNER_ID
,RL.VALUE
) TABLE_DIEUCHINHTANG
ON
C_BPARTNER.C_BPARTNER_ID = TABLE_DIEUCHINHTANG.C_BPARTNER_ID
INNER JOIN
(
SELECT
CCL.C_BPARTNER_ID
,RL.VALUE TYPESERVICE
,SUM(C.STATEMENTDIFFERENCE) DECREASEADJUST
FROM
BH1000CASHCOLLECTION CCL
LEFT JOIN
BH1000CASH C
ON
CCL.BH1000CASHCOLLECTION_ID = C.BH1000CASHCOLLECTION_ID
LEFT JOIN
AD_REF_LIST RL ON RL.VALUE = C.TYPESERVICE
WHERE ( 1 = 1 )
AND RL.ISACTIVE = 'Y'
AND CCL.DOCSTATUS = 'CO'
AND CCL.C_DOCTYPE_ID = (SELECT VALUE FROM BH1000CONFIG WHERE PARAMETERNAME = 'DIEUCHINHGIAM')
AND RL.AD_REFERENCE_ID = (SELECT VALUE FROM BH1000CONFIG WHERE PARAMETERNAME = 'DICHVULISTID')
AND CCL.STATEMENTDATE BETWEEN TO_DATE('01/09/2010','DD/MM/YYYY') AND TO_DATE('30/10/2010','DD/MM/YYYY')
AND CCL.C_BPARTNER_ID = 1006830
GROUP BY
CCL.C_BPARTNER_ID
,RL.VALUE
) TABLE_DIEUCHINHGIAM
ON
C_BPARTNER.C_BPARTNER_ID = TABLE_DIEUCHINHGIAM.C_BPARTNER_ID
--2.Truy vấn 2
SELECT
C_BPartner_ID
,TypeService
,SUM(BeginningBal) BeginningBal
,SUM(MustPayAmountTK) MustPayAmountTK
,SUM(PaidAmountTK_GNT) PaidAmountTK_GNT
,SUM(PaidAmountTK_PT) PaidAmountTK_PT
,SUM(IncreaseAdjust) IncreaseAdjust
,SUM(DecreaseAdjust) DecreaseAdjust
FROM
(
-- BEGIN Dieu chinh tang
SELECT C_BPartner_ID,
0 AS BeginningBal, 0 AS MustPayAmountTK, 0 AS PaidAmountTK_GNT, 0 AS PaidAmountTK_PT, NVL(IncreaseAdjust, 0) AS IncreaseAdjust, 0 AS DecreaseAdjust,
rl.Value TypeService
FROM AD_Ref_List rl
LEFT JOIN
(
SELECT NVL(SUM(IncreaseAdjust), 0) IncreaseAdjust, C_BPartner_ID, TypeService
FROM
(
-- Dieu chinh tang
SELECT
SUM(c.StatementDifference) AS IncreaseAdjust, bp.PARTNERPARENT C_BPartner_ID, rl.Value TypeService
FROM BH1000CashCollection ccl
LEFT JOIN BH1000Cash c ON ccl.BH1000CashCollection_ID = c.BH1000CashCollection_ID
LEFT JOIN AD_Ref_List rl ON rl.Value = c.TypeService
INNER JOIN C_BPartner bp ON ccl.C_BPartner_ID = bp.C_BPartner_ID
WHERE ( 1 = 1 )
AND ccl.C_DocType_ID = (SELECT Value FROM BH1000Config WHERE ParameterName = 'DieuChinhTang')
AND rl.IsActive = 'Y'
AND rl.AD_Reference_ID = (SELECT Value FROM BH1000Config WHERE ParameterName = 'DichVuListID')
AND ccl.DocStatus = 'CO'
AND ccl.StatementDate BETWEEN to_date('01/09/2010','dd/MM/yyyy') AND to_date('30/10/2010','dd/MM/yyyy')
AND bp.PARTNERPARENT = 1006830
GROUP BY bp.PARTNERPARENT, rl.Value
)
GROUP BY C_BPartner_ID, TypeService
) dct
ON dct.TypeService = rl.Value
WHERE rl.AD_Reference_ID = (SELECT Value FROM BH1000Config WHERE ParameterName = 'DichVuListID')
AND rl.IsActive = 'Y'
-- END Dieu chinh tang
UNION ALL
-- BEGIN Dieu chinh giam
SELECT C_BPartner_ID,
0 AS BeginningBal, 0 AS MustPayAmountTK, 0 AS PaidAmountTK_GNT, 0 AS PaidAmountTK_PT, 0 AS IncreaseAdjust, NVL(DecreaseAdjust, 0) AS DecreaseAdjust,
rl.Value TypeService
FROM AD_Ref_List rl
LEFT JOIN
(
SELECT NVL(SUM(DecreaseAdjust), 0) DecreaseAdjust, C_BPartner_ID, TypeService
FROM
(
-- Dieu chinh giam
SELECT
SUM(c.StatementDifference) AS DecreaseAdjust, bp.PARTNERPARENT C_BPartner_ID, rl.Value TypeService
FROM BH1000CashCollection ccl
LEFT JOIN BH1000Cash c ON ccl.BH1000CashCollection_ID = c.BH1000CashCollection_ID
LEFT JOIN AD_Ref_List rl ON rl.Value = c.TypeService
INNER JOIN C_BPartner bp ON ccl.C_BPartner_ID = bp.C_BPartner_ID
WHERE ( 1 = 1 )
AND ccl.C_DocType_ID = (SELECT Value FROM BH1000Config WHERE ParameterName = 'DieuChinhGiam')
AND rl.IsActive = 'Y'
AND rl.AD_Reference_ID = (SELECT Value FROM BH1000Config WHERE ParameterName = 'DichVuListID')
AND ccl.DocStatus = 'CO'
AND ccl.StatementDate BETWEEN to_date('01/09/2010','dd/MM/yyyy') AND to_date('30/10/2010','dd/MM/yyyy')
AND bp.PARTNERPARENT = 1006830
GROUP BY bp.PARTNERPARENT, rl.Value
)
GROUP BY C_BPartner_ID, TypeService
) dcg
ON dcg.TypeService = rl.Value
WHERE rl.AD_Reference_ID = (SELECT Value FROM BH1000Config WHERE ParameterName = 'DichVuListID')
AND rl.IsActive = 'Y'
-- END Dieu chinh giam
)
GROUP BY
C_BPartner_ID
,TypeService