所以我正在學習 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_campaign、JPN、A、B 和 C，我需要 JPN、A、B 和 C 以彼此相鄰的方式顯示為不同的列。這甚至可能嗎？
將非常感謝您的幫助
嘗試了不同的連接陳述句、子查詢但沒有任何效果，還嘗試研究 ofc 但找不到任何相關內容:(
uj5u.com熱心網友回復：
不確定我是否完全理解您的問題，但我認為您想要以下內容：
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
轉載請註明出處，本文鏈接：https://www.uj5u.com/qiye/519896.html
標籤：Google Cloud Collective sql加入谷歌大查询
上一篇：根據值是否存在于相應資料框的范圍內，向資料框添加一列
下一篇：沒有cte的更新很慢