如何基于两表字段值合并行程数据行并汇总Distance与Time？

表结构假设 #

先明确两张表的结构（基于问题描述推导）：

TripDetails 表 #

TravelPlan 表 #

解决方案 #

下面提供两种可行的SQL实现方式，根据你的实际表结构选择：

方式1：基于起止点关联的汇总 #

适合已知行程是连续且无分支的场景：

-- 先获取目标车辆的计划起止点
WITH TargetPlan AS (
    SELECT StartPoint, EndPoint
    FROM TravelPlan
    WHERE CarID = 1
)
SELECT
    td.CarID,
    tp.StartPoint AS Source,
    tp.EndPoint AS Destination,
    SUM(td.Distance) AS TotalDistance,
    SUM(td.Time) AS TotalTime
FROM TripDetails td
JOIN TargetPlan tp ON td.CarID = 1 -- 关联目标车辆的计划
WHERE 
    -- 筛选属于该连续行程链的记录：要么是起点，要么前一段行程的终点是当前起点
    (td.Source = tp.StartPoint OR EXISTS (
        SELECT 1 FROM TripDetails prev 
        WHERE prev.CarID = td.CarID AND prev.Destination = td.Source
    ))
    -- 同时要么是终点，要么后一段行程的起点是当前终点
    AND (td.Destination = tp.EndPoint OR EXISTS (
        SELECT 1 FROM TripDetails next 
        WHERE next.CarID = td.CarID AND next.Source = td.Destination
    ))
GROUP BY td.CarID, tp.StartPoint, tp.EndPoint;

方式2：基于行程顺序的分组汇总 #

如果你的TripDetails表有行程顺序字段（比如TripOrder，标记行程的先后顺序），可以用这种更精准的方式：

WITH TargetPlan AS (
    SELECT StartPoint, EndPoint
    FROM TravelPlan
    WHERE CarID = 1
),
TripGroups AS (
    SELECT
        *,
        -- 标记从计划起点开始的连续行程组
        SUM(CASE WHEN Source = tp.StartPoint THEN 1 ELSE 0 END)
        OVER (PARTITION BY CarID ORDER BY TripOrder ROWS UNBOUNDED PRECEDING) AS GroupID
    FROM TripDetails td
    CROSS JOIN TargetPlan tp
    WHERE td.CarID = 1
)
SELECT
    CarID,
    (SELECT StartPoint FROM TargetPlan) AS Source,
    (SELECT EndPoint FROM TargetPlan) AS Destination,
    SUM(Distance) AS TotalDistance,
    SUM(Time) AS TotalTime
FROM TripGroups
WHERE GroupID = 1 -- 只取从计划起点开始的连续组
  AND Destination = (SELECT EndPoint FROM TargetPlan)
GROUP BY CarID;

执行结果 #

两种方式都会得到你期望的合并结果：

关键思路说明 #

1. 先从TravelPlan表中定位目标CarID的计划起止点，避免硬编码
2. 筛选TripDetails中属于该连续行程链的记录：确保每一段行程都和前后段衔接，最终连接计划的起点和终点
3. 对筛选后的记录分组，汇总Distance和Time的总和

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