SQL关联查询：基于外部字段匹配对应出入时间的方案问询

核心结论 #

可以在JOIN中引用外部表字段，且有多种比子查询性能更优的实现方案，关键是利用数据库关联查询特性+合理索引优化。

方案1：LATERAL JOIN（适配PostgreSQL/MySQL 8.0+/SQLite 3.33+） #

这是性能最优的方案之一，允许子查询直接引用外部表（TIMESIN）的字段，每个进入记录仅触发一次精准查询：

SELECT 
    t.ID,
    t.TIME_IN,
    m.TIME_STAMP AS TIME_OUT
FROM TIMESIN t
LEFT JOIN LATERAL (
    -- 仅取当前ID下晚于进入时间的第一条离开记录
    SELECT TIME_STAMP
    FROM MOVEMENTTAB
    WHERE ID = t.ID
      AND COMMAND = 2
      AND TIME_STAMP > t.TIME_IN
    ORDER BY TIME_STAMP ASC
    LIMIT 1
) m ON true;

方案2：CROSS/OUTER APPLY（适配SQL Server） #

逻辑与LATERAL一致，语法适配SQL Server环境：

SELECT 
    t.ID,
    t.TIME_IN,
    m.TIME_STAMP AS TIME_OUT
FROM TIMESIN t
OUTER APPLY (
    SELECT TOP 1 TIME_STAMP
    FROM MOVEMENTTAB
    WHERE ID = t.ID
      AND COMMAND = 2
      AND TIME_STAMP > t.TIME_IN
    ORDER BY TIME_STAMP ASC
) m;

方案3：窗口函数通用方案（适配所有支持窗口函数的数据库） #

如果数据库不支持LATERAL/APPLY，可通过窗口函数先排序再筛选目标记录：

WITH joined_ranked AS (
    SELECT 
        t.ID,
        t.TIME_IN,
        m.TIME_STAMP,
        -- 给每个进入记录关联的离开记录按时间排序
        ROW_NUMBER() OVER (PARTITION BY t.ID, t.TIME_IN ORDER BY m.TIME_STAMP ASC) AS rn
    FROM TIMESIN t
    LEFT JOIN MOVEMENTTAB m
        ON m.ID = t.ID
        AND m.COMMAND = 2
        AND m.TIME_STAMP > t.TIME_IN
)
-- 取每个进入记录对应的第一条离开记录
SELECT ID, TIME_IN, TIME_STAMP AS TIME_OUT
FROM joined_ranked
WHERE rn = 1;

性能优化关键 #

必须给MOVEMENTTAB创建联合索引，避免全表扫描：

CREATE INDEX idx_movement_id_cmd_ts ON MOVEMENTTAB(ID, COMMAND, TIME_STAMP);

该索引能让数据库快速定位到指定ID、离开动作（COMMAND=2）且时间晚于进入时间的记录，大幅提升查询效率。

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