MySQL将选择与其他表的总和相结合 [英] MySQL combine select with sum from other table
问题描述
我对MySQL的了解并不多,但是我只需要声明一下,非常感谢您对此的帮助.
i'm not really much into MySQL, but i need just one statement and I would really appreciate your help on this.
我有两个表:'user'和'score'
I have two tables: 'user' and 'score'
这是用户"的结构:
| user_id | user_name |
| 1 | Paul |
| 2 | Peter |
这是得分"的结构:
| score_id | score_user_id | score_track_id | score_points |
| 1 | 2 | 23 | 200 |
| 2 | 2 | 25 | 150 |
现在我需要一个查询,该查询可以为我提供某种高分列表.结果应包含user_id,user_name以及与该用户相关的所有分数的总和:我应该看起来像这样:
now I need a query that provides me some kind of highscore-list. the result should contain user_id, user_name and the sum of all scores that are related to the user: i should look like this:
| user_id | user_name | scores |
| 1 | Paul | 0 |
| 2 | Peter | 350 |
如果结果按照用户在全球排名中的位置排名的顺序进行排序,则效果会更好:
even better would be, if the result would be sorted in order of the users position in the global ranking like this:
| position | user_id | user_name | scores |
| 1 | 2 | Peter | 350 |
| 2 | 1 | Paul | 0 |
我尝试过该声明
SELECT user_id as current_user, user_name, SUM(SELECT score_points FROM score WHERE score_user_id = current_user) as ranking FROM user ORDER BY ranking DESC
这会导致语法错误. 我的主要问题是将每行的user_id从"user"连接到"score"中的score_user_id.
which results in a syntax error. the main problem for me is to connect the user_id from 'user' to the score_user_id in 'score' for each row.
非常感谢您的帮助
推荐答案
您只需要按用户将分数分组:
You just need to group your scores by user:
SELECT @p:=@p+1 AS position, t.*
FROM (
SELECT user.user_id,
user.user_name,
IFNULL(SUM(score.score_points),0) AS total_points
FROM user LEFT JOIN score ON user.user_id = score.score_user_id
GROUP BY user.user_id
ORDER BY total_points DESC
) AS t JOIN (SELECT @p:=0) AS initialisation
在 sqlfiddle 上查看.
这篇关于MySQL将选择与其他表的总和相结合的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!