基于关联表记录统计更新EventEntries表Priority字段的SQL实现咨询

核心逻辑说明 #

仅更新未来举办的活动对应的报名记录优先级，优先级根据员工近90天获奖次数计算：

• 0次获奖：Priority=1（中奖概率最高）
• 1次获奖：Priority=2
• 2次及以上获奖：Priority=3

通用SQL示例（MySQL环境） #

UPDATE EventEntries ee
-- 筛选出举办时间晚于今日的活动
INNER JOIN EventDescriptions ed 
  ON ee.EventID = ed.EventID 
  AND ed.StartDateTime > CURDATE()
-- 统计每个员工近90天的获奖次数
LEFT JOIN (
  SELECT ew.EmployeeKey, COUNT(*) AS win_count
  FROM EventWinners ew
  INNER JOIN EventDescriptions ed_win 
    ON ew.EventID = ed_win.EventID
    AND ed_win.StartDateTime >= DATE_SUB(CURDATE(), INTERVAL 90 DAY)
    AND ed_win.StartDateTime <= CURDATE()
  GROUP BY ew.EmployeeKey
) win_stats ON ee.EmployeeKey = win_stats.EmployeeKey
-- 按规则赋值优先级
SET ee.Priority = CASE
  WHEN COALESCE(win_stats.win_count, 0) = 0 THEN 1
  WHEN COALESCE(win_stats.win_count, 0) = 1 THEN 2
  ELSE 3
END;

不同数据库适配调整 #

SQL Server版本 #

UPDATE ee
SET Priority = CASE
  WHEN COALESCE(win_stats.win_count, 0) = 0 THEN 1
  WHEN COALESCE(win_stats.win_count, 0) = 1 THEN 2
  ELSE 3
END
FROM EventEntries ee
INNER JOIN EventDescriptions ed 
  ON ee.EventID = ed.EventID 
  AND ed.StartDateTime > GETDATE()
LEFT JOIN (
  SELECT ew.EmployeeKey, COUNT(*) AS win_count
  FROM EventWinners ew
  INNER JOIN EventDescriptions ed_win 
    ON ew.EventID = ed_win.EventID
    AND ed_win.StartDateTime >= DATEADD(day, -90, GETDATE())
    AND ed_win.StartDateTime <= GETDATE()
  GROUP BY ew.EmployeeKey
) win_stats ON ee.EmployeeKey = win_stats.EmployeeKey;

PostgreSQL版本 #

UPDATE EventEntries ee
SET Priority = CASE
  WHEN COALESCE(win_stats.win_count, 0) = 0 THEN 1
  WHEN COALESCE(win_stats.win_count, 0) = 1 THEN 2
  ELSE 3
END
FROM EventDescriptions ed
LEFT JOIN (
  SELECT ew.EmployeeKey, COUNT(*) AS win_count
  FROM EventWinners ew
  INNER JOIN EventDescriptions ed_win 
    ON ew.EventID = ed_win.EventID
    AND ed_win.StartDateTime >= CURRENT_DATE - INTERVAL '90 days'
    AND ed_win.StartDateTime <= CURRENT_DATE
  GROUP BY ew.EmployeeKey
) win_stats ON ee.EmployeeKey = win_stats.EmployeeKey
WHERE ee.EventID = ed.EventID 
  AND ed.StartDateTime > CURRENT_DATE;

注意事项 #

• 执行更新前建议将UPDATE语句替换为SELECT查询，验证计算出的Priority值是否符合预期，避免误修改数据
• 如果EventWinners表自带获奖时间字段，可以直接用该字段筛选近90天记录，无需关联EventDescriptions表统计获奖次数
• COALESCE函数用于处理无获奖记录的员工，默认获奖次数赋值为0

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