SQL窗口函数实现关联表每人对应两类活动最新捐赠记录查询

基础环境说明 #

当前使用的MariaDB 10.3版本已原生支持窗口函数，完全可以通过窗口函数高效实现需求，不需要写复杂的相关子查询。
现有表结构：

• person：人员表，字段为id（主键）、name（姓名）
• donation：捐赠记录表，字段为id（主键）、person_id（关联person表外键）、amount（捐赠金额）、date_given（捐赠日期/时间）、campaign（捐赠活动标识，仅取值A、B）

两表为一对多关系，需求为每个人员返回单行数据，同时包含该用户给活动A的最新捐赠全字段、给活动B的最新捐赠全字段。

你原有示例代码的两个问题：

1. CTE块中没有实现「筛选同用户对应活动最新捐赠」的逻辑，直接使用GROUP BY person_id在严格SQL模式下会抛出语法错误，非严格模式下会返回同分组下的随机行，结果不可控
2. 多个CTE不需要重复写WITH关键字，第一个CTE声明后，后续CTE用逗号分隔即可

窗口函数正确写法 #

使用ROW_NUMBER()窗口函数，按「用户+活动」分区排序，仅扫描一次donation表即可拿到所有活动的最新记录，性能最优：

WITH ranked_donations AS (
  SELECT
    *,
    ROW_NUMBER() OVER (
      PARTITION BY person_id, campaign
      ORDER BY date_given DESC, id DESC
    ) AS row_rank
  FROM donation
)
SELECT
  p.id AS person_id,
  p.name,
  -- 活动A最新捐赠字段，统一加前缀避免列名冲突
  a.id AS last_a_donation_id,
  a.amount AS last_a_amount,
  a.date_given AS last_a_date_given,
  a.campaign AS last_a_campaign,
  -- 活动B最新捐赠字段
  b.id AS last_b_donation_id,
  b.amount AS last_b_amount,
  b.date_given AS last_b_date_given,
  b.campaign AS last_b_campaign
FROM person p
LEFT JOIN ranked_donations a
  ON a.person_id = p.id
  AND a.campaign = 'A'
  AND a.row_rank = 1
LEFT JOIN ranked_donations b
  ON b.person_id = p.id
  AND b.campaign = 'B'
  AND b.row_rank = 1;

注意事项 #

• 排序条件中增加了id DESC，是为了兼容同一用户同一活动在同一date_given下存在多笔捐赠的边界场景，此时会默认取主键id更大（后插入）的记录作为最新记录；如果你的业务中date_given是精确到毫秒的时间戳、不存在重复值，可以去掉这个排序条件。
• 所有关联都使用LEFT JOIN，保证从未捐赠、仅捐过A、仅捐过B的用户都能出现在结果集中，未参与活动对应的捐赠字段会返回NULL，和你原有代码的预期逻辑一致。
• 不要直接使用a.*、b.*通配符取字段，两次关联的捐赠表字段名完全重复，会导致返回结果列名冲突，显式指定字段并设置别名是生产环境更稳妥的写法。

如果你更习惯沿用拆分两个CTE的代码结构，也可以使用如下等价写法（会扫描两次donation表，性能略低于单CTE写法）：

WITH lastDonationA AS (
  SELECT
    *,
    ROW_NUMBER() OVER (PARTITION BY person_id ORDER BY date_given DESC, id DESC) AS row_rank
  FROM donation
  WHERE campaign = 'A'
),
lastDonationB AS (
  SELECT
    *,
    ROW_NUMBER() OVER (PARTITION BY person_id ORDER BY date_given DESC, id DESC) AS row_rank
  FROM donation
  WHERE campaign = 'B'
)
SELECT
  p.name,
  a.id AS a_donation_id,
  a.amount AS a_amount,
  a.date_given AS a_date_given,
  b.id AS b_donation_id,
  b.amount AS b_amount,
  b.date_given AS b_date_given
FROM person p
LEFT JOIN lastDonationA a
  ON a.person_id = p.id AND a.row_rank = 1
LEFT JOIN lastDonationB b
  ON b.person_id = p.id AND b.row_rank = 1;

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