SQL多表关联：按复合键取最大u_no、u_date并求和的查询问题

你的SQL语句存在一个关键问题：子查询中同时取max(u_no)和max(u_date)，这两个最大值不一定来自同一条记录。比如某个ref1,ref2组里，u_no最大的记录u_date可能不是最大的，反过来也是，这样组合出来的(ref1,ref2,u_no,u_date)在details表中可能根本不存在，导致IN条件匹配不到正确的数据，最终结果自然不对。

我们可以用窗口函数ROW_NUMBER()（或RANK()，如果允许多条记录同时满足最高u_date和u_no的情况）来精准定位每个ref1,ref2组中符合条件的记录，再进行求和关联。

方法一：使用ROW_NUMBER()窗口函数 #

WITH ranked_details AS (
    SELECT 
        ref1,
        ref2,
        amount,
        -- 按ref1+ref2分组，先按u_date降序，再按u_no降序排序，取第一条
        ROW_NUMBER() OVER (PARTITION BY ref1, ref2 ORDER BY u_date DESC, u_no DESC) AS rn
    FROM details
)
SELECT 
    i.id,
    SUM(rd.amount) AS total_amount
FROM identity i
JOIN ranked_details rd 
    ON i.ref1 = rd.ref1 AND i.ref2 = rd.ref2
WHERE rd.rn = 1 -- 只保留每组中排序第一的记录（最高u_date+最高u_no）
GROUP BY i.id;

说明： #

• ROW_NUMBER()会给每个ref1,ref2组内的记录按u_date降序、u_no降序分配序号，序号为1的就是该组中满足最高u_date且在此基础上最高u_no的记录。
• 如果你的业务中允许多条记录同时拥有相同的最高u_date和最高u_no（比如同一组里两条记录u_date和u_no都一样且都是最大值），可以把ROW_NUMBER()换成RANK()，这样这些记录都会被保留，求和时会把它们的amount都加进去。

方法二：关联子查询定位目标记录 #

如果你的数据库不支持窗口函数（比如一些老版本的MySQL），可以用关联子查询来实现：

SELECT 
    i.id,
    SUM(d.amount) AS total_amount
FROM identity i
JOIN details d 
    ON i.ref1 = d.ref1 AND i.ref2 = d.ref2
WHERE NOT EXISTS (
    SELECT 1
    FROM details d2
    WHERE d2.ref1 = d.ref1 AND d2.ref2 = d.ref2
    -- 存在其他记录u_date更大，或者u_date相同但u_no更大
    AND (d2.u_date > d.u_date OR (d2.u_date = d.u_date AND d2.u_no > d.u_no))
)
GROUP BY i.id;

说明： #

• 子查询的作用是检查当前记录是否是ref1,ref2组中u_date最大且u_no最大的：如果不存在其他记录比当前记录的u_date大，或者u_date相同但u_no更大，那么这条记录就是我们要找的目标记录。

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