如何使用SQL合并多行数据？基于包裹转运记录场景

假设你的转运记录表名为package_transit，包含以下字段：

• departure_time DATETIME：出发时间
• departure_location VARCHAR：出发地
• arrival_time DATETIME：到达时间
• arrival_location VARCHAR：目的地
  （如果有区分不同包裹的package_id字段，记得在分组时带上）

场景1：拼接完整转运路径 #

要是想把整个转运链路拼成类似「美国 → 中国 → 阿根廷 → 沙特阿拉伯」的格式，不同数据库用的函数不一样，给你举几个常用的：

MySQL 8.0+/MariaDB #

用GROUP_CONCAT按时间排序拼接，要是嫌中间地点重复（比如中国既是上一站的终点又是下一站的起点），可以用子查询先提取所有节点再拼接：

-- 直接拼接每段行程
SELECT 
  GROUP_CONCAT(
    CONCAT(departure_location, ' → ', arrival_location) 
    ORDER BY departure_time SEPARATOR ' → '
  ) AS full_transit_route,
  MIN(departure_time) AS total_start_time,
  MAX(arrival_time) AS total_end_time,
  TIMESTAMPDIFF(HOUR, MIN(departure_time), MAX(arrival_time)) AS total_hours
FROM package_transit
GROUP BY package_id;

-- 优化版：避免中间地点重复
SELECT 
  GROUP_CONCAT(location ORDER BY time_point, seq SEPARATOR ' → ') AS full_transit_route,
  MIN(time_point) AS total_start_time,
  MAX(time_point) AS total_end_time,
  TIMESTAMPDIFF(HOUR, MIN(time_point), MAX(time_point)) AS total_hours
FROM (
  -- 提取所有出发地和时间
  SELECT 
    departure_location AS location,
    departure_time AS time_point,
    1 AS seq
  FROM package_transit
  UNION ALL
  -- 提取所有目的地和时间
  SELECT 
    arrival_location AS location,
    arrival_time AS time_point,
    2 AS seq
  FROM package_transit
) AS location_nodes
GROUP BY package_id;

PostgreSQL #

用STRING_AGG函数实现：

SELECT 
  STRING_AGG(
    CONCAT(departure_location, ' → ', arrival_location), 
    ' → ' ORDER BY departure_time
  ) AS full_transit_route,
  MIN(departure_time) AS total_start_time,
  MAX(arrival_time) AS total_end_time,
  EXTRACT(EPOCH FROM (MAX(arrival_time) - MIN(departure_time)))/3600 AS total_hours
FROM package_transit
GROUP BY package_id;

场景2：生成总行程摘要 #

要是只需要核心信息（始发地、最终目的地、总耗时这些），用这条简单的SQL就行：

SELECT 
  MIN(departure_location) AS origin,
  MAX(arrival_location) AS destination,
  MIN(departure_time) AS start_time,
  MAX(arrival_time) AS end_time,
  TIMESTAMPDIFF(HOUR, MIN(departure_time), MAX(arrival_time)) AS total_duration_hours
FROM package_transit
GROUP BY package_id;

执行后会得到类似这样的结果：

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