mysql - 从表中选择次日发生

Question

这是我的桌面游戏日的数据库结构。每场比赛在晚上 8:00 结束。这里所有的游戏 id 都是相同的。所以现在我想通过游戏 id 像这样查询，如果今天是星期一并且在晚上 8:00 之前，那么它只会获取值为 day='Monday' 的 game_days。一旦晚上 8:00 结束，它将显示值为 day='Wednesday' 的行，直到周三晚上 8:00，晚上 8:00 之后，它将显示值为 day='Friday' 的行，直到星期五 8： 00pm，然后在星期五晚上 8:00 之后再次显示星期一...

那么对此的查询是什么？

标签： mysqlsql

Answer 1

Please try this (updated based on comments, replace table_name with appropriate table name is your db):

SELECT * 
FROM `table_name`
WHERE day = (
    SELECT t3.day FROM `table_name` as t3
    LEFT JOIN (
        SELECT HOUR(NOW()), 
        CASE
            WHEN HOUR(NOW()) <= 19 THEN DAYNAME(NOW() + INTERVAL t.p DAY)
            ELSE DAYNAME(NOW() + INTERVAL (t.p + 1) DAY)
        END AS day, 
        CASE
            WHEN HOUR(NOW()) <= 19 THEN  t.p
            ELSE t.p + 1
        END as p
        FROM (
            SELECT 0 as p
            UNION SELECT 1 as p 
            UNION SELECT 2 as p
            UNION SELECT 3 as p
            UNION SELECT 4 as p
            UNION SELECT 5 as p
            UNION SELECT 6 as p
        ) t
   ) t2 on t3.day = t2.day
   ORDER BY t2.p ASC LIMIT 1
)