Mysql连接计算来自另一个表的行

Question

在主表中的行的两个不同条件下，我无法从特定表中获取行数:

主要是，我有一个玩家表，其中包含简单的信息，例如玩家名和 ID。我还有一个 Action 表，其中列出了每个球员的 Action 类型(进球或助攻)。我想让所有球员都有一列计算他们的进球数，另一列计算他们的助攻数。当然，我想要那些没有进球，没有助攻的懒惰球员。

现在我提出了这个要求，但它只给我最好的球员，而我想要所有球员。请点燃我的思绪；)

SELECT
  `p`.`playername`, 
  COUNT(`a1`.`actionid`) AS `goals`, 
  COUNT(`a2`.`actionid`) AS `assists`

FROM `Player` AS `p`

LEFT JOIN `Actions` AS `a1` 
ON `a1`.`id` = `p`.`id`

LEFT JOIN `Actions` AS `a2` 
ON `a2`.`id` = `p`.`id`

WHERE `a1`.`type` = 'goal'
  AND `a2`.`type` = 'assist'

GROUP BY  
  `a1`.`userId`,  
  `a2`.`userId` 

LIMIT 0 , 30

Answer 1

我在您的查询中注意到您正在加入您的 actions 表，该条件引用别名为 u 的表，您没有为其分配任何表.所以，我认为您的查询可能会引发 MySQL 错误。此外，在您的连接条件中，您引用了 Actions 表的 id 字段，在正常约定下，我希望它是主要的自动递增键。您可能将其用作玩家 ID，我不确定，但为了更详细，我将其更改为阅读 player_id 这样您和其他人就可以确切地知道下面的查询中发生了什么。假设您在 SO 编辑器中将查询编辑为“更易于阅读”并且没有对其进行测试，我将其调整为我认为更准确的内容。

当连接表并在选择上运行 COUNT 时，您只能计算返回的总行数。您不能根据为 JOIN 中的单个表返回的行数COUNT。如果您绝对必须使用 COUNT，则必须运行 1 个以上的查询(每个 COUNT 需要运行 1 个)或使用运行 的子查询COUNT 您要查找的特定条件。

此查询可以作为单个查询运行，但 COUNT 不是解决方案。您使用 CASE 和 SUM 的组合。我们运行一个 CASE..WHEN 子句来测试每一行以查看已加入的操作是目标还是辅助。如果是进球，我们给 goals 列赋值 1，如果是助攻，我们给 assists 列赋值1. 然后我们运行一个聚合函数 (SUM) 将所有 goals 和 assists 的 1 相加，以及 GROUP BY 玩家 ID。这为每个球员提供了一行，其中包含他们获得的总进球数和助攻数，即使对于获得 0 分的球员也是如此。

SELECT 
    p.payername, 
    SUM(CASE a.type WHEN 'goal' THEN 1 ELSE 0 END) goals, 
    SUM(CASE a.type WHEN 'assist' THEN 1 ELSE 0 END) assists
FROM Player p
LEFT JOIN Actions a ON a.player_id = p.id
GROUP BY p.id
LIMIT 0, 30

我希望这对您有所帮助并且对您有意义。您可以查看 CASE 的文档如果您仍然感到困惑，可以获得更多见解。