MySQL:从聚合结果中检索分组行的总和
P粉978742405
P粉978742405 2024-02-26 23:21:28
[MySQL讨论组]

假设我有每个国家用户访问次数的统计:

查询如下:

SELECT 
    countries_users.user_id,
    countries.name,
    count(countries_users.id) as count_visit
FROM countries_users
LEFT JOIN countries on countries.id = countries_users.user_id
WHERE countries_users.user_id IN (111, ...)
GROUP BY countries_user.user_id, countries.id

结果如下:

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

现在,通常情况下,我会在代码层面完成这个操作。然而,由于某种愚蠢的原因,我想在结果集中添加一个额外的列,该列是用户的总访问次数,不考虑国家。

所以,结果将变为:

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

P粉978742405
P粉978742405

全部回复(1)
P粉819937486

may you need a subquery

SELECT 
cu.user_id,
c.name AS countries,
count(cu.id) AS count_visit,
visitall.total_count_visit AS overall_visit
FROM countries_users cu
LEFT JOIN countries c ON c.id = cu.user_id
LEFT JOIN (
    SELECT user_id, SUM(count(id)) AS total_count_visit
    FROM countries_users
    WHERE user_id IN (111, ...) 
    GROUP BY user_id
) AS visitall ON cu.user_id = visitall.user_id
WHERE cu.user_id IN (111, ...)  
GROUP BY cu.user_id, c.id, visitall.total_count_visit
热门教程
更多>
最新下载
更多>
网站特效
网站源码
网站素材
前端模板
关于我们 免责申明 意见反馈 讲师合作 广告合作 最新更新 English
php中文网:公益在线php培训,帮助PHP学习者快速成长!
关注服务号 技术交流群
PHP中文网订阅号
每天精选资源文章推送
PHP中文网APP
随时随地碎片化学习
PHP中文网抖音号
发现有趣的

Copyright 2014-2025 https://www.php.cn/ All Rights Reserved | php.cn | 湘ICP备2023035733号