根据返回值执行不同查询的方法是什么？

Question

我有一个足球比赛列表，定义如下：

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，包含了所有情况。

- sfarzoso

---

你应该澄清你的问题：什么是“next to the finished matches”的意思？你能给一些例子吗？如果比赛在过去，那么它们都已经结束了，如果比赛在未来，那么它们都没有结束，这不是吗？你的查询示例肯定是错误的，因为没有定义m2。你试图实现什么并不是非常清楚。 - Dinu

@Dinu 你好，感谢你对这个问题的关注。我更新了一些我错过的其他细节，对此我很抱歉。如果你有任何其他问题，请告诉我，我也会进行澄清。 - sfarzoso

@sfarzoso：我认为真正有帮助的是提供一组数据，通常代表您的用例，以及相应期望的结果。如果要求过于复杂，可以提供几组样本数据/期望输出。 - GMB

@sfarzoso，我很感兴趣想知道.. “这个只返回8条记录，我不明白为什么” .. 这8条记录是否返回了正确的数据？ - FanoFN

好的，各位，我更新了问题，使其更加清晰，并且我还添加了一个 fiddle：https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=71830cc9aa593b1e8d0977a55509e5c4，它涵盖了所有情况，请让我知道您对该问题是否有其他疑问。 - sfarzoso

Answer 1

这个查询感觉有点复杂，但它确实与上面所述的输出匹配。可能是一个不错的起点。

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查询有关发布的场景

1. https://www.db-fiddle.com/f/2f7NEPo72tUM7zLHBX2GXQ/0
2. https://www.db-fiddle.com/f/3ghG6zf7hv2SbACL9vtnJa/1
3. https://www.db-fiddle.com/f/q7DgtJRfDxyPA8bQheRncA/1
4. https://www.db-fiddle.com/f/tV7VhZg1Ywfx1YmnFMtZdh/1

- Rob Taylor

---

可以，但这样做可能会牺牲一些清晰度。首先要做的是摆脱CTE，因为它是为了我能够快速切换每个情况而添加的。然后，您可以通过重复组合一些查询，例如查找是否存在任何比赛周值。虽然缩短查询长度，但您可能会失去对每个部分描述的清晰度。 - Rob Taylor

您的查询似乎几乎涵盖了所有情况，除了一个：db-fiddle.com/f/3ghG6zf7hv2SbACL9vtnJa/2 如果您查看那里，会发现在第29轮比赛之后还有另一轮比赛，在这种情况下，需要返回第29轮比赛的记录而不是所有其他记录，例如：1,2,3,4,5,6,7,8,9,10 您的查询返回了全部记录。 - sfarzoso

Answer 2

鉴于您的比赛看起来不会经常更改，我的建议是在应用程序层（如果存在）中拆分这些查询，以避免头痛。
从您的示例中，您可以向数据库提出一系列问题，以获得所需的结果。
“在第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轮中未完成比赛中没有游戏周是哪些？

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

注意：这种方法需要更多的查询，但可读性、可调试性和灵活性都远远优于尝试使用一个巨大的查询，甚至可能比其表现更好

您可能可以将上面的某些查询合并，并添加过多的不透明度，但我仍然建议先将每个步骤写出来并进行测试

我还强烈建议简化整个流程如下：

• 在轮次表中存储当前轮次和每轮最大轮次（如果该轮没有比赛则为NULL）
• 只需显示该轮次当前比赛周的所有比赛
• 当比赛结束时（或在一天/一周结束时），如果所有比赛都已结束且尚未达到最大值，则增加当前比赛周数

- Arth

---

我喜欢你组织查询的方式，但是你的逻辑应该在获得结果之前执行不同的查询，那么我该如何实现这个解决方案呢？具体来说，我应该检查每个查询是否返回结果，如果没有，则执行下一个查询。 - sfarzoso

是的，我建议分别运行每个查询，必要时检查结果，然后根据结果运行其他查询。如果您的项目有应用程序层，可以在该层执行此操作。 - Arth

这个解决方案不是比一个查询多次检查更耗费性能吗？ - sfarzoso

@sfarzoso 可能吧，但不一定。一个庞大的查询会非常复杂，可能会导致引擎进行额外的工作，检查和连接您可以通过分别执行查询来排除的内容。单个复杂查询的执行计划也更有可能随着表格的增长或更改而发生变化...这可能会导致意想不到的减速。即使将查询分开最终变得更慢，如果减速显著，我会非常惊讶。 - Arth

Answer 3

只需使用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

- Zaynul Abadin Tuhin

---

如果您查看我的更新，您就会发现您的查询并没有覆盖所有情况，事实上对于最后一个情况并不起作用，但对于其他情况是正确的，您能修复一下吗？ - sfarzoso

@sfarzoso 你怎么知道没有游戏周？ - Zaynul Abadin Tuhin

因为字段gameweek为空，所以一些比赛没有轮次。 - sfarzoso

Answer 4

这是你想要的吗？：

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。

- Yogesh Sharma