连接五个表的 mySQL 查询

Question

我有以下五个表(为了简洁省略了一些字段)

(campaign)
campaign_id
===========
campaign_name
campaign_description


(ad_group)
campaign_id
===========
group_id
===========
group_name


(used_ad)
campaign_id
===========
group_id
===========
ad_id
===========


(billboard_group)
campaign_id
===========
group_id
===========
group_name


(used_billboard)
campaign_id
==============
group_id
==============
billboard_code
==============

因此，“广告系列”由 1) 一个“ad_group”和 2) 一个“billboard_group”组成。 ad_group 包含许多广告(存储在“used_ad”组中)，billboard_group 包含许多广告牌(存储在“used_billboard”组中)。

我想运行一个将返回所有事件的查询，并列出与该事件相关的广告和广告牌的总数。我只能对 ad_group 执行此操作，但不能对 ad_group 和 billboard_group 执行此操作。

例如，这个查询:

SELECT campaign.*, COUNT(used_ad.ad_id) AS used_ads
FROM campaign
LEFT JOIN ad_group 
  ON campaign.campaign_id = ad_group.campaign_id
LEFT JOIN used_ad
  ON ad_group.group_id = used_ad.group_id
  AND used_ad.campaign_id = campaign.campaign_id
GROUP BY campaign.campaign_id

给出如下列表:

campaign_id     campaign_name     campaign_description    used_ads
1               Test campaign     Testing                 4
2               Second campaign   Second                  0

但我追求的是:

campaign_id     campaign_name     campaign_description    used_ads    used_billboards
1               Test campaign     Testing                 4           5
2               Second campaign   Second                  0           3

只是我似乎无法弄清楚如何包括来自 used_ad 和 used_billboard 的计数。如果有任何帮助，我将不胜感激。

Answer 1

为了避免笛卡尔乘法，我将 used_ad 和 used_billboard 分别分组和计数，然后才加入它们:

SELECT campaign.*, ag.used_ads_count, bg.used_billboards_count
FROM campaign c
LEFT JOIN  (SELECT    campaign_id, COUNT(*) AS used_ads_count
            FROM      ad_group ag
            LEFT JOIN used_ad ua ON ag.group_id = ua.group_id
            GROUP BY  campaign_id) ac ON c.campaign_id = ac.campaign_id
LEFT JOIN  (SELECT    campaign_id, COUNT(*) AS used_billboards_count
            FROM      billboard_group bg
            LEFT JOIN used_billboard ub ON bg.group_id = ub.group_id
            GROUP BY  campaign_id) bc ON c.campaign_id = bc.campaign_id