如何基于Source与Destination合并SQL旅行详情表中的行并聚合数据？

假设表结构 #

先假设你的旅行详情表（trip_details）字段如下，若实际字段不同可对应调整：

CREATE TABLE trip_details (
    trip_date DATE,       -- 旅行日期
    source VARCHAR(10),   -- 出发地
    destination VARCHAR(10), -- 目的地
    travel_time INT,      -- 行程时间（单位：分钟）
    distance INT          -- 行程距离（单位：公里）
);

解决方案 #

方法1：递归CTE（灵活适配多段行程） #

递归CTE可以自动串联同日期下连续的行程（A→B→C→D），最终聚合得到从A到D的总时间和距离：

WITH recursive_trips AS (
    -- 初始节点：选取所有从A出发的行程
    SELECT 
        trip_date,
        source AS start_source,
        destination AS current_dest,
        travel_time,
        distance,
        1 AS segment_count
    FROM trip_details
    WHERE source = 'A'
    
    UNION ALL
    
    -- 递归串联后续行程：上一段的终点作为当前段的起点
    SELECT 
        rt.trip_date,
        rt.start_source,
        td.destination AS current_dest,
        rt.travel_time + td.travel_time,
        rt.distance + td.distance,
        rt.segment_count + 1
    FROM recursive_trips rt
    JOIN trip_details td 
        ON rt.trip_date = td.trip_date 
        AND rt.current_dest = td.source
    WHERE td.destination != 'D' -- 未到达终点D时继续递归
)
-- 筛选出最终到达D的行程记录
SELECT 
    trip_date,
    start_source AS source,
    current_dest AS destination,
    travel_time AS total_travel_time,
    distance AS total_distance
FROM recursive_trips
WHERE current_dest = 'D'
ORDER BY trip_date;

方法2：多表自连接（适用于固定3段行程） #

如果确定每次都是3段行程（A→B、B→C、C→D），可以直接通过三次自连接实现：

SELECT 
    t1.trip_date,
    t1.source AS source,
    t3.destination AS destination,
    t1.travel_time + t2.travel_time + t3.travel_time AS total_travel_time,
    t1.distance + t2.distance + t3.distance AS total_distance
FROM trip_details t1
JOIN trip_details t2 
    ON t1.trip_date = t2.trip_date 
    AND t1.destination = t2.source
JOIN trip_details t3 
    ON t2.trip_date = t3.trip_date 
    AND t2.destination = t3.source
WHERE t1.source = 'A' 
    AND t3.destination = 'D';

关键说明 #

• Concatenate仅用于字符串拼接，无法处理基于行程关联关系的聚合合并，因此需要用自连接或递归CTE来串联行程链。
• 如果同日期存在多组独立的A→D行程，建议表中增加trip_id这类唯一标识字段，在连接条件中加入该字段，避免不同行程的数据混淆。

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