SQLite3.12.2如何基于首表非唯一ID关联两表获取匹配的最早B表记录

问题原因 #

你当前编写的SQL仅做了基础关联筛选，会返回所有SerialID相等、且B表时间戳大于A表时间戳的关联记录。比如SerialID=3的A表时间为1的记录，会关联到B表时间为3、16的两条符合条件的记录，最终返回多余的重复行，和你要求的「仅匹配第一条（时间最小）符合条件的B记录」的需求不符。

正确实现方案 #

这里提供两种兼容性较好的写法：

写法1：窗口函数法（支持MySQL8.0+/PostgreSQL/SQL Server等支持窗口函数的数据库）

SELECT 
    A.timestamp AS `A.Timestamp`,
    B.timestamp AS `B.Timestamp`,
    A.SerialID AS `Serial ID`,
    A.Category,
    B.Sound
FROM A
LEFT JOIN (
    SELECT 
        A.SerialID,
        A.timestamp AS A_ts,
        MIN(B.timestamp) AS min_B_ts
    FROM A
    JOIN B ON A.SerialID = B.SerialID AND B.timestamp > A.timestamp
    GROUP BY A.SerialID, A.timestamp
) AS t ON A.SerialID = t.SerialID AND A.timestamp = t.A_ts
JOIN B ON t.SerialID = B.SerialID AND t.min_B_ts = B.timestamp
ORDER BY B.timestamp

写法2：关联子查询法（兼容低版本数据库）

SELECT 
    A.timestamp AS `A.Timestamp`,
    (SELECT MIN(B.timestamp) FROM B WHERE B.SerialID = A.SerialID AND B.timestamp > A.timestamp) AS `B.Timestamp`,
    A.SerialID AS `Serial ID`,
    A.Category,
    (SELECT B.Sound FROM B WHERE B.SerialID = A.SerialID AND B.timestamp = (SELECT MIN(B.timestamp) FROM B WHERE B.SerialID = A.SerialID AND B.timestamp > A.timestamp)) AS Sound
FROM A
ORDER BY `B.Timestamp`

两种写法执行后都会得到预期结果：

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