PostgreSQL查询各卡种最高交易时出现transaction_id主键重复问题

问题根因 #

• 原有SQL的核心错误是主查询未建立credit_cards信用卡表和transactions交易表的关联关系，直接对两张表做笛卡尔积运算，只要交易金额和某卡种最高金额相等，就会和该卡种强行匹配，才会出现单交易对应多个不属于自身卡种的错误结果。你猜测的IN子句跨卡种匹配只是表象，本质是缺少表关联条件。

修复方案 #

方案1：直接修改原有SQL #

只需在主查询WHERE条件中补充两张表的关联逻辑即可，修正后代码如下：

SELECT tr.identifier, cc.type, tr.amount as max_amount
FROM credit_cards cc, transactions tr 
WHERE cc.number = tr.number -- 新增信用卡号与交易卡号的关联条件
  AND (tr.amount, cc.type) IN (
    SELECT MAX(tr.amount), cc.type   
    FROM credit_cards cc, transactions tr 
    WHERE cc.number = tr.number
    GROUP BY cc.type
  )
GROUP BY tr.identifier, cc.type;

方案2：用窗口函数优化写法（逻辑更清晰，性能更优） #

用RANK()窗口函数按卡种分组对交易金额倒序排序，直接取排名第一的交易即可，天然兼容同卡种多交易并列最高的场景：

WITH card_trade_rk AS (
  SELECT 
    tr.identifier, 
    cc.type, 
    tr.amount AS max_amount,
    RANK() OVER(PARTITION BY cc.type ORDER BY tr.amount DESC) AS amount_rank
  FROM credit_cards cc
  INNER JOIN transactions tr ON cc.number = tr.number
)
SELECT identifier, type, max_amount
FROM card_trade_rk
WHERE amount_rank = 1;

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