如何根据返回值执行不同的查询? [英] How to execute different query based on returned value?
问题描述
我有一个足球比赛的清单，定义如下:
I have a list of football matches defined as follow:
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而不是全部.
I need to return all the matches which have as gameweek the next gameweek to the finished matches not all.
有些rounds没有任何gameweek，因此在这种情况下，应返回所有matches，在完成的match旁边带有datetime的所有matches.
Some rounds doesn't have any gameweek, so in that case should be returned all the matches which have a datetime next to the finished match.
我写的查询是这样的:
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条记录，我不明白为什么，唯一的原因是一条记录也有时间.
this return only 9 records, and I don't understand why, the only reason is that one record have the time too.
完成matches是什么意思?
What does finished matches mean?
对于matches结束或完成，我的意思是每个match的状态为5或3.状态1表示match已安排，但尚未播放. 5表示finished和3已取消.
For matches ended or finished I mean that the status of each match is 5 or 3. A status of 1 means that the match is scheduled, but hasn't played yet; 5 means finished and 3 canceled.
例如:
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条记录.
as you can see the first three records are already played. In that case the query need to return all the matches (played and scheduled) because the gameweek 29 contains also other matches that hasn't played yet, so the expected result are all the 10 records.
预期结果:1、2、3、4、5、6、7、8、9、10
expected result: 1, 2, 3, 4, 5, 6, 7, 8, 9, 10
另一个重要的事情是某些round没有任何gameweek，因此，假设这个，我们需要返回即将到来的matches，例如:
Another important thing is that some round doesn't have any gameweek, so supposing this, we need to return the upcoming matches, eg:
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
expected result: 4, 5, 6, 7, 8, 9, 10
(在小提琴中，缺少记录7).
(In the fiddle the record 7 is missing).
如果没有gameweeks，但是所有matches已完成(状态5)，那么我们需要返回最新的datetime的所有matches，例如:
If there are no gameweeks, but all the matches are finished (status 5), then we need to return all the matches of the latest datetime, eg:
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，
我创建了一个小提琴，其中涵盖了所有情况. /p>
I created a fiddle here which covers all the cases.
推荐答案
这看起来像是不必要的复杂查询，但确实与上述输出匹配.可能是一个很好的起点.
This feels like an unnecessarily complex query, but it does match the outputs stated above. Might be a good starting point.
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 Fiddle查询发布的场景
DB Fiddle Queries on scenarios posted
- 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
- 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
这篇关于如何根据返回值执行不同的查询?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
