MySQL LEFT JOIN丢失行 如何展示无营收/成本但有手续费的日期数据

核心思路是先生成所有需要展示的日期的完整集合，再以这个日期集合为左表，分别左关联4张业务表，就能覆盖所有存在任意业务数据的日期，不会漏行。

方案1：有预构建的日历维度表（推荐） #

如果你库里有现成的全量日期维度表dim_date，直接用日期字段关联所有业务表即可，SQL示例如下：

SELECT
  d.date,
  COALESCE(r.revenue, 0) AS revenue,
  COALESCE(c.cost, 0) AS cost,
  COALESCE(f.fees, 0) AS fees,
  COALESCE(o.others, 0) AS others,
  -- 计算最终营收
  COALESCE(r.revenue, 0) - (COALESCE(c.cost, 0) + COALESCE(f.fees, 0) + COALESCE(o.others, 0)) AS final
FROM dim_date d
-- 关联所有业务表，日期范围可以按需加WHERE过滤
LEFT JOIN revenue r ON d.date = r.date
LEFT JOIN cost c ON d.date = c.date
LEFT JOIN fees f ON d.date = f.date
LEFT JOIN others o ON d.date = o.date
-- 可以加过滤条件，只保留有至少一项业务数据的日期，避免全量日历的空行
WHERE COALESCE(r.revenue, c.cost, f.fees, o.others) IS NOT NULL

方案2：无日历表，用UNION生成日期全集 #

如果没有预建的日历表，可以先把4张表的所有日期去重合并，得到日期全集后再做左关联：

WITH all_dates AS (
  SELECT date FROM revenue
  UNION
  SELECT date FROM cost
  UNION
  SELECT date FROM fees
  UNION
  SELECT date FROM others
)
SELECT
  ad.date,
  COALESCE(r.revenue, 0) AS revenue,
  COALESCE(c.cost, 0) AS cost,
  COALESCE(f.fees, 0) AS fees,
  COALESCE(o.others, 0) AS others,
  COALESCE(r.revenue, 0) - (COALESCE(c.cost, 0) + COALESCE(f.fees, 0) + COALESCE(o.others, 0)) AS final
FROM all_dates ad
LEFT JOIN revenue r ON ad.date = r.date
LEFT JOIN cost c ON ad.date = c.date
LEFT JOIN fees f ON ad.date = f.date
LEFT JOIN others o ON ad.date = o.date

注意：所有业务数值字段都用COALESCE把空值转为0，避免加减运算时出现NULL结果。

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