将3个大表与2个联接组合起来

Question

我有三张桌子用复杂的。我把它们分开运行，没有问题。以下是查询：

表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;

表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;

表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;

是否可以将使用的三个join和一个left join结合起来？

我想要一些类似的东西

SELECT * FROM (TABLE A join TABLE B ON A.ID_NOTIFICATION=B.ID_NOTIFICATION) AS TMP LEFT JOIN TABLE C ON TMP.ID_DIFFUSION=C.ID_COMMUNICATION; 

我一直在尝试，但总是因为漏掉或放错括号而失败。

谢谢!

Answer 1

我不知道您的查询是否正确，但公共表表达式(公共表表达式，CTE)可以经常用于保持结果集联接干净。

WITH TABLE_A AS 
  ( SELECT 
      FROM ...
           ...
  ),
  TABLE_B AS 
  ( SELECT 
      FROM ...
           ...
  ),
  TABLE_C AS 
  ( SELECT 
      FROM ...
           ...
  )
  SELECT * 
    FROM TABLE_A TA
    JOIN TABLE_B TB
      ON TA.Key_Field = TB.Key_Field
    JOIN TABLE_C TC
      ON TB.Key_Field = TC.Key_Field