SQL实现旅行记录表的行程映射转换需求

原始Travel表 #

期望输出表 #

转换规则 #

• 按学生分组，将该学生的旅行地点按旅行日期升序排序
• 相邻的地点依次映射为Travel_location1和Travel_location2，前一个地点作为前者，后一个作为后者
• 每个学生最后一个旅行地点对应的Travel_location2为null

解决方案（窗口函数实现） #

用ROW_NUMBER()给每个学生的旅行记录按日期排序，再用LEAD()函数获取下一个地点，就能直接得到结果：

WITH sorted_travel AS (
    SELECT 
        Student,
        `Travel Location` AS location,
        -- 按学生分组，将旅行日期转为日期类型后排序，生成行号
        ROW_NUMBER() OVER (PARTITION BY Student ORDER BY STR_TO_DATE(`Travel Date`, '%d-%m-%Y')) AS rn
    FROM Travel
)
SELECT 
    Student,
    location AS Travel_location1,
    -- 获取同组下一行的地点，没有则返回null
    LEAD(location) OVER (PARTITION BY Student ORDER BY rn) AS Travel_location2
FROM sorted_travel;

不同数据库的日期转换适配

• PostgreSQL：把STR_TO_DATE(Travel Date, '%d-%m-%Y')换成TO_DATE("Travel Date", 'DD-MM-YYYY')
• SQL Server：换成CONVERT(date, "Travel Date", 105)

兼容老版本数据库的自连接实现 #

如果你的数据库不支持窗口函数，可以用自连接的方式：

SELECT 
    t1.Student,
    t1.`Travel Location` AS Travel_location1,
    t2.`Travel Location` AS Travel_location2
FROM (
    SELECT 
        Student,
        `Travel Location`,
        ROW_NUMBER() OVER (PARTITION BY Student ORDER BY STR_TO_DATE(`Travel Date`, '%d-%m-%Y')) AS rn
    FROM Travel
) t1
LEFT JOIN (
    SELECT 
        Student,
        `Travel Location`,
        ROW_NUMBER() OVER (PARTITION BY Student ORDER BY STR_TO_DATE(`Travel Date`, '%d-%m-%Y')) AS rn
    FROM Travel
) t2 ON t1.Student = t2.Student AND t1.rn + 1 = t2.rn
ORDER BY t1.Student, t1.rn;

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