我有一个足球比赛列表,定义如下:
id | datetime | status | gameweek | round_id | home_team_id
1 2019-03-31 00:00:00 1 29 12696 1243
2 2019-03-31 00:00:00 1 29 12696 1248
3 2019-03-31 00:00:00 1 29 12696 1242
4 2019-03-31 00:00:00 1 29 12696 1246
5 2019-03-31 00:00:00 1 29 12696 1244
6 2019-03-31 00:00:00 1 29 12696 1247
7 2019-03-31 20:30:00 1 29 12696 1241
8 2019-03-31 00:00:00 1 29 12696 1249
9 2019-03-31 00:00:00 1 29 12696 2981
10 2019-03-31 00:00:00 1 29 12696 1259
我需要返回所有比赛中符合以下条件的数据:其“gameweek”属性应为下一轮比赛的“gameweek”,而不是所有已结束的比赛。但有些“rounds”没有任何“gameweek”,在这种情况下,应返回所有具有接近结束比赛的“datetime”的比赛数据。以下是我编写的查询:
Select m.* from `match` m where round_id = 12696 and m.datetime = (SELECT COALESCE(MIN(CASE WHEN m2.status < 5 THEN m2.datetime END), MAX(m2.datetime)) FROM `match` m2 WHERE m2.round_id = m.round_id)
这里只返回了9条记录,我不明白原因是什么,唯一的原因可能是其中一条记录有时间限制。
“matches”这个词是什么意思?
在这里,“matches”指的是比赛。当比赛的状态为“5”或“3”时,我们认为比赛已经结束或取消。如果比赛的状态为“1”,则表示该比赛预定了比赛时间,但还没有进行;“5”表示比赛已经结束,“3”表示比赛被取消。
例如:
id | datetime | status | gameweek | round_id | home_team_id
1 2019-03-20 00:00:00 5 29 12696 1243
2 2019-03-20 00:00:00 5 29 12696 1248
3 2019-03-20 00:00:00 5 29 12696 1242
4 2019-03-31 00:00:00 1 29 12696 1246
5 2019-03-31 00:00:00 1 29 12696 1244
6 2019-03-31 00:00:00 1 29 12696 1247
7 2019-03-31 20:30:00 1 29 12696 1241
8 2019-03-31 00:00:00 1 29 12696 1249
9 2019-03-31 00:00:00 1 29 12696 2981
10 2019-03-31 00:00:00 1 29 12696 1259
正如您所看到的,前三条记录已经播放过了。在这种情况下,查询需要返回所有的比赛(已经播放和计划中的),因为第29轮还有其他尚未播放的比赛,所以预期结果是10条记录。
预期结果: 1, 2, 3, 4, 5, 6, 7, 8, 9, 10
另一个重要的事情是,有些回合没有任何游戏周数,所以假设这一点,我们需要返回即将到来的比赛,例如:
id | datetime | status | gameweek | round_id | home_team_id
1 2019-03-20 00:00:00 5 NULL 12696 1243
2 2019-03-20 00:00:00 5 NULL 12696 1248
3 2019-03-20 00:00:00 5 NULL 12696 1242
4 2019-03-31 00:00:00 1 NULL 12696 1246
5 2019-03-31 00:00:00 1 NULL 12696 1244
6 2019-03-31 00:00:00 1 NULL 12696 1247
7 2019-03-31 20:30:00 1 NULL 12696 1241
8 2019-03-31 00:00:00 1 NULL 12696 1249
9 2019-03-31 00:00:00 1 NULL 12696 2981
10 2019-03-31 00:00:00 1 NULL 12696 1259
期望结果: 4, 5, 6, 7, 8, 9, 10
(在示例中,记录7已丢失)。
如果没有比赛周(gameweeks
),但是所有比赛都已结束(状态为5),则需要返回最新datetime
的所有matches
,例如:
id | datetime | status | gameweek | round_id | home_team_id
1 2019-03-20 00:00:00 5 NULL 12696 1243
2 2019-03-20 00:00:00 5 NULL 12696 1248
3 2019-03-20 00:00:00 5 NULL 12696 1242
4 2019-03-31 00:00:00 5 NULL 12696 1246
5 2019-03-31 00:00:00 5 NULL 12696 1244
6 2019-03-31 00:00:00 5 NULL 12696 1247
7 2019-03-31 20:30:00 5 NULL 12696 1241
8 2019-03-31 00:00:00 5 NULL 12696 1249
9 2019-04-05 00:00:00 5 NULL 12696 2981
10 2019-04-05 00:00:00 5 NULL 12696 1259
预期结果: 9, 10
id | datetime | status | gameweek | round_id | home_team_id
1 2019-03-20 00:00:00 5 28 12696 1243
2 2019-03-20 00:00:00 5 28 12696 1248
3 2019-03-20 00:00:00 1 28 12696 1242
4 2019-03-31 00:00:00 1 28 12696 1246
5 2019-04-05 00:00:00 5 29 12696 1244
6 2019-04-05 00:00:00 5 29 12696 1247
7 2019-04-05 20:30:00 5 29 12696 1241
8 2019-04-05 00:00:00 5 29 12696 1249
期望结果: 1,2,3,4,
我在这里创建了一个Fiddle,包含了所有情况。