假设我有每个国家用户访问次数的统计：
查询如下：
1
1130SELECT
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 ... | ... | ... | ...
Copyright 2014-2025 https://www.php.cn/ All Rights Reserved | php.cn | 湘ICP备2023035733号
收藏
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