存在共同值但SQL多表关联无结果，求排查解决方法

我需要按月汇总每个产品的广告支出与收入：

• 第一个查询按月汇总各产品的广告成本
• 第二个查询按月汇总各产品的收入
• 第三个查询需合并这两个结果生成目标表格

原成本查询 #

SELECT ap.product, DATE_FORMAT(min(ai.date), '%m-%Y') as date, ROUND(SUM(ai.ad_spend)) as monthly_cost
FROM ad_insight ai
JOIN (SELECT ad_id, product FROM ads_products anp) ap ON ai.ad_id = ap.ad_id
GROUP BY ap.product, YEAR(ai.date), MONTH(ai.date)
ORDER BY min(ai.date) asc;

原收入查询 #

select pi.name, round(sum(pi.value)) as monthly_income, DATE_FORMAT(min(pi.date), "%m-%Y") as date
from product_insight pi
where pi.name in (select distinct anp.product from ads_products anp)
group by pi.name, YEAR(pi.date), MONTH(pi.date)
order by min(pi.date) asc;

尝试的关联查询（无结果） #

SELECT ap.product, DATE_FORMAT(min(ai.date), '%m-%Y') as date, ROUND(SUM(ai.ad_spend)) as monthly_cost
FROM ad_insight ai
JOIN (SELECT ad_id, product FROM ads_products anp) ap ON ai.ad_id = ap.ad_id
join (
  select pi.name, round(sum(pi.value)) as monthly_income, DATE_FORMAT(min(pi.date), "%m-%Y") as date
  from product_insight pi
  where pi.name in (select distinct anp.product from ads_products anp)
  group by pi.name, YEAR(pi.date), MONTH(pi.date)
) jpi on jpi.name = ap.product and jpi.date = ai.date
GROUP BY ap.product, YEAR(ai.date), MONTH(ai.date)
ORDER BY min(ai.date) asc;

问题原因 #

核心问题在于关联条件jpi.date = ai.date：

• jpi.date是格式化后的字符串类型（例如04-2021）
• ai.date是原始日期类型（例如2021-04-01）
  两者类型不匹配，无法匹配到任何数据。此外，你在原始表关联汇总子查询后再分组，逻辑顺序错误——应该先分别完成两组数据的汇总，再用相同维度（产品、年月）关联。

正确的查询语句 #

将两个汇总后的子查询通过product（或name）和格式化后的date字段关联，确保关联维度一致：

SELECT 
  cost.product,
  cost.date,
  cost.monthly_cost,
  income.monthly_income
FROM (
  SELECT 
    ap.product, 
    DATE_FORMAT(min(ai.date), '%m-%Y') as date, 
    ROUND(SUM(ai.ad_spend)) as monthly_cost
  FROM ad_insight ai
  JOIN (SELECT ad_id, product FROM ads_products anp) ap ON ai.ad_id = ap.ad_id
  GROUP BY ap.product, YEAR(ai.date), MONTH(ai.date)
) cost
JOIN (
  SELECT 
    pi.name as product, 
    round(sum(pi.value)) as monthly_income, 
    DATE_FORMAT(min(pi.date), "%m-%Y") as date
  from product_insight pi
  where pi.name in (select distinct anp.product from ads_products anp)
  group by pi.name, YEAR(pi.date), MONTH(pi.date)
) income ON cost.product = income.product AND cost.date = income.date
ORDER BY cost.date asc;

期望输出 #

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