我对MySQL并不是很了解,但我需要一个声明,我真的很感谢你的帮助。
我有两张桌子:'用户'和'得分'
这是'用户'的结构:
| 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以及与用户相关的所有分数的总和:我应该如下所示:
| user_id | user_name | scores | | 1 | Paul | 0 | | 2 | Peter | 350 |更好的是,如果结果将按照用户在全球排名中的位置排序,如下所示:
| 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。
非常感谢您的帮助
i'm not really much into MySQL, but i need just one statement and I would really appreciate your help on this.
I have two tables: 'user' and 'score'
here's the structure of 'user':
| user_id | user_name | | 1 | Paul | | 2 | Peter |here's the structure of 'score':
| score_id | score_user_id | score_track_id | score_points | | 1 | 2 | 23 | 200 | | 2 | 2 | 25 | 150 |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 |I tried the statement
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 DESCwhich 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.
thank you very much for your help
最满意答案
您只需要按用户对您的分数进行分组:
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上看到它。
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 initialisationSee it on sqlfiddle.
更多推荐
发布评论