mysql - 无法返回具有特定条件的 MAX 值

Question

我正在尝试获取下一个比赛周比赛的比赛。比赛周是在比赛进行时，我们可以有这些类型的比赛周:20、21、22、23。每个比赛周都在特定日期进行。现在所有的比赛都有一个特定的状态，如果状态值为 5，我可以查看特定比赛周的比赛是否进行了，如果没有进行，状态可以是:1、2、3、4。

我的查询是这样设计的:

 SELECT m.id, m.round_id, m.datetime,
  CASE m.status
    WHEN 1 THEN 'scheduled'
    WHEN 2 THEN 'postponed'
    WHEN 3 THEN 'canceled'
    WHEN 4 THEN 'playing'
    WHEN 5 THEN 'finished'
  END AS match_status,
  m.gameweek, m.home_team_id, m.home_team_half_time_score, m.home_team_score,
  m.home_extra_time, m.home_penalties, m.away_team_id, m.away_team_half_time_score,
  m.away_team_score, m.away_extra_time, m.away_penalties, m.venue_id,
  m.venue_attendance, m.aggregate_match_id,
  t.name AS home_team_name,
  t_info.shield_link AS home_team_shield,
  t2.name AS away_team_name,
  t2_info.shield_link AS away_team_shield,
  c.name AS competition_name,
  c.id AS competition_id,
  r.name AS round_name
  FROM `match` m
  LEFT JOIN team t ON m.home_team_id = t.id
  LEFT JOIN team_info t_info ON t.id = t_info.team_id
  LEFT JOIN team t2 ON m.away_team_id = t2.id
  LEFT JOIN team_info t2_info ON t2.id = t2_info.team_id
  LEFT JOIN competition_rounds r ON m.round_id = r.id
  LEFT JOIN competition_seasons s ON r.season_id = s.id
  LEFT JOIN competition c ON c.id = s.competition_id
  WHERE 1 AND m.status IN (1, 2, 3, 4, 5) AND m.round_id IN (10, 488, 392, 70)  
  AND m.gameweek = (SELECT MAX(m2.gameweek)
  FROM `match` m2 WHERE m2.round_id = m.round_id AND m2.status = m.status) 
  ORDER BY m.datetime DESC LIMIT 10 

主要问题和数据结构

主要问题:游戏周。我想返回已经玩过的比赛的下一个比赛周，所以假设我有以下比赛:

| id | round.id | status | gameweek | date
  1      488        5         1        1/07/2018
  2      488        5         1        1/07/2018
  3      488        5         1        1/07/2018
  4      488        1         2        7/07/2018
  5      488        1         2        7/07/2018
  6      488        1         2        7/07/2018
  7      488        1         3        16/07/2018
  8      488        1         3        16/07/2018
  9      488        1         3        16/07/2018

我的查询将返回比赛 ID:7、8、9。但应该返回:4、5、6，因为是下一个需要比赛的比赛周，比赛周 1 已经完全比赛。

注意，例如，如果在第 1 个比赛周中几乎有一场比赛尚未进行，则查询应返回第 1 个比赛周而不是第 2 个比赛周。

我该如何解决这个问题？

谢谢。

更多详细信息

在查询的第一部分中，我只选择了我希望从联接表中返回的字段。使用存储所有匹配项的主表匹配项进行选择。我在表中加入了 team 和 team_info，它获取了参加比赛的两支球队的信息。

在此之后，我加入了获取回合详细信息的 competition_rounds，回合只是比赛的组织，赛季也一样，例如:

round.name = Regular Season | season.id = 5 season.name = 2017/2018 | id = 5

这在这个问题中并不重要，但我正在尝试解释所有查询事件。

在 where 子句中，我得到了所有具有所有可用状态的匹配项，即:1、2、3、4、5(您可以在查询字段选择器案例中找到状态描述)。后来我还有一个过滤器，它返回属于这些回合的所有匹配项:10、488、392、70。

Answer 1

考虑以下几点:

DROP TABLE IF EXISTS my_table;

CREATE TABLE my_table
(id SERIAL PRIMARY KEY
,played TINYINT NOT NULL
,gameweek INT NOT NULL
);

INSERT INTO my_table VALUES
(1,1,1),
(2,1,1),
(3,1,1),
(4,1,2),
(5,1,2),
(6,0,2),
(7,0,3),
(8,0,3),
(9,0,3);     

SELECT MIN(gameweek) gameweek FROM my_table WHERE played = 0;
+----------+
| gameweek |
+----------+
|        2 |
+----------+

对于玩所有游戏的情况，我想你可以这样做 - 尽管可能有更优雅的解决方案......

UPDATE my_table SET played = 1;

SELECT COALESCE(y.gameweek,MAX(x.gameweek)) gameweek 
  FROM my_table x 
  LEFT 
  JOIN  
     ( SELECT MIN(gameweek) gameweek 
         FROM my_table 
        WHERE played = 0 
     ) y
    ON y.gameweek = x.gameweek;
+----------+
| gameweek |
+----------+
|        3 |
+----------+