php - 使用 mysql php 查找打开和关闭状态

Question

我有餐厅和餐厅时间表。我需要使用以下查询查找餐厅开业状态:

SELECT rest.resid, rest.resname, restime.tue_ot, restime.tue_ct,
     IF( (  (restime.tue_status = 'Open') AND 
            (CURTIME() BETWEEN restime.tue_ot AND restime.tue_ct, 'Open','Closed') = 'Open' ), 'Open', 'Closed') AS final_status
     FROM restaurant AS rest 
     LEFT JOIN restaurant_time AS restime ON rest.resid = restime.res_id 
     WHERE rest.restaurant_status = '1' 

我收到如下所示的 mysql 错误。

1241 - Operand should contain 3 column(s)

我需要像这样的输出。

resid   resname tue_status  tue_ot      tue_ct      final_status
1       aaaa    Open        08:00:00    23:00:00    Open
2       bbbb    Closed      21:00:00    23:00:00    Closed
3       cccc    Open        21:00:00    23:00:00    Closed
4       ddddd   Closed      08:00:00    23:00:00    Closed

数据库结构如下...

CREATE TABLE IF NOT EXISTS `restaurant` (
  `resid` int(11) NOT NULL AUTO_INCREMENT,
  `resname` varchar(10) NOT NULL,
  `restaurant_status` enum('0','1') NOT NULL,
  PRIMARY KEY (`resid`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=5 ;

--
-- Dumping data for table `restaurant`
--

INSERT INTO `restaurant` (`resid`, `resname`, `restaurant_status`) VALUES
(1, 'aaaa', '1'),
(2, 'bbbbb', '1'),
(3, 'ccccc', '1'),
(4, 'dddddd', '1');


-- Table structure for table `restaurant_time`


CREATE TABLE IF NOT EXISTS `restaurant_time` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `res_id` int(11) NOT NULL,
  `tue_status` enum('Open','Closed') NOT NULL DEFAULT 'Open',
  `tue_ot` time NOT NULL,
  `tue_ct` time NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=5 ;

--Dumping data for table `restaurant_time`
INSERT INTO `restaurant_time` (`id`, `res_id`, `tue_status`, `tue_ot`, `tue_ct`) VALUES
(1, 1, 'Open', '08:00:00', '23:00:00'),
(2, 2, 'Closed', '21:00:00', '23:00:00'),
(3, 3, 'Open', '21:00:00', '23:00:00'),
(4, 4, 'Closed', '08:00:00', '23:00:00');

Answer 1

以下查询将解决您的问题

SELECT rest.resid, rest.resname, restime.`tue_status`, restime.tue_ot, restime.tue_ct, 
 CASE 
  WHEN (CURTIME() BETWEEN restime.tue_ot AND restime.tue_ct AND restime.`tue_status` = 'Open') 
  THEN 'Open' ELSE 'CLOSED' 
 END as final_status 
FROM restaurant as rest 
JOIN  `restaurant_time` as restime ON rest.resid = restime.`res_id`