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

Question

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

我想把两个日期字段组合在一起，然后按日期，到达时间，出发时间排序。

所以我想要实现的是按顺序显示接下来的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(...)替换日期字符串。

Demo on db<>fiddle