SQL如何用GROUP BY统计指定日期范围内两表不同字段总和

AI 小北

2026-9-23

错误原因

你当前SQL的核心问题是直接关联工时明细表和请假明细表，同一个员工的N条有效工时记录和M条有效带薪请假记录会生成N*M条笛卡尔积数据，直接基于关联后的明细求和会导致重复计算，最终数值偏大。

解决方案

不要直接关联两张明细表，先分别单独聚合两张表的统计结果，再按员工ID关联即可，不需要用partition by复杂逻辑，调整后SQL如下：

-- 定义传入参数，可根据实际数据库语法调整
SET @p_start_date = '2020-01-01';
SET @p_end_date = '2020-01-31';

SELECT
  papf.person_number,
  IFNULL(ts.double_total, 0) AS `Double`,
  IFNULL(ts.regular_total, 0) AS `Regular`,
  'Paid Leave' AS Hour_code,
  IFNULL(als.paid_leave_total, 0) AS hour_amount
FROM per_all_people_F papf
-- 关联工时统计结果
LEFT JOIN (
  SELECT
    person_id,
    SUM(CASE WHEN Time_type = 'Double' THEN Measure ELSE 0 END) AS double_total,
    SUM(CASE WHEN Time_type = 'Regular Pay' THEN Measure ELSE 0 END) AS regular_total
  FROM time_track_tab
  -- 过滤参数日期范围内的工时记录
  WHERE start_date <= @p_end_date
    AND end_date >= @p_start_date
  GROUP BY person_id
) ts ON papf.person_id = ts.person_id
-- 关联带薪假统计结果
LEFT JOIN (
  SELECT
    person_id,
    SUM(duration) AS paid_leave_total
  FROM abs_tab
  -- 过滤参数日期范围内的带薪假
  WHERE Absence_type = 'Paid Leave'
    AND start_date <= @p_end_date
    AND end_date >= @p_start_date
  GROUP BY person_id
) als ON papf.person_id = als.person_id
-- 只返回至少有一条有效工时/有效带薪假的员工，可根据需求调整
WHERE ts.person_id IS NOT NULL OR als.person_id IS NOT NULL;