MySQL按4个字段日期和时间排序

Question

我有一个表，其中有2个日期列和2个时间列(到达日期、到达时间、出发日期、出发时间(

我想把这两个日期字段合并为一个，然后按日期、到达时间和出发时间排序。

所以我想实现的是按顺序显示接下来x天的到达和离开。

由此：

+--------------+--------------+----------------+----------------+--+
| arrival date | arrival time | departure date | departure time |  |
+--------------+--------------+----------------+----------------+--+
| 20-06-21     | 06:00        | NULL           | NULL           |  |
| 19-06-21     | 08:00        | 24-06-21       | 08:00          |  |
| NULL         | NULL         | 20-06-21       | 08:00          |  |
+--------------+--------------+----------------+----------------+--+

对此：

+----------+--------------+----------------+
| date     | arrival time | departure time |
+----------+--------------+----------------+
| 19-06-21 | 08:00        | NULL           |
+----------+--------------+----------------+
| 20-06-21 | 06:00        | NULL           |
+----------+--------------+----------------+
| 20-06-21 | NULL         | 08:00          |
+----------+--------------+----------------+
| 24-06-21 | NULL         | 08:00          |
+----------+--------------+----------------+

这是我现在的问题：

SELECT * FROM `bookings` WHERE STR_TO_DATE(`arrivalDate`, '%d-%m-%y') >= NOW() 
AND STR_TO_DATE(`arrivalDate`, '%d-%m-%y') <= DATE(DATE_ADD(NOW(), INTERVAL +20 DAY))  
OR STR_TO_DATE(`departureDate`, '%d-%m-%y') >= NOW() AND STR_TO_DATE(`departureDate`, '%d-%m-%y') <= DATE(DATE_ADD(NOW(), INTERVAL +20 DAY))
ORDER BY STR_TO_DATE(`arrivalDate`, '%d-%m-%y') asc, 
STR_TO_DATE(`arrivalTime`, '%h:%i') asc,  
STR_TO_DATE(`departureDate`, '%d-%m-%y') asc,  
STR_TO_DATE(`departureTime`, '%h:%i') asc

Answer 1

您首先需要将到达和离开时间分为两行(例如，在您的数据中，3行变为4行(。只有这样你才能对它们进行排序：

SELECT *
FROM (
SELECT arrivalDate AS date, arrivalTime, NULL AS departureTime
FROM t
WHERE arrivalDate IS NOT NULL
UNION ALL
SELECT departureDate, NULL, departureTime
FROM t
WHERE departureDate IS NOT NULL
) AS sq1
ORDER BY date, COALESCE(arrivalTime, departureTime)

在上面的示例中，必须将日期字符串替换为STR_TO_DATE(...)。

db<上的演示>小提琴