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

Question

表格:请在此处查看表格。 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

Answer 1

我相信它可以在没有子查询的情况下完成。

我做了如下匹配表

并使用以下查询对结果进行分组，每个系列一行

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

产生以下结果

请注意，在对一个系列的结果进行分组时，没有光明队或可怕队之类的东西。 Radiant 和 Dire 的角色在同一个系列赛中可能会互换几次，所以我只称他们为 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