如何编写SQL查询收入支出表计算各日期的盈利/亏损值

• 分组维度错误：仅按IncomeDate分组，若某日期仅存在支出记录无收入记录，IncomeDate为NULL，这类日期会被遗漏，无法覆盖两张表的所有去重日期
• 聚合逻辑错误：未先分别按日期聚合收入、支出数据就直接关联两张表，同一天有多条收入、支出记录时会产生笛卡尔积，导致求和结果被错误放大
• 空值未处理：当一侧表无对应日期数据时，求和结果为NULL，后续计算会出错
• 缺少目标字段：未实现当日盈利/亏损的计算逻辑，排序逻辑也无法覆盖支出独有的日期

SELECT 
  -- 合并两张表的日期作为统一统计日期
  COALESCE(i.StatDate, e.StatDate) AS StatisticsDate,
  -- 空值转0，避免无收入/支出时计算结果为NULL
  COALESCE(i.DailyIncome, 0) AS DailyIncome,
  COALESCE(e.DailyExpenditure, 0) AS DailyExpenditure,
  -- 计算当日盈利亏损，正数为盈利，负数为亏损
  COALESCE(i.DailyIncome, 0) - COALESCE(e.DailyExpenditure, 0) AS DailyProfit
FROM
  -- 先单独聚合收入表的每日总收入
  (SELECT IncomeDate AS StatDate, SUM(AmountPaid) AS DailyIncome 
   FROM Income 
   GROUP BY IncomeDate) i
FULL OUTER JOIN
  -- 再单独聚合支出表的每日总支出
  (SELECT ExpenseDate AS StatDate, SUM(TotalAmount) AS DailyExpenditure 
   FROM Expenditure 
   GROUP BY ExpenseDate) e
ON i.StatDate = e.StatDate
-- 按合并后的统一日期排序
ORDER BY StatisticsDate

• 先分别对收入、支出表按日期做预聚合，避免直接关联产生笛卡尔积，保证求和结果准确
• 用FULL OUTER JOIN关联预聚合后的两张子表，保证两张表的所有日期都能被覆盖
• 用COALESCE函数合并两侧日期作为统一统计维度，同时将无收入/支出的场景对应值转为0，避免运算结果出现NULL
• 新增DailyProfit字段直接得到每日盈利亏损结果，按统一统计日期排序符合业务查看习惯

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