我有以下查詢需要提高性能。最大的問題是它多次呼叫同一個表，我正在嘗試看看是否有更好的方法來執行它。
SELECT tc.chargesID
,p1.payerID as paymentPlan_Payer_1
,p2.payerID as paymentPlan_Payer_2
,p3.payerID as paymentPlan_Payer_3
,p4.payerID as paymentPlan_Payer_4
FROM TableCharges tc
LEFT JOIN Sales_Enc se ON c.chargesID = se.salesencounterID
LEFT JOIN Sales_Enc_Payer sep1 ON sep.salesencounterID = se.salesencounterID and sep.salesCode = 1
LEFT JOIN Sales_Enc_Payer sep2 ON sep.salesencounterID = se.salesencounterID and sep.salesCode = 2
LEFT JOIN Sales_Enc_Payer sep3 ON sep.salesencounterID = se.salesencounterID and sep.salesCode = 3
LEFT JOIN Sales_Enc_Payer sep4 ON sep.salesencounterID = se.salesencounterID and sep.salesCode = 4
LEFT JOIN payer p1 ON sep1.payerID = p1.payerID
LEFT JOIN payer p2 ON sep2.payerID = p2.payerID
LEFT JOIN payer p3 ON sep3.payerID = p3.payerID
LEFT JOIN payer p4 ON sep4.payerID = p4.payerID
讓我知道你們的想法。
uj5u.com熱心網友回復：
根據@Charlieface 所說，嘗試只加入Sales_Enc_Payer一次，并將 salesCode = 1 連接過濾器移動到具有相應付款人表實體的連接，如下所示......
SELECT
tc.chargesID
, p1.payerID AS paymentPlan_Payer_1
, p2.payerID AS paymentPlan_Payer_2
, p3.payerID AS paymentPlan_Payer_3
, p4.payerID AS paymentPlan_Payer_4
FROM TableCharges tc
LEFT JOIN Sales_Enc se ON tc.chargesID = se.salesencounterID
LEFT JOIN Sales_Enc_Payer sep ON sep.salesencounterID = se.salesencounterID
LEFT JOIN payer p1 ON sep.payerID = p1.payerID
AND sep.salesCode = 1
LEFT JOIN payer p2 ON sep.payerID = p2.payerID
AND sep.salesCode = 2
LEFT JOIN payer p3 ON sep.payerID = p3.payerID
AND sep.salesCode = 3
LEFT JOIN payer p4 ON sep.payerID = p4.payerID
AND sep.salesCode = 4;
uj5u.com熱心網友回復：
我就是放不下這個。這就是你解決這個問題的方法。
SELECT tc.chargesID,
MAX(CASE WHEN sep1.salesCode = 1 THEN p1.payerID ELSE NULL END) as paymentPlan_Payer_1,
MAX(CASE WHEN sep1.salesCode = 2 THEN p1.payerID ELSE NULL END) as paymentPlan_Payer_2,
MAX(CASE WHEN sep1.salesCode = 3 THEN p1.payerID ELSE NULL END) as paymentPlan_Payer_3,
MAX(CASE WHEN sep1.salesCode = 4 THEN p1.payerID ELSE NULL END) as paymentPlan_Payer_4
FROM TableCharges tc
LEFT JOIN Sales_Enc se ON c.chargesID = se.salesencounterID
LEFT JOIN Sales_Enc_Payer sep1 ON sep.salesencounterID = se.salesencounterID and sep.salesCode IN (1,2,3,4)
LEFT JOIN payer p1 ON sep1.payerID = p1.payerID
GROUP BY tc.chargesID
現在我得到了我正在尋找的行。我設法消除了多個表呼叫，這是我想要的主要內容。
轉載請註明出處，本文鏈接：https://www.uj5u.com/qukuanlian/435014.html