MySQL结合select和sum从其他表

i'm not really much into MySQL, but i need just one statement and I would really appreciate your help on this.

我不是很喜欢MySQL，但是我只需要一个声明，非常感谢您在这方面的帮助。

I have two tables: 'user' and 'score'

我有两个表格，'user'和'score'

here's the structure of 'user':

这是“用户”的结构:

| user_id | user_name |
| 1       | Paul      |
| 2       | Peter     |

here's the structure of 'score':

这里是“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以及与用户相关的所有分数之和:我应该如下所示:

| 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 DESC

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.

这会导致语法错误。我的主要问题是将user_id从'user'连接到score_user_id(每个行的'score')。

thank you very much for your help

非常感谢你的帮助

1 个解决方案

#1

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

See it on sqlfiddle.

sqlfiddle上看到它。

#1