如何用JOIN与LEFT JOIN合并三个复杂查询表并解决语法错误

阿华AIGC实验室

2026-5-13

解决多查询表合并的JOIN语法问题

我懂你现在卡在哪了——三个单独跑都没问题的查询，想要拼在一起用JOIN和LEFT JOIN合并时，总因为括号的问题报错对吧？其实核心就是每个独立的子查询必须用括号完整包裹，同时要保证整个SQL的括号嵌套逻辑完全正确。

你想要的合并逻辑是先把表A和表B做内连接，再把结果和表C做左连接，那我们只需要把每个表的原始查询都变成带括号的派生表，再按照你的逻辑拼接就行，具体正确的SQL写法如下：

SELECT * 
FROM (
    -- TABLE A的完整查询，包裹成派生表并指定别名A
    (select maxxx.id_demande_diffusion AS ID_DIFFUSION, maxxx.id_notification as ID_NOTIFICATION, maxxx.cd_organisation_client as ID_ENTITE, maxxx.cod_entrep as ID_ENTITE_GARANTE, maxxx.cd_canal as CD_CANAL, maxxx.id_demande_diffusion_originale as ID_DIFFUSION_PARENT, maxxx.ref_maquette as REF_MAQUETTE, maxxx.qualification_canal as QUALIFICATION_CANAL, maxxx.ger_id_pli as ID_PLI_GER, case when maxxx.typ_mvt="S" then 1 else 0 end AS TOP_SUPP, case when maxxx.typ_mvt = "S" then to_date(substr(maxxx.dt_capt, 1, 11)) else null end AS DT_SUPP, minnn.typ_mvt as MIN_MVT, maxxx.typ_mvt as MAX_MVT, case when minnn.typ_mvt = 'C' then 'C' else 'M' end as TYP_MVT from (select s.id_demande_diffusion, s.dt_capt, s.typ_mvt from ${use_database}.pz_send_demande_diffusion as s join (select id_demande_diffusion, min(dt_capt) as dtmin from ${use_database}.pz_send_demande_diffusion group by id_demande_diffusion) as minn on minn.id_demande_diffusion=s.id_demande_diffusion and s.dt_capt=minn.dtmin ) as minnn join (select s.id_demande_diffusion, s.typ_mvt, s.id_notification, s.dt_capt, s.cd_organisation_client, s.cod_entrep, s.cd_canal, s.id_demande_diffusion_originale, s.ref_maquette, s.qualification_canal, s.ger_id_pli from ${use_database}.pz_send_demande_diffusion as s join (select id_demande_diffusion, max(dt_capt) as dtmax from ${use_database}.pz_send_demande_diffusion group by id_demande_diffusion) as maxx on s.id_demande_diffusion=maxx.id_demande_diffusion and s.dt_capt=maxx.dtmax)as maxxx on minnn.id_demande_diffusion=maxxx.id_demande_diffusion) AS A
    JOIN
    -- TABLE B的完整查询，包裹成派生表并指定别名B
    (select maxxx.id_notification as ID_NOTIFICATION, maxxx.cd_type_destinataire as CD_TYPE_DESTINATAIRE, case when maxxx.cd_type_destinataire = "IDGRC" then maxxx.destinataire else null end AS ID_PERSONNE, case when maxxx.cd_type_destinataire = "MAIL" then maxxx.destinataire else null end AS EMAIL_DESTINATAIRE, case when maxxx.cd_type_destinataire = "SMS" then maxxx.destinataire else null end AS NUM_TEL_DESTINATAIRE, maxxx.cd_type_evenement, maxxx.cd_type_notification, maxxx.cd_type_destinataire_source AS CD_TYPE_DEST_SOURCE, case when maxxx.cd_type_destinataire_source = "IDGRC" then maxxx.destinataire_source when maxxx.cd_type_destinataire_source = "IDGRC|IDGRC" then substr(maxxx.destinataire_source, 1, locate("|", maxxx.destinataire_source)-1) else null end AS ID_PERS_DEST_SOURCE, case when maxxx.cd_type_destinataire_source = "SIGMA" or maxxx.cd_type_destinataire_source = "CUBA" then maxxx.destinataire_source else null end AS REF_EXT_DEST_SOURCE, case when maxxx.cd_type_destinataire_source = "MAIL" then maxxx.destinataire_source else null end AS EMAIL_DEST_SOURCE, case when maxxx.cd_type_destinataire_source = "SMS" then maxxx.destinataire_source else null end AS NUM_TEL_DEST_SOURCE, case when maxxx.cd_type_destinataire_source = "IDGRC|IDGRC" then substr(maxxx.destinataire_source, locate("|", maxxx.destinataire_source)+1, length(maxxx.destinataire_source)) end AS ID_PERSONNE_DEST_SOURCE_2 from (select n.id_notification, n.destinataire, n.cd_type_evenement, n.cd_type_notification, n.destinataire_source, n.cd_type_destinataire, n.cd_type_destinataire_source from ${use_database}.pz_send_notification as n join (select id_notification, max(dt_capt) as dtmax from ${use_database}.pz_send_notification group by id_notification) as maxx on n.id_notification=maxx.id_notification and n.dt_capt=maxx.dtmax) as maxxx) AS B
    ON A.ID_NOTIFICATION = B.ID_NOTIFICATION
) AS TMP
LEFT JOIN
-- TABLE C的完整查询，包裹成派生表并指定别名C
(select maxxx.id_communication AS ID_COMMUNICATION, maxxx.cd_sa as CD_SYS_DIFFUSEUR, maxxx.type_conteneur as CD_TYPE_CONTENEUR from (select n.id_communication, n.cd_sa, n.type_conteneur from ${use_database}.pz_send_comm_retour as n join (select id_communication, max(dt_capt) as dtmax from ${use_database}.pz_send_comm_retour group by id_communication) as maxx on n.id_communication=maxx.id_communication and n.dt_capt=maxx.dtmax) as maxxx) AS C
ON TMP.ID_DIFFUSION = C.ID_COMMUNICATION;