mysql - 在左联接中使用子查询时如何优化查询

桌子：
请看一下这里的桌子。How to query counting specific wins of team and find the winner of the series
问题：
如何使查询更加优化？
如何减少查询冗余？
如何使此查询更快？
摘要
正如您在示例查询中看到的，该部分被多次使用。

WHERE leagueid = 2096
AND start_time >= 1415938900
AND ((matches.radiant_team_id= 1848158 AND matches.dire_team_id= 15)
OR (matches.radiant_team_id= 15 AND matches.dire_team_id= 1848158))

SELECT matches.radiant_team_id,
       matches.dire_team_id,
       matches.radiant_name,
       matches.dire_name,
       TA.Count AS teamA,
       TB.Count AS teamB,
       TA.Count + TB.Count AS total_matches,
       SUM(TA.wins),
       SUM(TB.wins),
       (CASE
            WHEN series_type = 0 THEN 1
            WHEN series_type = 1 THEN 2
            WHEN series_type = 2 THEN 3
        END) AS wins_goal
FROM matches
LEFT JOIN
  (SELECT radiant_team_id,
          COUNT(id) AS COUNT,
          CASE
              WHEN matches.radiant_team_id = radiant_team_id && radiant_win = 1 THEN 1
          END AS wins
   FROM matches
   WHERE leagueid = 2096
     AND start_time >= 1415938900
     AND ((matches.radiant_team_id= 1848158
           AND matches.dire_team_id= 15)
          OR (matches.radiant_team_id= 15
              AND matches.dire_team_id= 1848158))
   GROUP BY radiant_team_id) AS TA ON TA.radiant_team_id = matches.radiant_team_id
LEFT JOIN
  (SELECT dire_team_id,
          COUNT(id) AS COUNT,
          CASE
              WHEN matches.dire_team_id = dire_team_id && radiant_win = 0 THEN 1
          END AS wins
   FROM matches
   WHERE leagueid = 2096
     AND start_time >= 1415938900
     AND ((matches.radiant_team_id= 1848158
           AND matches.dire_team_id= 15)
          OR (matches.radiant_team_id= 15
              AND matches.dire_team_id= 1848158))
   GROUP BY dire_team_id) AS TB ON TB.dire_team_id = matches.dire_team_id
WHERE leagueid = 2096
  AND start_time >= 1415938900
  AND ((matches.radiant_team_id= 1848158
        AND matches.dire_team_id= 15)
       OR (matches.radiant_team_id= 15
           AND matches.dire_team_id= 1848158))
GROUP BY series_id

计划的匹配

ID| leagueid| team_a_id| team_b_id| starttime
 1|     2096|   1848158|        15| 1415938900

最佳答案

我相信不需要子查询就可以完成。
我做了下面的火柴桌
并使用以下查询将结果分组，每个系列一行

SELECT
      matches.leagueid,
      matches.series_id,
      matches.series_type,
      COUNT(id) as matches,
      IF(radiant_team_id=1848158,radiant_name, dire_name) AS teamA,
      IF(radiant_team_id=1848158,dire_name, radiant_name) AS teamB,
      SUM(CASE
           WHEN radiant_team_id=1848158 AND radiant_win=1  THEN 1
           WHEN dire_team_id=1848158 AND radiant_win=0 THEN 1
           ELSE 0 END) AS teamAwin,
      SUM(CASE
           WHEN radiant_team_id=15 AND radiant_win=1  THEN 1
           WHEN dire_team_id=15 AND radiant_win=0 THEN 1
           ELSE 0 END) AS teamBwin

FROM `matches`
WHERE leagueid = 2096
     AND start_time >= 1415938900
AND dire_team_id IN (15, 1848158)
AND radiant_team_id IN  (15, 1848158)
group by leagueid,series_id,series_type,teamA,teamB

结果如下
请注意，当对一个系列的结果进行分组时，不存在辐射团队或可怕团队。在同一个系列中，辐射和可怕的角色可能会被切换好几次，所以我只将团队称为teamA和teamB。
现在，看看你之前的问题，我发现你需要根据系列赛类型和每个队的胜利来确定系列赛的赢家。这需要包装前一个查询并将其用作子查询，如

SELECT matchresults.*,
      CASE series_type
      WHEN 0 then IF(teamAwin>=1, teamA,teamB)
      WHEN 1 then IF(teamAwin>=2, teamA,teamB)
      ELSE IF(teamAwin>=3, teamA,teamB)
      END as winner

from ( THE_MAIN_QUERY) as matchresults

关于mysql - 在左联接中使用子查询时如何优化查询，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/27089832/