mysql - SQL 左连接错误 - 体育应用

Question

我在将计数加入查询时遇到问题。我有一个存储目标事件的表。 “scorer”、“assist1”和“assist2”每一列都保存玩家 ID。我试图找到玩家 ID 在记分器、助攻 1 或助攻 2 中出现的次数，但仅当类型为 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

删除 LEFT JOIN 子句并在返回列中添加 SUB SELECT。还要注意用括号将 OR 与 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