MySQL:是否可以创建查询来评估来自不同记录的数据? [英] MySQL: Is it possible to create a query that evaluates data from different records?

问题描述

我有一张这样的桌子.它显示了不同餐厅在不同工作日的开始时间和结束时间.

I've got a table like this. It shows the starttime and endtime of different restaurants on different weekdays.

<身体>

否	餐厅	平日	开始时间	结束时间
1	A	星期一	10:00	21:00
2	A	星期二	10:00	21:00
3	A	星期三	19:00	08:00
4	A	星期四
5	A	星期五	10:00	21:00
6	A	星期六	10:00	21:00
7	A	Sun	19:00	08:00
8	B	星期一	11:00	21:00
9	B	星期二	11:00	21:00
10	B	星期三	11:00	23:00
11	B	星期四	11:00	23:00
12	B	星期五	11:00	16:00
13	B	星期六	10:00	00:00
14	B	Sun	00:00	13:00

我想要的是这个.给定当前时间，代码应显示当前营业的餐厅.这就是我尝试过的.

What I want is this. Given the current time, the code should display which restaurant is currently open. This is what I tried.

(CASE WHEN user_time.start_time > user_time.end_time AND ( CURTIME() < user_time.end_time OR CURTIME() > user_time.start_time ) THEN 1
      WHEN user_time.start_time < user_time.end_time AND ( CURTIME() > user_time.start_time AND CURTIME() < user_time.end_time ) THEN 1
      ELSE 0
 END) = 0 AND user_time.weekday = WEEKDAY(CURDATE())

查找当前工作日以及对应的开放时间和结束时间，以与当前时间进行比较.但是，当餐厅从晚上开始到第二天结束时，就会出现问题.

It finds the current weekday and the corresponding opentime and endtime to compare with the current time. But the problem occurs when a restaurant starts in the evening and ends the next day.

1. 在4号餐厅的情况下，餐厅A关闭，但实际上一直营业至8:00.如果当前时间是7:00，则餐厅A应该显示为营业.
2. 当我们看1号和7号时，星期一的前一天是星期日.因此，如果当前时间是星期一7:00 AM，则查询应将时间与星期天的开放时间，并给结果开放".
3. 我也想执行相反的查询，找到目前关闭的餐厅.

这意味着查询应该能够决定使用哪个时间数据，今天还是昨天.请帮助我找到解决方案.

This means the query should be able to decide which time data to use, today's or yesterday's. Please help me find a solution.

MySQL服务器版本为10.4.17 MariaDB.

MySQL server version is 10.4.17 MariaDB.

No的数据类型为int，其他所有类型为VARCHAR.

The datatype of No is int, everything else is VARCHAR.

推荐答案

我已经建立了一个包含2个新虚拟字段的表.此字段是自动填写的.我在工作日使用一些小技巧.因此1生成一个日期，而工作日是一个月中的某天.它仅用于比较.

I have build a Table with 2 new virtual fields. This fields are automatic filled. I use a little trick for the weekday. So 1 generate a date and the weekday is the the day of month. It only use for compare.

SELECT * 
FROM user_time
WHERE
    TIMESTAMP(CONCAT('2000-01-0',DAYOFWEEK(CURDATE()),' ',current_time()))
    BETWEEN date_start AND date_end
OR
    IF(DAYOFWEEK(CURDATE()) <> 'SUNDAY' , FALSE,
    TIMESTAMP(CONCAT('2000-01-0',DAYOFWEEK(CURDATE())+7,' ',current_time()))
    BETWEEN date_start AND date_end
    );

表格

CREATE TABLE `user_time` (
  `No` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `Restaurant` varchar(32) DEFAULT NULL,
  `Weekday` enum('SUNDAY','MONDAY','TUESDAY','WEDNESDAY','THURSDAY','FRIDAY','SATURDAY') DEFAULT NULL,
  `start_time` varchar(5) DEFAULT NULL,
  `end_time` varchar(5) DEFAULT NULL,
  `date_start` timestamp GENERATED ALWAYS AS (cast(concat('2000-01-0',`Weekday` + 0,' ',`start_time`) as datetime)) STORED,
  `date_end` timestamp GENERATED ALWAYS AS (cast(concat('2000-01-0',`Weekday` + 0,' ',`end_time`) + interval if(cast(`start_time` as time) >= cast(`end_time` as time),1,0) day as datetime)) STORED,
  PRIMARY KEY (`No`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4;

插入一些数据

INSERT INTO `user_time` (`No`, `Restaurant`, `Weekday`, `start_time`, `end_time`)
VALUES
    (1, 'a', 'MONDAY', '12:00', '13:00'),
    (2, 'a', 'MONDAY', '14:00', '02:00'),
    (3, 'b', 'MONDAY', '16:00', '18:00'),
    (4, 'c', 'TUESDAY','21:00', '18:00');

查看生成的内容

MariaDB [bernd]> SELECT * FROM user_time;
+----+------------+---------+------------+----------+---------------------+---------------------+
| No | Restaurant | Weekday | start_time | end_time | date_start          | date_end            |
+----+------------+---------+------------+----------+---------------------+---------------------+
|  1 | a          | MONDAY  | 12:00      | 13:00    | 2000-01-02 12:00:00 | 2000-01-02 13:00:00 |
|  2 | a          | MONDAY  | 14:00      | 02:00    | 2000-01-02 14:00:00 | 2000-01-03 02:00:00 |
|  3 | b          | MONDAY  | 16:00      | 18:00    | 2000-01-02 16:00:00 | 2000-01-02 18:00:00 |
|  4 | c          | TUESDAY | 21:00      | 18:00    | 2000-01-03 21:00:00 | 2000-01-04 18:00:00 |
+----+------------+---------+------------+----------+---------------------+---------------------+
4 rows in set (0.01 sec)

MariaDB [bernd]> 

查询当前日期

MariaDB [bernd]> SELECT * 
    -> FROM user_time
    -> WHERE
    -> TIMESTAMP(CONCAT('2000-01-0',DAYOFWEEK(CURDATE()),' ',current_time()))
    -> BETWEEN date_start AND date_end;
+----+------------+---------+------------+----------+---------------------+---------------------+
| No | Restaurant | Weekday | start_time | end_time | date_start          | date_end            |
+----+------------+---------+------------+----------+---------------------+---------------------+
|  4 | c          | TUESDAY | 21:00      | 18:00    | 2000-01-03 21:00:00 | 2000-01-04 18:00:00 |
+----+------------+---------+------------+----------+---------------------+---------------------+
1 row in set (0.00 sec)

MariaDB [bernd]> 

这篇关于MySQL:是否可以创建查询来评估来自不同记录的数据?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！