MySQL分组查询结果中如何添加用户总访问量列

问题背景 #

现有需求为统计指定用户在各国家的访问次数，原查询语句存在关联条件错误（countries.id = countries_users.user_id应为关联用户访问的国家ID字段，否则无法正确匹配用户与对应访问国家），且需新增overall_visit列展示该用户所有国家的总访问量。

修改后的查询语句 #

SELECT 
    cu.user_id,
    c.name AS countries,
    COUNT(cu.id) AS count_visit,
    SUM(COUNT(cu.id)) OVER (PARTITION BY cu.user_id) AS overall_visit
FROM countries_users cu
LEFT JOIN countries c ON c.id = cu.country_id  -- 修正关联条件，需与实际表结构字段匹配
WHERE cu.user_id IN (111, ...)
GROUP BY cu.user_id, c.id, c.name
ORDER BY cu.user_id, count_visit DESC;

关键说明 #

• 修正关联逻辑：原语句中把国家ID和用户ID直接关联属于逻辑错误，需改为countries.id关联countries_users表中存储国家ID的字段（示例中假设为country_id，请根据实际表结构调整）。
• 窗口函数计算总访问量：通过SUM(COUNT(cu.id)) OVER (PARTITION BY cu.user_id)，在按「用户+国家」分组统计单国家访问量的同时，以user_id为分区维度计算每个用户的总访问次数，实现一行数据同时展示两类统计值。
• GROUP BY 字段补充：MySQL 5.7及以上版本要求GROUP BY需包含所有非聚合字段，因此需加入c.name（若countries表中id与name一一对应，也可仅保留c.id）。

预期查询结果 #

user_id | countries | count_visit | overall_visit
---------------------------------------------------
111     | Norway    | 5           | 7
111     | Japan     | 2           | 7
...     | ...       | ...         | ...

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