MySQL ORDER BY 不同where子句中的多个元素

Question

我有一个问题，我不知道如何准确地调用它。也许我只是没有使用正确的命名，因此我找不到答案。

但是事情是这样的。我有一个数据库表，其中包含与以下类似的数据:

• booking_id(整数)
• booking_start(年-月-日)
• booking_starttime(H:i)
• booking_hotelstart(年-月-日)
• booking_hotelstarttime(年-月-日)
• booking_hotelend(年-月-日)
• booking_hotelendtime(H:i)
• booking_end(年-月-日)
• 预订结束时间(H:i)
• booking_confirmed( bool )

现在我想做一个与此相关的查询:(无效查询只是为了演示我想要的内容)

SELECT `booking_id` FROM `system_bookings` WHERE (
(`booking_start`='2014-10-20' ORDER BY `booking_starttime` ASC)
OR
(`booking_hotelstart`='2014-10-20' ORDER BY `booking_hotelstarttime` ASC)
OR
(`booking_hotelend`='2014-10-20' ORDER BY `booking_endtime` ASC)

OR
(`booking_end`='2014-10-20' ORDER BY `booking_endtime` ASC)
)
AND
`booking_confirmed` = TRUE LIMIT 0, 100

所以基本上是带有子句的 ORDER BY。但如何做到这一点？我不知道如何正确搜索这个。因此，我希望有人能告诉我我应该前进的方向。除此之外。我想知道这个怎么称呼。用于下次搜索。

提前致谢!!!

<小时/>

编辑:

我根据要求创建了一些示例数据:

CREATE TABLE IF NOT EXISTS `system_bookings` (
  `booking_id` int(6) NOT NULL AUTO_INCREMENT,
  `booking_start` date NOT NULL,
  `booking_starttime` varchar(5) NOT NULL,
  `booking_hotelstart` date NOT NULL,
  `booking_hotelstarttime` varchar(5) NOT NULL,
  `booking_hotelend` date NOT NULL,
  `booking_hotelendtime` varchar(5) NOT NULL,
  `booking_end` date NOT NULL,
  `booking_endtime` varchar(5) NOT NULL,
  `booking_confirmed` tinyint(1) NOT NULL,
  PRIMARY KEY (`booking_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=6 ;



INSERT INTO `system_bookings` (`booking_id`, `booking_start`, `booking_starttime`, `booking_hotelstart`, `booking_hotelstarttime`, `booking_hotelend`, `booking_hotelendtime`, `booking_end`, `booking_endtime`, `booking_confirmed`) VALUES
(1, '2014-10-09', '21:19', '2014-10-08', '21:19', '2014-10-23', '08:00', '2014-10-23', '22:00', 1),
(2, '2014-10-11', '16:00', '2014-10-27', '12:15', '2014-10-28', '17:45', '2014-10-28', '17:45', 1),
(3, '2014-10-10', '20:30', '2014-10-10', '20:30', '2014-10-11', '08:00', '2014-10-20', '14:00', 1),
(4, '2014-10-12', '20:00', '2014-10-12', '20:00', '2014-10-13', '05:00', '2014-10-29', '22:00', 0),
(5, '2014-10-22', '15:00', '2014-10-22', '20:30', '2014-10-23', '04:15', '2014-10-31', '12:00', 1);

Answer 1

您可以在 order by 子句中包含多个条件。所以，正式地，你似乎想要这个:

SELECT `booking_id`
FROM `system_bookings`
WHERE `booking_confirmed` = TRUE AND
       (`booking_start` = '2014-10-20' OR
        `booking_hotelstart` = '2014-10-20' OR
        `booking_hotelend`='2014-10-20' OR
        `booking_end`='2014-10-20'
       )
ORDER BY (CASE WHEN `booking_start` = '2014-10-20' THEN `booking_starttime` 
               WHEN `booking_hotelstart` = '2014-10-20' THEN `booking_hotelstarttime`
               WHEN `booking_hotelend` = '2014-10-20' THEN `booking_endtime`
               WHEN `booking_end` = '2014-10-20' THEN `booking_endtime`
          END)
LIMIT 0, 100;

但是，这有点无意义，因为您正在将一个值与一个常量进行比较，然后按该值进行排序。我怀疑您想要优先考虑 where 子句，并且确实想要更多类似这样的内容:

ORDER BY (CASE WHEN `booking_start` = '2014-10-20' THEN 1
               WHEN `booking_hotelstart` = '2014-10-20' THEN 2
               WHEN `booking_hotelend` = '2014-10-20' THEN 3
               WHEN `booking_end` = '2014-10-20' THEN 4
          END)