MySQL JOIN与SUM和3个表 [英] MySQL JOIN with SUM and 3 tables

问题描述

我有以下表格(球员，赛事，比分)

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

我想要一个列表，列出每位球员在2012年获得的积分. 我希望列表中包含所有球员，即使其中一些球员没有在2012年出战.

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

我尝试过:

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.

我们可以使用子查询或括号来强制此特定联接优先"，但是最好只使用SQL文本中的JOIN顺序，然后仔细地将最后一个JOIN定向给玩家(在这种情况下为正确) ).

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).

COALESCE仅用于将NULL转换为0.

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;

这将产生:

P_ID    LASTNAME    FIRSTNAME   TOTALPOINTS
1       Hansen      Ola         6
2       Svendson    Tove        0
3       Pettersen   Kari        0

您可以在此 SQL小提琴中使用它.

这篇关于MySQL JOIN与SUM和3个表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！