如何在SQL中连接3个表而不重复?

Question

我有这个工作:

我们必须获得在职业生涯中至少获得30,000分，并且获得奖品并且仍然活着的玩家。

和我有在我的sql 3不同的表为这些信息。

我可以用这个代码获得超过30K并且还活着的玩家:

SELECT players.playerID, lastName, SUM(points) AS AllPoints, deathDate, 
FROM players
INNER JOIN players_teams  ON players.playerID = players_teams.playerID  
GROUP BY players.playerID, lastName, deathDate  
HAVING SUM(points) > 30000 AND deathDate = "0000-00-00"

和我有:

playerID    lastName    AllPoints   deathDate
0   abdulka01   Abdul-Jabbar    38387.0 0000-00-00
1   ervinju01   Erving  30026.0 0000-00-00
2   jordami01   Jordan  32292.0 0000-00-00
3   malonka01   Malone  36928.0 0000-00-00

所以只有4个球员有这两个特征。

但是如果我想要3个表格告诉我哪个玩家有奖励，它会给我错误的结果。

这是我的尝试:

SELECT players.playerID, lastName, sum(points), award, deathDate
FROM players
INNER JOIN players_teams ON players.playerID = players_teams.playerID  
INNER JOIN awards_players ON players.playerID = awards_players.playerID  
GROUP BY players.playerID, lastName, award, deathDate
HAVING award IS NOT NULL and deathDate = "0000-00-00"

但是查询给了我:

playerID    lastName    sum(points) award   deathDate
0   abdulka01   Abdul-Jabbar    191935.0    All-Defensive First Team    0000-00-00
1   abdulka01   Abdul-Jabbar    230322.0    All-Defensive Second Team   0000-00-00
2   abdulka01   Abdul-Jabbar    383870.0    All-NBA First Team  0000-00-00
3   abdulka01   Abdul-Jabbar    191935.0    All-NBA Second Team 0000-00-00
4   abdulka01   Abdul-Jabbar    76774.0 Finals MVP  0000-00-00
... ... ... ... ... ...
786 wisewi01    Wise    19454.0 All-ABA Second Team 0000-00-00
787 wisewi01    Wise    19454.0 All-Defensive Team  0000-00-00
788 worthja01   Worthy  32640.0 All-NBA Third Team  0000-00-00
789 worthja01   Worthy  16320.0 Finals MVP  0000-00-00
790 youngth01   Young   4534.0  All-Rookie Second Team  0000-00-00

正如你所看到的，表格给出了相同的玩家重复，查询为每个奖励添加了每个积分。例如，如果X在1997年获得了3个奖项，查询将其分数乘以3。

Answer 1

问题是一个玩家可能有多个奖项——在这种情况下，第二个加入会"倍增"。

因为你只是想检查玩家是否有任何奖励，我推荐exists:

select p.playerid, p.lastname, sum(pt.points) as allpoints, p.deathdate
from players p
inner join players_teams pt on pt.playerid = p.playerid
where 
    p.deathdate = "0000-00-00"
    and exists (select 1 from awards_players ap where ap.playerID = p.playerID)
group by p.playerid, p.lastname, p.deathdate  
having sum(pt.points) > 30000 

请注意，我在查询中的所有列都加上了它们所属的表的前缀，这使得它更容易标记。还要注意，deathdate上的条件属于where子句而不是having子句，后者对聚合表达式进行操作(尽管MySQL允许)。

我们可以进一步使用子查询，并使用横向连接来检索总点，因此在外部查询中根本没有聚合(这至少需要MySQL 8.0.14):

select p.playerid, p.lastname, pt.allpoints, p.deathdate
from players p
cross join lateral (
    select sum(pt.points) as allpoints 
    from players_teams pt 
    where pt.playerid = p.playerid
) p
where 
    p.deathdate = "0000-00-00"
    and exists (select 1 from awards_players ap where ap.playerID = p.playerID)
    and pt.allpoints > 30000