Oracle三表关联查询需求及优化后SQL语句咨询

需求匹配验证 #

你的SQL核心逻辑符合需求：

• 使用LEFT OUTER JOIN关联主表与两个子表，确保主表CHANGEREQUESTS的数据全部返回，子表无对应数据时字段显示为NULL，满足子表可选的要求
• 包含了主表、子表的目标字段，以及关联字典表获取文本值的逻辑，还有自定义的状态计算字段CMP STATUS

但存在一处关键逻辑错误：

原SQL中主表的时间筛选条件cr.createdon >= ...和cr.lastmodifiedon <= ...写在了第二个LEFT JOIN的关联条件之后，这会将时间条件作为子表关联的一部分，导致主表中不符合时间范围的记录也可能被返回（当子表无匹配数据时，LEFT JOIN会保留主表所有记录）。正确的做法是将主表筛选条件放到WHERE子句中。

优化建议 #

1. 修正时间条件位置 #

将主表的时间筛选移至WHERE子句，确保只有符合时间范围的主表记录被查询：

WHERE 
    cr.CREATEDON >= TO_DATE('07-02-2024', 'dd-mm-yyyy')
    AND cr.LASTMODIFIEDON <= TO_DATE('14-02-2024', 'dd-mm-yyyy')

2. 替换标量子查询为JOIN #

原SQL中获取TYPE OF CHANGE和CURRENT STATUS的标量子查询会逐行执行，数据量大时性能较差。改为LEFT JOIN方式关联字典表，提升查询效率：

LEFT JOIN CHANGETYPES ct ON cr.CHANGETYPEID = ct.CHANGETYPEID
LEFT JOIN APP_WF.WF_STATUS st ON cr.STATUSID = st.STATUS_ID

3. 修正重复列别名 #

原SQL中cr.CREATEDBY和pph.CREATED_BY都使用了"CREATED BY"别名，会导致结果集列名冲突，需修改其中一个别名，例如将pph.CREATED_BY改为"PPH CREATED BY"。

4. 字段存在性校验 #

你提供的主表结构中未列出DESCRIPTION、CREATEDBY、CREATEDON、LASTMODIFIEDBY、LASTMODIFIEDON、STATUSID、BAND字段，需确认这些字段确实存在于CHANGEREQUESTS表中，避免执行报错。

5. 索引优化 #

为提升查询性能，建议确保以下字段存在合适的索引：

• CHANGEREQUESTS：CHANGEREQUESTID（主键默认已有）、CREATEDON、LASTMODIFIEDON、CHANGETYPEID、STATUSID
• TBL_PRE_POST_HOTO：CHANGEREQUEST_ID
• TBL_CMP_EMG_DATA_INFO：CHANGE_REQUEST_ID
• CHANGETYPES：CHANGETYPEID
• APP_WF.WF_STATUS：STATUS_ID

优化后的完整SQL #

SELECT 
    cr.CHANGEREQUESTID AS "CHANGE REQUEST ID",
    cr.CHANGEREQUESTNUMBER AS "CHANGE REQUEST NO",
    ct.CHANGETYPE AS "TYPE OF CHANGE",
    cr.NETWORKTYPE AS "NETWORK TYPE",
    cr.STATENAME AS "STATE NAME",
    cr.CITYNAME AS "CITY NAME",
    cr.DESCRIPTION,
    cr.CREATEDBY AS "CREATED_BY",
    cr.CREATEDON AS "CREATED DATE",
    cr.LASTMODIFIEDBY AS "LAST MODIFIED BY",
    cr.LASTMODIFIEDON AS "LAST MODIFIED DATE",
    st.STATUS AS "CURRENT STATUS",
    cr.BAND,
    
    pph.SAP_ID AS "SAP ID",
    pph.LATITUDE,
    pph.LONGITUDE,
    pph.SAP_ID_TYPE AS "SAP ID TYPE",
    pph.SAPID_REMARKS AS "SAP ID CREATION REMARKS",
    pph.CREATION_SAPID AS "CREATED SAP ID",
    pph.CREATED_BY AS "PPH CREATED BY",
    pph.APPROVED_BY AS "SAPID APPROVED BY",
    pph.APPROVE_REJECT AS "APPROVE REJECT REMARKS",
    pph.APPROVED_DATE AS "APPROVED DATE",
    pph.REJECT_REMARKS AS "SAPID REJECT REMARKS",
    pph.REJECTED_BY AS "SAPID REJECTED BY",
    pph.REJECTED_DATE AS "REJECTED DATE",
    
    ce.SAP_ID AS "SITE REPLACEMENT SAP ID",
    ce.DELETION_SITE_SAPID AS "DELETION SAP ID",
    ce.RCOM_ID_NEW_SITE AS "RCOM ID NEW SITE",
    ce.NEW_LATITUDE AS "NEW LATITUDE",
    ce.NEW_LONGITUDE AS "NEW LONGITUDE",
    ce.REMARKS AS "REMARKS",
    ce.CREATED_BY AS "SITE REPLACEMENT CREATED BY",
    ce.APPROVE_REJECT AS "SAP ID Approved/Rejected By",
    ce.APPROVED_BY AS "SITE REPLACEMENT APPROVED BY",
    ce.APPROVED_DATE AS "SITE REPLACEMENT APPROVED DATE",
    ce.REJECT_REMARKS AS "SITE REPLACEMENT REJECT REMARKS",
    ce.REJECTED_BY AS "SITE REPLACEMENT REJECTED BY",
    ce.REJECTED_DATE AS "SITE REPLACEMENT REJECTED DATE",
    CASE cr.STATUSID WHEN 825 THEN 'Close' ELSE 'Open' END AS "CMP STATUS"       
FROM CHANGEREQUESTS cr
LEFT JOIN CHANGETYPES ct ON cr.CHANGETYPEID = ct.CHANGETYPEID
LEFT JOIN APP_WF.WF_STATUS st ON cr.STATUSID = st.STATUS_ID
LEFT JOIN TBL_PRE_POST_HOTO pph ON cr.CHANGEREQUESTID = pph.CHANGEREQUEST_ID
LEFT JOIN TBL_CMP_EMG_DATA_INFO ce ON cr.CHANGEREQUESTID = ce.CHANGE_REQUEST_ID
WHERE 
    cr.CREATEDON >= TO_DATE('07-02-2024', 'dd-mm-yyyy')
    AND cr.LASTMODIFIEDON <= TO_DATE('14-02-2024', 'dd-mm-yyyy')
ORDER BY cr.LASTMODIFIEDON DESC;

内容的提问来源于stack exchange，提问作者Nadeem