用 JOIN 替換子查詢 - MYSQL

您能否建議我如何在獲取選擇列時替換子查詢以提高查詢性能：

選擇 sa.id 作為 patAppIdSA，
sa.rte_id 作為 patRTEId，
sa.case_type_id AS patAppCaseId,
DATE_FORMAT (sa.sa_app_start_date, '% m-% d-% Y') AS saDate,
DATE_FORMAT(sa.sa_app_starttime, '%h:%i %p') AS saTime,
CONCAT_WS(', ', usSA.lname, usSA.fname) 作為 saPhy,
fac.name 為 saFac，
CONCAT(pd.lname, ', ', pd.fname) AS patName,
sa.sa_patient_id AS patIdSA,
insComp.name AS insCompName,
insComp.claim_type AS insCompType,
insComp.rte_chk,
insData.provider 作為 patInsProvider，
insData.id AS insRecId,
insData.policy_number AS insPatPolicyNo,
insData.ins_caseid AS insPatCaseId,

**（選擇 rtme.id
FROM real_time_medicare_eligibility rtme
WHERE rtme.ins_data_id = insData.id
AND EB_responce != ''
ORDER BY rtme.request_date_time DESC
限制 0 , 1) 作為 rte_master_id,
(SELECT DATEDIFF('2018-04-13', DATE_FORMAT(rtme.request_date_time, '%Y-%m-%d'))
FROM real_time_medicare_eligibility rtme
WHERE rtme.ins_data_id = insData.id
AND EB_responce != ''
ORDER BY rtme.request_date_time DESC
限制 0 , 1) 作為 rte_passed_days**

FROM schedule_appointments sa
INNER JOIN 使用者 usSA
在 usSA.id = sa.sa_doctor_id
INNER JOIN 設施 fac
ON fac.id = sa.sa_facility_id
INNER JOIN 患者數據 pd
ON pd.id = sa.sa_patient_id
INNER JOIN insurance_case insCase
ON insCase.patient_id = sa.sa_patient_id
INNER JOIN insurance_data insData
ON insData.pid = sa.sa_patient_id
AND insData.ins_caseid = insCase.ins_caseid
INNER JOIN insurance_companies insComp
ON insComp.id = insData.provider
WHERE sa.sa_doctor_id IN (280, 102, 103, 100, 182, 282, 1, 237,
96, 105, 106, 107, 306, 303)
AND sa.sa_facility_id IN (1)
AND sa.sa_app_start_date = '2018-04-18'
並且 sa.sa_patient_app_status_id 不在（201、18、19、20、203）
AND sa.rte_id = '0'
AND insData.actInsComp = '1'
AND insData.type = 'primary'
和 insData.ins_caseid > 0
AND insData.ins_caseid = sa.case_type_id
AND insCase.ins_case_type = '1'
按 sa.sa_app_start_date、sa.sa_app_starttime 排序；

MYSQL 版本是 5.7.20。

我在這個問題中描述了一種模擬LATERAL連接的方法（CROSS / OUTER APPLY在某些 DBMS 中呼叫）：為每個作者選擇一個最新的文章。它通常對每個組的最大 n 個查詢（如您的查詢）和類似問題很有用。

訣竅是將內聯子查詢重寫為連接而不是派生表。子查詢成為ON條件的正確部分。由於您的兩個子查詢是相同的，除了返回的列，一個好處是我們現在只需要一個“橫向”連接，而不是兩個子查詢。

關於性能，不確定是否會顯著提高，但使用適當的索引，這種類型的重寫通常更快，特別是如果“基”表（insData在這種情況下，或者更確切地說是FROM子句中連接的乘積）不大.

您的查詢將被寫入（為清楚起見，省略了未更改的部分）：

SELECT sa.id AS patAppIdSA,
   --
   -- several columns omitted
   --
   insData.ins_caseid AS insPatCaseId,

   rtme.id AS rte_master_id,
   DATEDIFF('2018-04-13', DATE_FORMAT(rtme.request_date_time, '%Y-%m-%d'))
           AS rte_passed_days
FROM schedule_appointments sa
   --
   -- several joins omitted
   --
   LEFT JOIN real_time_medicare_eligibility AS rtme
   ON rtme.id =                      -- I assume this is
      (SELECT ri.id                  -- the Primary Key
       FROM  real_time_medicare_eligibility AS ri
       WHERE ri.ins_data_id = insData.id
         AND ri.EB_responce <> ''
       ORDER BY ri.request_date_time DESC
       LIMIT 1)
WHERE sa.sa_doctor_id IN (280 , 102, 103, 100, 182, 282, 1, 237,
                         96, 105, 106, 107, 306, 303)
   --
   -- WHERE predicates omitted
   --
   AND insCase.ins_case_type = '1'
ORDER BY sa.sa_app_start_date, sa.sa_app_starttime ;

引用自：https://dba.stackexchange.com/questions/203949