MySQL JOIN with SUM and 3 tables

问题:

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.

回答1:

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.

回答2:

Try this:

select firstName, lastName, sum(if(year is null, 0, points)) points from p
left join s on p_id = player
left join e on e_id = event and year = 2012
group by p_id, lastName, firstName
order by points desc

I guess it should perform better than a subquery... but will be less portable. Give it a try!

Here is the fiddle.

回答3:

Since people (myself included) like to put the "most important" or "subject" table first, you can accomplish what you're looking for by making the join between Events and Scores occur first and as an INNER JOIN:

SELECT
   P.P_Id,
   P.LastName,
   P.FirstName,
   IsNull(Sum(P.Points), 0) TotalPoints
FROM
   Players P
   LEFT JOIN (
      Events E
      INNER JOIN Scores S
         ON E.Event = S.E_Id
         AND E.Year = '2012'
   ) ON P.P_Id = S.Player
GROUP BY P.P_Id, P.LastName, P.FirstName -- no silly MYSQL implicit grouping for me!
ORDER BY TotalPoints DESC

The trick about enforcing join order is that really it's the location of the ON clause that does it—you can remove the parentheses and it should still work. But I prefer to use them because I think the extra clarity is a must.

It's also a perfectly valid way to enforce join order by putting your INNER JOINs first and the OUTER last. Only, you want the last table to be the one that dictates membership in the final set. So switching it to RIGHT JOIN will do the trick:

SELECT
   P.P_Id,
   P.LastName,
   P.FirstName,
   IsNull(Sum(P.Points), 0) TotalPoints
FROM
   Events E
   INNER JOIN Scores S
      ON E.Event = S.E_Id
      AND E.Year = '2012'
   RIGHT JOIN Players P
      ON S.Player = P.P_Id
GROUP BY P.P_Id, P.LastName, P.FirstName
ORDER BY TotalPoints DESC

I feel compelled to add that using different names for a column when it is PK vs. FK in different tables is in my mind a really bad violation of best practice. It should be "P_Id" in all tables, or "Player" in all tables, and not be inconsistent.

回答4:

SELECT P.FIRSTNAME, P.LASTNAME,  A.Points as Points 
FROM Players P
LEFT OUTER JOIN 
(
   SELECT S.Player, sum(Points) 
   FROM Scores S, Events E 
   WHERE S.Event=E.E_Id 
   AND E.Year='2012' 
   GROUP BY S.Player
) A 
ON A.PLAYER = P.P_ID
ORDER by Points DESC

basically you need an INNER JOIN between events and Scores to force the sum of the points of the 2012 and a LEFT OUTER JOIN with this subresult, because you want all the players + their points in the 2012.