mysql order by sum,MySQL JOIN SUM和3个表

I have the following tables (Players, Events, Scores)

I would like a list of how many points each player has got in 2012.

I want the list to feature all of the players, even though some haven't played in 2012.

So in theory it should print:

Ola Hansen 6

Tove Svendson 0

Kari Pettersen 0

I tried:

SELECT *, sum(Points) as Points FROM Players

LEFT OUTER JOIN Scores ON P_Id=Player

LEFT OUTER JOIN Events ON Event=E_Id AND Year='2012'

GROUP by P_Id ORDER by Points DESC

But that counts all the points from all the years.

解决方案

Scores and events need to be inner-joined before outer-joining them to players.

We could use a subquery or parentheses to force this particular join "precedence", but it's nicer to just use the order of JOINs in the SQL text, and then carefully "orient" the last JOIN to players (RIGHT in this case).

The COALESCE is just for converting NULLs to 0s.

SELECT

P_Id, LastName, FirstName, COALESCE(SUM(Points), 0) TotalPoints

FROM

Scores

JOIN Events

ON Event = E_Id AND Year = 2012

RIGHT JOIN Players

ON P_Id = Player

GROUP BY

P_Id, LastName, FirstName

ORDER BY

TotalPoints DESC;

This produces:

P_ID LASTNAME FIRSTNAME TOTALPOINTS

1 Hansen Ola 6

2 Svendson Tove 0

3 Pettersen Kari 0

You can play with it in this SQL Fiddle.