MySQL 计算行重复的最大计数

发布于03月15日

我的数据库里有这些数据.

id  | company_id |     referrer
001 | 001        | https://google.com
002 | 001        | https://riot.com
003 | 001        | https://google.com
004 | 002        | https://vimeo.com
005 | 002        | https://yahoo.com
006 | 003        | https://yahoo.com

我想得到每个公司id的推荐人重复次数.一个公司有很多推荐人，但我只想得到数据中重复次数最高的推荐人.

结果是这样的:

company_id | referrer           | nRepeat
001        | https://google.com |  2
002        | https://yahoo.com  |  2

我试过这个密码:

SELECT company_id,
       referrer,
       Count(referrer) AS viewCount
FROM   `my_table`
GROUP  BY company_id
ORDER  BY viewcount DESC

这个问题似乎是错误的.

抱歉，如果我解释得不好，如果你有问题，请告诉我.

有什么建议或帮助吗？非常感谢你的帮助！谢谢

推荐答案

我们可以在这里使用RANK:

WITH cte AS (
    SELECT company_id, referrer, COUNT(*) viewCount,
           RANK() OVER (PARTITION BY company_id ORDER BY COUNT(*) DESC) rnk
    FROM my_table
    GROUP BY company_id, referrer
)

SELECT company_id, referrer, viewCount
FROM cte
WHERE rnk = 1;

以下是使用CTE编写的单选文本:

SELECT company_id, referrer, viewCount
FROM
(
    SELECT company_id, referrer, COUNT(*) viewCount,
           RANK() OVER (PARTITION BY company_id ORDER BY COUNT(*) DESC) rnk
    FROM my_table
    GROUP BY company_id, referrer
) t
WHERE rnk = 1;

这里有一个版本应该可以在8+之前的MySQL版本上使用:

SELECT company_id, referrer, COUNT(*) viewCount
FROM my_table t1
GROUP BY company_id, referrer
HAVING COUNT(*) = (SELECT COUNT(*)
                   FROM my_table t2
                   WHERE t2.company_id = t1.company_id
                   GROUP BY referrer
                   ORDER BY COUNT(*) DESC
                   LIMIT 1);