如何将多个表连接到一个中心表[SQl]

Question

所以我在学习SQL时遇到了一个问题：我想将多个表连接到一个中心表，并从每个拆分为列的连接中获取所有唯一的结果。为了更容易理解，我将对此进行说明！我有1个中央桌子和3个外部桌子，它们彼此没有连接](https://i.stack.imgur.com/dzbjw.jpg)

在应该如下所示的输出表中：！所需格式](https://i.stack.imgur.com/hsdBB.png)

但是，我不能用一个简单的Join语句来实现这一点——因为没有所有表的公共数据，所以返回值是no data。当我只对中心表和一个外部表运行一个联接语句时，它当然可以正常工作。我还将在这里向您展示代码以供参考：

WITH active_campaign AS (SELECT fr.campaign_id, DATE_TRUNC(fr.day, MONTH) AS month, SUM(fr.imps_count) AS sum_of_impressions, advertiser_id 
FROM `some_table` AS fr
WHERE day between (CURRENT_DATE() - 365) AND (CURRENT_DATE() - EXTRACT(DAY FROM CURRENT_DATE()) +1)
GROUP BY fr.campaign_id, month, advertiser_id 
HAVING SUM(fr.imps_count) > 100
ORDER BY month desc),
PC_JPN AS (SELECT id, profitCenter
FROM some_table2 AS camps
WHERE camps.profitCenter IN ('JPN')),
PC_A AS (SELECT id, profitCenter
FROM some_table2 AS camps
WHERE camps.profitCenter IN ('A')),
PC_B AS (SELECT id, profitCenter
FROM some_table2 AS camps
WHERE camps.profitCenter IN ('B')),
PC_C AS (SELECT id, profitCenter
FROM some_table2 AS camps
WHERE camps.profitCenter IN ('C'))

SELECT active.month, COUNT(DISTINCT pc_jpn.id) AS JPN
FROM active_campaign AS active
JOIN PC_JPN AS pc_jpn
ON active.advertiser_id = pc_jpn.id 
GROUP BY active.month
ORDER BY active.month DESC

正如你所看到的，我有这5个表：active_compaign、JPN、A、B和C，我需要JPN、A。这可能吗？

将非常感谢的帮助

尝试了不同的联接语句、子查询，但没有成功，也尝试了研究ofc，但找不到任何相关的内容：(

Answer 1

我不确定我是否完全理解你的问题，但我认为你想要以下内容：

SELECT 
active.month, 
COUNT(DISTINCT pc_jpn.id) AS JPN,
COUNT(DISTINCT pc_a.id) AS A,
COUNT(DISTINCT pc_b.id) AS B,
COUNT(DISTINCT pc_c.id) AS C
FROM active_campaign AS active
LEFT JOIN PC_JPN AS pc_jpn
ON active.advertiser_id = pc_jpn.id 
LEFT JOIN PC_A AS pc_a
ON active.advertiser_id = pc_a.id 
LEFT JOIN PC_B AS pc_b
ON active.advertiser_id = pc_b.id 
LEFT JOIN PC_C AS pc_c
ON active.advertiser_id = pc_c.id 

GROUP BY active.month
ORDER BY active.month DESC