如何在SQL中为每个ID筛选对应最大Count值的URL？

下面提供几种实用的SQL方案，适配不同数据库版本和需求：

方案一：窗口函数（推荐，支持现代数据库） #

利用窗口函数为每个ID下的记录按Count降序排名，直接取排名第一的行。适合MySQL 8+、PostgreSQL、SQL Server、Oracle等支持窗口函数的数据库。

WITH ranked_hits AS (
    SELECT
        id,
        url,
        count(*) AS count,
        -- 若允许多个URL有相同最大Count，替换为RANK()
        ROW_NUMBER() OVER (PARTITION BY id ORDER BY count(*) DESC) AS rn
    FROM hit
    WHERE ...
    GROUP BY id, url
)
SELECT id, url, count
FROM ranked_hits
WHERE rn = 1;

• 用ROW_NUMBER()时，若同一ID下有多个URL的Count相同且为最大值，会随机保留一条；
• 用RANK()时，会返回所有Count等于最大值的URL记录。

方案二：子查询关联（兼容老版本数据库） #

如果你的数据库不支持窗口函数（比如MySQL 5.x），可以通过两次子查询关联实现：先算出每个ID的最大Count，再匹配对应的URL。

SELECT t1.id, t1.url, t1.count
FROM (
    -- 先得到每个ID+URL的Count统计
    SELECT
        id,
        url,
        count(*) AS count
    FROM hit
    WHERE ...
    GROUP BY id, url
) t1
JOIN (
    -- 再算出每个ID的最大Count
    SELECT
        id,
        MAX(count) AS max_count
    FROM (
        SELECT
            id,
            count(*) AS count
        FROM hit
        WHERE ...
        GROUP BY id, url
    ) t2
    GROUP BY id
) t3 ON t1.id = t3.id AND t1.count = t3.max_count;

此方案会返回所有Count等于最大值的URL记录，若需仅保留一条，可结合LIMIT或其他去重逻辑（依数据库特性调整）。

方案三：MySQL专属聚合拼接法（快速取单条URL） #

如果使用MySQL，且只需要每个ID对应一个最大Count的URL（不关心同最大值的多条记录），可以用GROUP_CONCAT配合SUBSTRING_INDEX实现：

SELECT
    id,
    -- 按Count降序拼接URL，取第一个；若URL含逗号，可替换SEPARATOR为其他字符（如'|'）
    SUBSTRING_INDEX(GROUP_CONCAT(url ORDER BY count(*) DESC SEPARATOR '|'), '|', 1) AS url,
    MAX(count(*)) AS count
FROM hit
WHERE ...
GROUP BY id;

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