sql - 加入 3 个查询的结果

Question

我有 3 个有效的查询，但我不知道如何将它们组合在一个查询中。表格：

------------------        --------------------------       -----------------
PIECE                     MCARTEFIDENT                     MCARTEFIDLIG
------------------        --------------------------       -----------------
ET_LIBELLE : Store        MFC_ETABLISSEMENT : STORE        MFL_ETABLISSEMENT
GP_NUMERO : TICKET        MFC_VALDISPOTHEO                 MFL_NBPASSAGEAPRES
-------------------       --------------------------
--QUERY 1
select [et_libelle] AS [STORE NAME],COUNT([GP_NUMERO])
FROM PIECE
LEFT OUTER JOIN ETABLISS ET1 ON gp_etablissement=ET1.ET_ETABLISSEMENT 
GROUP BY et_libelle

--QUERY 2
SELECT MFC_ETABLISSEMENT as [STORE NAME], COUNT (MFC_VALDISPOTHEO)
FROM MCARTEFIDENT 
LEFT OUTER JOIN ETABLISS ET2 ON MFC_ETABLISSEMENT=ET2.ET_ETABLISSEMENT 
GROUP BY MFC_ETABLISSEMENT

--QUERY 3
SELECT MFL_ETABLISSEMENT AS [STORE NAME], MFL_NBPASSAGEAPRES
FROM MCARTEFIDLIG
LEFT OUTER JOIN ETABLISS ET3 ON MFL_ETABLISSEMENT=ET3.ET_ETABLISSEMENT 
GROUP BY MFL_ETABLISSEMENT

columns 应该是 combine 后的结果：

[et_libelle] (Query 1),
COUNT([GP_NUMERO]) (Query 1),
COUNT([GP_NUMERO]) (Query 2),
MFL_NBPASSAGEAPRES (Query 3)

标签： sqljoin

Answer 1

你可以使用你的 3 个查询作为 subqiery 并加入

select a.[STORE NAME], a.count_gp_numero, b.count_mfc_valdispotheo, c.MFL_NBPASSAGEAPRES
from  (
  select [et_libelle] AS [STORE NAME], COUNT([GP_NUMERO]) count_gp_numero
  FROM PIECE
  LEFT OUTER JOIN ETABLISS ET1 ON gp_etablissement=ET1.ET_ETABLISSEMENT 
  GROUP BY et_libelle
 )  a
 left  join  (
    SELECT MFC_ETABLISSEMENT as [STORE NAME], COUNT(MFC_VALDISPOTHEO) b.count_mfc_valdispotheo
    FROM MCARTEFIDENT 
    LEFT OUTER JOIN ETABLISS ET2 ON MFC_ETABLISSEMENT=ET2.ET_ETABLISSEMENT 
    GROUP BY MFC_ETABLISSEMENT
 ) b on a.[STORE NAME] = b.[STORE NAME]
 left join  (
    SELECT MFL_ETABLISSEMENT AS [STORE NAME], MFL_NBPASSAGEAPRES
    FROM MCARTEFIDLIG
    LEFT OUTER JOIN ETABLISS ET3 ON MFL_ETABLISSEMENT=ET3.ET_ETABLISSEMENT 
    GROUP BY MFL_ETABLISSEMENT
 ) c on a.[STORE NAME] = c.[STORE NAME]