我有一个足球比赛列表,定义如下:
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
的matches
返回到完成的matches
而不是全部。
有些rounds
没有任何gameweek
,所以在这种情况下,应该返回所有在完成的match
旁边有一个datetime
的matches
。
我写的查询是这样的:
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
,我的意思是每个match
的状态是5
或3
。状态为1
表示match
已安排,但尚未播放;5
表示finished
和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
如您所见,前三张唱片已经播放完毕。在这种情况下,查询需要返回所有matches
(播放和计划),因为gameweek
29 还包含尚未播放的其他matches
,因此预期结果是所有 10 条记录。
预期成果: 1, 2, 3, 4, 5, 6, 7, 8, 9, 10
另一个重要的事情是有些round
没有任何gameweek
,所以假设这一点,我们需要返回即将到来的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 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
,但所有matches
都已完成(状态 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,
我在这里创建了一个涵盖所有情况的小提琴。
这感觉像是一个不必要的复杂查询,但它确实与上述输出匹配。可能是一个很好的起点。
with current_round as (
select *
from match_case_1
where round_id = 12696
)
select *
from current_round cr
where
(
not exists(select * from current_round where gameweek is null)
)
or
(
exists(select * from current_round where status = 1)
and not exists(select * from current_round where gameweek is not null)
and cr.status = 1
)
or
(
not exists(select * from current_round where status = 1)
and not exists(select * from current_round where gameweek is not null)
and cast(cr.`datetime` as date) = (
select max(cast(`datetime` as date)) as `date`
from current_round
where status = 5 or status = 3
)
);
编辑
DB小提琴查询发布的方案
- https://www.db-fiddle.com/f/2f7NEPo72tUM7zLHBX2GXQ/0
- https://www.db-fiddle.com/f/3ghG6zf7hv2SbACL9vtnJa/1
- https://www.db-fiddle.com/f/q7DgtJRfDxyPA8bQheRncA/1
- https://www.db-fiddle.com/f/tV7VhZg1Ywfx1YmnFMtZdh/1
鉴于您的匹配看起来不会经常更改,我的建议是省去头痛并将这些查询拆分到您的应用程序层(如果存在)
从您的示例中,您可以向数据库提出一系列问题以获得您想要的结果
有没有最低的比赛周有未完成的比赛 12696?
SELECT MIN(gameweek) min_gameweek
FROM `match`
WHERE round_id = 12696
AND gameweek IS NOT NULL
AND status = 1
如果是..那个比赛周的比赛是什么?
SELECT *
FROM `match`
WHERE round_id = 12696
AND gameweek = :min_gameweek
如果没有。(您可能希望尝试在此处找到第 12696 轮已完成比赛的最大比赛周,并列出其中的所有比赛)
如果仍然没有。.第12696轮没有比赛周的未完成比赛是什么?
SELECT *
FROM `match`
WHERE round_id = 12696
AND gameweek IS NULL
AND status = 1
如果没有..他们是第 12696 轮没有比赛周的结束比赛的最新日期时间吗?
SELECT MAX(`datetime`) latest_datetime
FROM `match`
WHERE round_id = 12696
AND gameweek IS NULL
AND status != 1
如果是..在第 12696 轮中,哪些没有比赛周的已完成比赛具有此最新日期时间?
SELECT *
FROM `match`
WHERE round_id = 12696
AND `datetime` = :latest_datetime
注:注:这种方法涉及更多的查询,但更具可读性、可调试性和灵活性,甚至可能比尝试一个怪物查询的性能更好。
您可能会将上面的一些查询与添加过多的不透明度结合起来,但我仍然会从写出每个步骤并对其进行测试开始
我还认真地建议将整个过程简化如下
- 在圆桌会议中存储每个回合的当前游戏周和最大游戏周(如果该回合没有游戏周,则为空)
- 只需显示相关回合当前比赛周的所有比赛 当比赛结束时
- (或在一天/一周结束时),如果所有比赛都已完成且尚未达到最大值,则增加当前比赛周
这是你想要的吗?
SELECT m.*
FROM `match` m
WHERE m.round_id = 48 AND
m.gameweek = (SELECT MAX(m1.gameweek)
FROM `match` m1
WHERE m1.round_id = m.round_id AND m1.status = 1
);
这样,您将获得每场比赛的最后gameweek
。
只需使用 1 周的未来过滤器
select m.* from `match` m
WHERE DATE(datetime)>=date(now())
and DATE(datetime) <= date_add(current_date, INTERVAL 1 week)
and m.status =1