mysql - 按每个 user_id 的总积分降序排列

Question

select 
((select 
        COALESCE(sum(b.points_received), 0) as badge_total_points
    from
        user_badges ub
            join
        badges b ON ub.badge_id = b.badge_id
    where
        ub.user_id = '$user_id') + (select 
        COALESCE(sum(aps.given_points), 0) as total_action_points
    from
        user_action_points uap
            join
        action_point_system aps ON uap.point_id = aps.point_id
    where
        uap.user_id = '$user_id')) as total_contribution_points

如何使用此 SQL 语句检索按 user_id 分组的总分列表？有人有建议吗？

编辑:

select 
ub.user_id,
COALESCE(sum(b.isGold), 0) as gold_count,
COALESCE(sum(b.isSilver), 0) as silver_count,
COALESCE(sum(b.isBronze), 0) as bronze_count
from
user_badges ub
    join
badges b ON ub.badge_id = b.badge_id
group by ub.user_id

如何将此查询添加到第一个问题的结果中？

Answer 1

尽管有多种方法可以完成您想要的操作，但您可以使用相关子查询在现有查询的基础上进行构建:

select u.*,
       ((select COALESCE(sum(b.points_received), 0) as badge_total_points
         from user_badges ub join
              badges b
              ON ub.badge_id = b.badge_id
         where ub.user_id = u.user_id
        ) +
        (select COALESCE(sum(aps.given_points), 0) as total_action_points
         from user_action_points uap join
              action_point_system aps
              ON uap.point_id = aps.point_id
         where uap.user_id = u.user_id
        )
       ) as total_contribution_points
from users u
order by total_contribution_points desc;