关联ads_products表后SQL查询结果异常，如何修正匹配原结果？

问题背景 #

我需要实现两个查询结果一致：

• 第一个查询已正确返回所有广告每月的总ad_spend
• 第二个查询本应返回关联product_id为(1,2,3,4,5)的广告每月总ad_spend（理论上这组产品涵盖所有可用产品，结果应和第一个查询一致），但当前结果错误

现有查询 #

正确的第一个查询 #

select min(DATE_FORMAT(ai.date,"%m-%Y")) as date, ROUND(SUM(ai.ad_spend)) as total_ad_spend
from ad_insight ai 
group by YEAR(ai.date), MONTH(ai.date) 
order by ai.date asc;

结果错误的第二个查询 #

select DATE_FORMAT(ai.date, '%m-%Y') as mmyy,
  MIN(ai.date) as min_date,
  ROUND(SUM(ai.ad_spend)) as total_ad_spend
from ad_insight as ai
join ads_products as ap on ai.ad_id = ap.ad_id
where ap.product_id in (1,2,3,4, 5)
group by mmyy
order by min_date

问题原因 #

第二个查询用JOIN关联ads_products时，如果一个广告对应多个符合条件的产品（即同一ad_id在ads_products中有多条匹配记录），会导致ad_insight中的同一行数据被多次统计，最终SUM(ai.ad_spend)被重复计算，结果偏大。

修复方案 #

需要先确保每个ad_id只被匹配一次，避免重复统计，以下是两种可行方案：

方案1：通过子查询去重关联 #

select DATE_FORMAT(ai.date, '%m-%Y') as mmyy,
  MIN(ai.date) as min_date,
  ROUND(SUM(ai.ad_spend)) as total_ad_spend
from ad_insight as ai
join (
    SELECT DISTINCT ad_id 
    FROM ads_products 
    WHERE product_id in (1,2,3,4,5)
) as ap on ai.ad_id = ap.ad_id
group by mmyy
order by min_date;

方案2：使用EXISTS子查询 #

select DATE_FORMAT(ai.date, '%m-%Y') as mmyy,
  MIN(ai.date) as min_date,
  ROUND(SUM(ai.ad_spend)) as total_ad_spend
from ad_insight as ai
where EXISTS (
    SELECT 1 
    FROM ads_products ap 
    WHERE ap.ad_id = ai.ad_id 
      AND ap.product_id in (1,2,3,4,5)
)
group by mmyy
order by min_date;

两种方式都能保证每个广告只被统计一次，避免ad_spend重复计算，最终结果会和第一个查询一致。

内容的提问来源于stack exchange，提问作者proggaf