mysql - SUM信息的子查询未包含在JOIN MySQL的表结果中

因此，我觉得这可能已经在某个地方得到了解答，但是我整夜都在搜寻互联网，并且继续空手而归。我在MySQL数据库中有3个表，并且在它们上使用JOIN语句。我按运动过滤结果（即，仅那些运动等于NFL的结果）。

问题是我想在过滤结果上创建一列，以汇总其他体育项目中统计信息显示的时间。但是跟随WHERE Sport.type = 'NFL'变得不可能了，因为我已经过滤掉了所有这些实例。我现在想我需要一些子查询，但是我不确定如何组织它，以便可以绘制只列出每个player_id一次的查询结果，然后让我将数据引用的列制成表格整个数据库中与该player_id相关的任何信息，即使已通过JOIN语句进行了过滤。我觉得这应该很容易，而且我只是想念一些东西。

真正简单的代码示例如下。有人可以帮忙吗？先感谢您！

SELECT
  Player.player_id,
  Player.contact,
  SUM(CASE WHEN Sport.type != 'NFL' THEN Info.Minutes ELSE 0 END)
FROM
    Player
JOIN Info ON Player.player_id = Info.player_id
JOIN Sport ON Sport.game_id = Info.game_id
WHERE Sport.type = 'NFL'
GROUP BY Player.player_id);

最佳答案

使用UNION

SELECT
  Player.player_id,
  Player.contact,
  0 AS stats
FROM
    Player
JOIN Info ON Player.player_id = Info.player_id
JOIN Sport ON Sport.game_id = Info.game_id
WHERE Sport.type = 'NFL'
GROUP BY Player.player_id

UNION

SELECT
  Player.player_id,
  Player.contact,
  SUM(Minutes) AS stats
FROM
    Player
JOIN Info ON Player.player_id = Info.player_id
JOIN Sport ON Sport.game_id = Info.game_id
WHERE Sport.type != 'NFL'
GROUP BY Player.player_id;

更新

要获得独特的玩家但统计数据不同，请使用以下查询。


  例如。
  
  
  如果玩家同时属于NFL和非NFL，则将NFL_only返回为1，将All_other_sports返回为Sum of minutes。
  如果玩家仅属于NFL，它将返回NFL_only as 1 and All_other_sports as 0`。
  如果玩家仅属于“其他运动”，则将返回NFL_only as 0 and All_other_sports as分钟总数。
  

SELECT z.player_id, z.contact, SUM(z.NFL_only) AS NFL_only, SUM(z.All_other_sports) AS All_other_sports
FROM (
    SELECT
      Player.player_id,
      Player.contact,
      1 AS NFL_only,
      0 AS All_other_sports
    FROM
    Player
    JOIN Info ON Player.player_id = Info.player_id
    JOIN Sport ON Sport.game_id = Info.game_id
    WHERE Sport.type = 'NFL'
    GROUP BY Player.player_id

    UNION

    SELECT
      Player.player_id,
      Player.contact,
      0 AS NFL_only,
      SUM(Minutes) AS All_other_sports
    FROM
    Player
    JOIN Info ON Player.player_id = Info.player_id
    JOIN Sport ON Sport.game_id = Info.game_id
    WHERE Sport.type != 'NFL'
    GROUP BY Player.player_id;
) z
GROUP BY z.player_id;

更新2

根据您的明确要求，如果我的一张原始桌子上有一栏指示这项运动（即NFL，MLB，NBA），那么我要列出的是至少参加过NFL一次的球员名单。然后，如果他们参加了其他运动，我想知道他们参加了多少次。问题是，如果仅使用NFL过滤那些行，我将无法再计算具有MLB和NBA的行，因为我已使用JOIN删除了这些行。

SELECT
  Player.player_id,
  Player.contact,
  SUM(CASE WHEN Sport.type != 'NFL' THEN Info.Minutes ELSE 0 END)
FROM
  Player
JOIN Info ON Player.player_id = Info.player_id
JOIN Sport ON Sport.game_id = Info.game_id
WHERE Player.player_id IN (
    SELECT
      Player.player_id,
    FROM
    Player
    JOIN Info ON Player.player_id = Info.player_id
    JOIN Sport ON Sport.game_id = Info.game_id
    WHERE Sport.type = 'NFL'
    GROUP BY Player.player_id
)
GROUP BY Player.player_id;

子查询将仅返回NFL球员。主要查询将仅过滤NFL播放器并返回预期输出。