mysql - SQL "First relevant day"

Question

我有一张餐厅的 opening_hours 表:

SELECT * FROM opening_hours;
+----+---------------+------------+----------+-----+
| id | restaurant_id | start_time | end_time | day |
+----+---------------+------------+----------+-----+
|  1 |             1 | 12:00:00   | 18:00:00 |   1 |
|  2 |             1 | 09:00:00   | 19:00:00 |   4 |
|  3 |             2 | 09:00:00   | 16:00:00 |   4 |
|  4 |             2 | 09:00:00   | 16:00:00 |   5 |
|  5 |             3 | 09:00:00   | 16:00:00 |   4 |
|  6 |             3 | 09:00:00   | 16:00:00 |   5 |
|  7 |             3 | 09:00:00   | 16:00:00 |   1 |
|  8 |             3 | 09:00:00   | 16:00:00 |   6 |
+----+---------------+------------+----------+-----+

http://www.sqlfiddle.com/#!2/eaea09/1

现在我想为每家餐厅获取“最近”的第二天或当天。例如，当当天是 1 时，结果将是:

restaurant_id: 1 day: 1
restaurant_id: 2 day: 4
restaurant_id: 3 day: 1

在第 1 天的情况下，我可以这样做:

SELECT day FROM opening_hours WHERE day >= 1 GROUP BY restaurant_id LIMIT 1

但是如果今天是 6 那将不起作用。我需要查询去获取最大天数 (7)，如果找不到它应该再次从 1 开始尝试。所以第 6 天的结果在这种情况下是:

restaurant_id: 1 day: 1
restaurant_id: 2 day: 4
restaurant_id: 3 day: 6

我如何通过查询实现这一点？

我认为在伪 SQL 中可能是这样的:

SELECT `day` FROM opening_hours WHERE `day` >= 'today' IF NOT FOUND WHERE `day` >= 1 GROUP BY `restaurant_id` LIMIT 1

编辑:

我可以运行 2 个查询，并确定是否在第一个中找到了餐厅的匹配项。如果没有，请运行第二个查询。但必须有更好的方法。

Answer 1

经过测试，这个有效:) 你可以保持简单。

查询:

SELECT * FROM (
    SELECT
    oh.*
    FROM
    opening_hours oh
    ORDER BY restaurant_id, 
    `day` + IF(`day` < $current_day, 7, 0)
) sq
GROUP BY restaurant_id;

解释:

不过请注意，这有点 hacky。要选择未在 group by 中使用且未应用聚合函数的列，通常是不允许的，因为理论上它可以为您提供每个组的随机行。这就是为什么它在大多数数据库系统中是不允许的。 MySQL 实际上是我所知道的唯一一个允许这样做的(如果没有通过 sql-mode 设置的话)。就像我说的，理论上。实际上它有点不同，如果您在子查询中执行 order by，MySQL 将始终为您提供最小值或最大值(取决于排序顺序)。

测试:

当前日期 = 1 的期望结果:

root@VM:playground > SELECT * FROM (
    ->     SELECT
    ->     oh.*
    ->     FROM
    ->     opening_hours oh
    ->     ORDER BY restaurant_id,
    ->     `day` + IF(`day` < 1, 7, 0)
    -> ) sq
    -> GROUP BY restaurant_id;
+----+---------------+------------+----------+-----+
| id | restaurant_id | start_time | end_time | day |
+----+---------------+------------+----------+-----+
|  1 |             1 | 12:00:00   | 18:00:00 |   1 |
|  3 |             2 | 09:00:00   | 16:00:00 |   4 |
|  7 |             3 | 09:00:00   | 16:00:00 |   1 |
+----+---------------+------------+----------+-----+
3 rows in set (0.00 sec)

当天的期望结果 = 6:

root@VM:playground > SELECT * FROM (
    ->     SELECT
    ->     oh.*
    ->     FROM
    ->     opening_hours oh
    ->     ORDER BY restaurant_id,
    ->     `day` + IF(`day` < 6, 7, 0)
    -> ) sq
    -> GROUP BY restaurant_id;
+----+---------------+------------+----------+-----+
| id | restaurant_id | start_time | end_time | day |
+----+---------------+------------+----------+-----+
|  1 |             1 | 12:00:00   | 18:00:00 |   1 |
|  3 |             2 | 09:00:00   | 16:00:00 |   4 |
|  8 |             3 | 09:00:00   | 16:00:00 |   6 |
+----+---------------+------------+----------+-----+
3 rows in set (0.00 sec)