SQL查询：获取travel_manifest表中乘客当前预订及历史出行日期

首先先看一下你的travel_manifest表结构和数据：

+----+--------------+-------------+
| id | pax          | travel_date |
+----+--------------+-------------+
| 1  | passenger1   | 2018-06-14  |
| 2  | passenger2   | 2018-06-14  |
| 3  | passenger3   | 2018-03-24  |
| 4  | passenger1   | 2018-03-16  |
| 5  | passenger1   | 2018-02-05  |
| 6  | passenger3   | 2018-01-11  |
+----+--------------+-------------+

你的需求 #

你需要查询出行日期晚于'2018-05-14'的乘客列表，同时展示每位乘客对应的所有历史出行日期。而你现在的查询语句只能筛选出符合日期条件的记录，没办法带出历史出行信息：

$currentDate = '2018-05-14';
SELECT pax,travel_date FROM travel_manifest WHERE travel_date > '2018-05-14';

（这里提醒下：你的SQL里日期没加单引号，会被当成数值计算，一定要加上单引号哦！）

解决方案 #

根据你的需求，我给你两种常用的实现方式：

方式1：获取所有历史出行日期（拼接为字符串）

如果想把乘客的所有历史出行日期一次性展示出来，可以用GROUP_CONCAT函数配合自连接来实现：

SELECT 
    tm.pax AS name,
    tm.travel_date,
    GROUP_CONCAT(tm_history.travel_date ORDER BY tm_history.travel_date DESC) AS previous_travel_dates
FROM travel_manifest tm
LEFT JOIN travel_manifest tm_history 
    ON tm.pax = tm_history.pax 
    AND tm_history.travel_date < tm.travel_date
WHERE tm.travel_date > '2018-05-14'
GROUP BY tm.id, tm.pax, tm.travel_date;

这个语句的逻辑是：

• 主表tm筛选出所有符合日期条件的出行记录
• 自连接tm_history关联同一个乘客的所有早于当前出行日期的历史记录
• 用GROUP_CONCAT把历史日期按倒序拼接成字符串，方便查看最近的出行记录
• 分组确保每条符合条件的出行记录都对应正确的历史日期

查询结果会是这样：

方式2：仅获取最近一次历史出行日期

如果你只需要乘客的上一次出行日期，可以用窗口函数LAG，写法更简洁：

SELECT 
    pax AS name,
    travel_date,
    LAG(travel_date) OVER (PARTITION BY pax ORDER BY travel_date) AS last_travel_date
FROM travel_manifest
WHERE travel_date > '2018-05-14'
ORDER BY pax, travel_date;

这个语句会给每条符合条件的记录，带上同一个乘客的上一次出行日期，结果如下：

注意点 #

你示例里的结果有重复的travel_date字段，这在SQL里是不允许的（字段名必须唯一），所以上面的方案里我把历史日期字段命名为previous_travel_dates或last_travel_date，你可以根据自己的需求调整字段名。

内容的提问来源于stack exchange，提问作者marcus muli