如何将两条SQL记录合并为一条？保留首个起始位置与最终结束位置

现有如下SQL数据表格：

需要将同一ID的多条记录合并为一条，保留该ID最早StartTime对应的StartLocation，以及最晚EndTime对应的EndLocation，合并后预期结果如下：

解决方案 #

以下是两种适配不同数据库环境的实现方式：

方式1：聚合函数+子查询（兼容多数数据库） #

SELECT
    t.ID,
    MIN(t.StartTime) AS StartTime,
    (SELECT StartLocation FROM your_table WHERE ID = t.ID AND StartTime = MIN(t.StartTime)) AS StartLocation,
    MAX(t.EndTime) AS EndTime,
    (SELECT EndLocation FROM your_table WHERE ID = t.ID AND EndTime = MAX(t.EndTime)) AS EndLocation
FROM your_table t
GROUP BY t.ID;

方式2：窗口函数（适用于MySQL 8.0+、PostgreSQL、SQL Server等） #

WITH ranked_records AS (
    SELECT
        *,
        ROW_NUMBER() OVER (PARTITION BY ID ORDER BY StartTime ASC) AS rn_start,
        ROW_NUMBER() OVER (PARTITION BY ID ORDER BY EndTime DESC) AS rn_end
    FROM your_table
)
SELECT
    ID,
    MAX(CASE WHEN rn_start = 1 THEN StartTime END) AS StartTime,
    MAX(CASE WHEN rn_start = 1 THEN StartLocation END) AS StartLocation,
    MAX(CASE WHEN rn_end = 1 THEN EndTime END) AS EndTime,
    MAX(CASE WHEN rn_end = 1 THEN EndLocation END) AS EndLocation
FROM ranked_records
GROUP BY ID;

注：请将代码中的your_table替换为实际表名。

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