含COUNT(*)与MAX()的MySQL查询如何返回多列关联结果

问题原因 #

直接对Total字段使用MAX()聚合函数时，数据库会将所有查询结果聚合为单条返回，默认不会保留非聚合字段（姓名）的关联信息，因此只能拿到最大出场次数，无法匹配到对应演员。

通用解决方法 #

假设你已有的统计子查询可以输出first_name（名）、last_name（姓）、Total（总出场次数）三个字段，以下是不同场景的实现方案：

1. 全数据库兼容写法 #

无需依赖数据库高阶特性，所有SQL环境都可以运行：

SELECT 
    first_name,
    last_name,
    Total AS `Total Appearances`
FROM 
    -- 替换为你自己的统计子查询
    (SELECT first_name, last_name, COUNT(film_id) AS Total FROM 你的表关联逻辑 GROUP BY actor_id, first_name, last_name) AS actor_counts
WHERE 
    Total = (
        SELECT MAX(Total) 
        FROM -- 这里再复用一次你自己的统计子查询
        (SELECT first_name, last_name, COUNT(film_id) AS Total FROM 你的表关联逻辑 GROUP BY actor_id, first_name, last_name) AS actor_counts
    );

如果有多位演员出场次数同为最大值，该写法会将所有符合条件的演员全部返回。

2. 现代数据库简化写法（支持CTE即可，MySQL8.0+/PostgreSQL/SQL Server等均支持） #

用CTE复用统计逻辑，避免重复写子查询：

WITH actor_counts AS (
    -- 替换为你自己的统计子查询
    SELECT first_name, last_name, COUNT(film_id) AS Total 
    FROM 你的表关联逻辑 
    GROUP BY actor_id, first_name, last_name
)
SELECT first_name, last_name, Total AS `Total Appearances`
FROM actor_counts
WHERE Total = (SELECT MAX(Total) FROM actor_counts);

3. 支持窗口函数的数据库可选写法 #

如果后续需要调整为取出场次数前N名的演员，用RANK()窗口函数更灵活：

WITH actor_counts AS (
    -- 替换为你自己的统计子查询
    SELECT first_name, last_name, COUNT(film_id) AS Total 
    FROM 你的表关联逻辑 
    GROUP BY actor_id, first_name, last_name
),
ranked_actors AS (
    SELECT 
        first_name,
        last_name,
        Total AS `Total Appearances`,
        RANK() OVER(ORDER BY Total DESC) AS rank_num
    FROM actor_counts
)
SELECT first_name, last_name, `Total Appearances`
FROM ranked_actors
WHERE rank_num = 1;

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