MySQL中EXISTS子句无法绕过LIMIT限制的问题排查

问题场景 #

尝试用EXISTS子句绕过MySQL“子查询不能使用LIMIT”的限制，编写查询语句后，结果仅返回MemberId=1193且Div="FSO"的所有记录平均值，EXISTS子句未生效。预期是返回特定会员特定分区下最近5条（或更少）分数的滚动平均值。

初始查询语句：

SELECT MScores.MemberId, MScores.`Div`, AVG(MScores.Final) AS AvgOfFinal
FROM qryMatchScores MScores
WHERE MScores.MemberId=1193 AND MScores.`Div`="FSO" AND 
EXISTS (
    SELECT MatchDate 
    FROM qryMatchScores MDates
    WHERE 
        MDates.MemberId = MScores.MemberId AND 
        MDates.`Div` = MScores.`Div` AND
        MDates.MatchDate = MScores.MatchDate
    ORDER BY MatchDate DESC
    LIMIT 5
);

失效原因 #

• EXISTS子句的核心逻辑是判断子查询是否返回至少一条结果，你的子查询中，只要当前行的MemberId、Div、MatchDate与MDates表中的记录匹配（实际上就是当前行本身），子查询就会返回这条记录，ORDER BY和LIMIT 5在这里完全不起作用。
• 也就是说，这个EXISTS条件等价于“当前记录是否存在于表中”，而所有符合外层WHERE条件的记录必然都存在，因此EXISTS没有起到任何过滤作用，最终计算的是所有符合条件的记录的平均值，而非最近5条。

解决方案 #

使用窗口函数ROW_NUMBER()先对每个会员、分区的记录按MatchDate倒序编号，筛选出前5条后再计算平均值：

SELECT MemberId, `Div`, AVG(Final) AS RollingAVG 
FROM (
    SELECT 
        MemberId, 
        `Div`, 
        Final, 
        MatchDate,
        ROW_NUMBER() OVER (PARTITION BY MemberId, `Div` ORDER BY MatchDate DESC) AS row_num
    FROM qryMatchScores
    ORDER BY MemberId, `Div`
    ) AS MScores
WHERE row_num <= 5
GROUP BY MemberId, `Div`;

方案说明 #

1. 内层子查询通过PARTITION BY MemberId, Div``将数据按会员和分区分组，每组内按MatchDate DESC排序，用ROW_NUMBER()生成序号row_num，最近的记录序号为1。
2. 外层查询筛选出row_num <=5的记录，即每个会员每个分区的最近5条（或更少）记录，最后分组计算平均值。

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