子查询聚合前选取其他列方法 SQL查询最大交易次数及对应姓名

原SQL问题分析 #

你编写的SQL存在逻辑错误：外层查询按Name、TotalTrans分组后，每个分组的MAX(TotalTrans)等于当前分组自身的TotalTrans值，HAVING条件会命中全部分组，最终返回所有用户的交易次数统计，无法筛选出最大值对应的记录。

问题1：子查询执行聚合操作前选取其他列的实现方法 #

• 如果待选取的列和分组键是一一对应关系（比如分组键是用户ID，待取列是对应用户名），直接将该列加入GROUP BY分组键列表即可，符合SQL语法规范。
• 如果需要取分组内某一行的非分组列（比如按用户分组，取该用户最新一笔交易的交易金额），可以使用窗口函数先行标记目标行：先通过ROW_NUMBER()/RANK()对分组内的行排序，过滤出目标行后再执行聚合操作即可。
• 若使用不支持窗口函数的低版本数据库，可以先通过子查询拿到分组聚合的过滤条件，再关联回原表取出需要的其他列。

问题2：查询交易次数最大值及对应姓名的正确实现 #

写法1：窗口函数写法（兼容MySQL8.0+、PostgreSQL、SQL Server等主流新版本数据库） #

使用RANK()可以同时返回所有并列最高交易次数的用户：

SELECT Name, TotalTrans
FROM (
    SELECT 
        Name,
        COUNT(*) AS TotalTrans,
        RANK() OVER(ORDER BY COUNT(*) DESC) AS trans_rank
    FROM trans
    GROUP BY Name
) AS sub_query
WHERE trans_rank = 1;

如果仅需要返回任意一个最高交易次数的用户，将RANK()替换为ROW_NUMBER()即可。

写法2：低版本数据库兼容写法（适配MySQL5.x等不支持窗口函数的场景） #

SELECT Name, COUNT(*) AS TotalTrans
FROM trans
GROUP BY Name
HAVING TotalTrans = (
    SELECT MAX(trans_cnt)
    FROM (
        SELECT COUNT(*) AS trans_cnt
        FROM trans
        GROUP BY Name
    ) AS cnt_sub
);

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