SQL左加入错误-体育应用程序

Question

我很难加入一个计数到我的查询。我有一张存储目标事件的桌子。每一列‘得分手’'assist1‘和'assist2’都持有玩家ids。我试图找出一个球员出现在射手、assist1或assist2中的次数，但是只有当类型是pp时才会出现。

goalEvents

 gid scorer assist1 assist2 type
  1    1       2       3     eve
  1    1       2      NULL   pp
  1    1       2       3     sh

查询

SELECT 
    p.playerid, p.playernum,
    CONCAT_WS('. ', SUBSTR(p.playerfname, 1, 1), p.playerlname) name,
    COALESCE(pp.powerplay, 0) PPP
FROM players p
LEFT JOIN (
    SELECT COUNT(*) AS powerplay FROM goalEvents WHERE scorer = p.playerid OR assist1 = p.playerid OR assist2 = p.playerid AND type='pp'   
) pp 
WHERE p.playerteam = 1

作为最终结果我想要什么

id   name  PPP
 1 P. Name  1
 2 A. Name  1
 3 S. Name  0

Answer 1

删除左联接子句，并在返回列中添加一个子选择。还要注意括号，以便将OR's与AND分隔开。尝试这样的方法(未经测试)：

SELECT 
    playerid, 
    playernum,
    CONCAT_WS('. ', SUBSTR(playerfname, 1, 1), playerlname) name,
    (SELECT COUNT(*) AS powerplay FROM goalEvents 
     WHERE (scorer = playerid OR assist1 = playerid OR assist2 = playerid) 
     AND type='pp') AS PPP
FROM players
WHERE playerteam = 1