如何基于两个SQLite表创建含最近匹配值的视图

场景与需求 #

Table A的ColA列存储一组数值，Table B的ColB列存储另一组数值。需要创建一个SQLite视图，视图中包含Table A的ColA数值，以及从Table B的ColB中匹配到的最近数值（即与ColA差值绝对值最小的ColB值）。

示例数据 #

---Table A (ColA)---|---Table B (ColB)---
13                 | 22
25                 | 49
22                 | 11
33                 | 36

期望视图结果 #

---View (ColA)---|---View (ColB)---
13               | 11
25               | 22
22               | 22
33               | 36

解决方案 #

可以通过子查询计算每个ColA与ColB的差值绝对值，筛选出差值最小的ColB值，最终创建视图：

CREATE VIEW MatchingClosestValues AS
SELECT 
    a.ColA,
    (SELECT b.ColB
     FROM TableB b
     ORDER BY ABS(a.ColA - b.ColB) ASC, b.ColB ASC
     LIMIT 1) AS ColB
FROM TableA a;

说明 #

1. 核心逻辑是对Table A中的每个ColA值，关联Table B的所有ColB值，计算两者的差值绝对值ABS(a.ColA - b.ColB)。
2. 通过ORDER BY先按差值绝对值升序排序，确保差值最小的ColB排在最前面；如果存在多个ColB与ColA差值相同（比如ColA=15，ColB有14和16），可通过第二个排序条件（示例中是b.ColB ASC）决定取哪一个，按需调整即可。
3. LIMIT 1确保只取到最近的那个ColB值。

内容的提问来源于stack exchange，提问作者J.Doe