mysql - 使用 group by 仅显示顶部汇总结果

Question

我的表格记录了同一组用户的三个不同分数(地点、公园、分区)。用户的总得分为“所有站点得分之和”+“所有公园得分之和”+“所有分区得分之和”。

代码如下，我使用的是MySQL:

select  sum(points)as points, user_name 
from 
(SELECT sum(site_point) as points, user_name from visits v join users u on 
u.user_id = v.user_id group by user_name
union 
select sum(park_point) as points , user_name  from visits v join users u on                 
u.user_id = v.user_id group by user_name
union
select sum(division_point) as points , user_name  from visits v join users u 
on u.user_id = v.user_id  group by user_name
) V group by user_name  order by sum(points) DESC ;

我只想显示分数排在前 5 名的用户及其分数。十个用户可能具有相同的最高分数。我需要将它们全部显示出来。我感谢任何帮助。

Answer 1

我想这就是你想要的:

select user_name,
       sum(site_point + park_point + division_point) as points
from visits v join
     users u
     on u.user_id = v.user_id
group by user_name
order by points desc
limit 5;

单一聚合简化了计算。

您可能没有意识到，union 在某些情况下会返回不正确的结果。 Union 会删除重复项，因此如果用户的部分分数相同，则两行会变成一行。

编辑:

获得前 5 名分数有点困难，但有可能:

select user_name,
       sum(site_point + park_point + division_point) as points
from visits v join
     users u
     on u.user_id = v.user_id
group by user_name
having points >= (select distinct points
                  from (select sum(site_point + park_point + division_point)
                        from visits v join
                             users u
                             on u.user_id = v.user_id
                        group by user_name
                       ) vu
                  order by points desc
                  limit 1 offset 4
                 )
order by points desc
limit 5;

假设 user_name 对于给定的 id 是唯一的，您可以稍微简化一下:

having points >= (select distinct points
                  from (select sum(site_point + park_point + division_point) as points
                        from visits v 
                        group by user_id
                       ) vu
                  order by points desc
                  limit 1 offset 4
                 )

而且，如果您想要匹配前 5 位用户的任何人(但如果存在平局则返回 5 行以上)，则将 select different 更改为 select 在子查询中。