如何在SQLite中按最匹配的非精确整数键连接两张表

我有两张表，用于连接的键并非完全相同的整数值，需要基于这些键按**差值最小（最匹配）**的方式进行表连接。以下是演示数据：

CREATE TABLE t1(
  "size" TEXT,
  filename TEXT
);

-- ----------------------------
-- Records of t1
-- ----------------------------
INSERT INTO "main"."t1" VALUES (1162775, 'file1');
INSERT INTO "main"."t1" VALUES (1145387, 'file1');
INSERT INTO "main"."t1" VALUES (1388613, 'file1');
INSERT INTO "main"."t1" VALUES (1306413, 'file1');
INSERT INTO "main"."t1" VALUES (1792882, 'file1');
INSERT INTO "main"."t1" VALUES (1798382, 'file1');
INSERT INTO "main"."t1" VALUES (878147,  'file1');
INSERT INTO "main"."t1" VALUES (2614277, 'file1');
INSERT INTO "main"."t1" VALUES (838639,  'file1');
INSERT INTO "main"."t1" VALUES (3053906, 'file1');
INSERT INTO "main"."t1" VALUES (1019579, 'file1');
INSERT INTO "main"."t1" VALUES (3234508, 'file1');
INSERT INTO "main"."t1" VALUES (2442681, 'file1');

CREATE TABLE t2(
   "info" Text,
   readysize TEXT
);

-- ----------------------------
-- Records of t2
-- ----------------------------
INSERT INTO "main"."t2" VALUES ('info1', 1162780);
INSERT INTO "main"."t2" VALUES ('info1', 1145392);
INSERT INTO "main"."t2" VALUES ('info1', 1388620);
INSERT INTO "main"."t2" VALUES ('info1', 1306420);
INSERT INTO "main"."t2" VALUES ('info1', 1792888);
INSERT INTO "main"."t2" VALUES ('info1', 1798388);
INSERT INTO "main"."t2" VALUES ('info1', 878152 );
INSERT INTO "main"."t2" VALUES ('info1', 2614284);
INSERT INTO "main"."t2" VALUES ('info1', 838644 );
INSERT INTO "main"."t2" VALUES ('info1', 3053912);
INSERT INTO "main"."t2" VALUES ('info1', 1019584);
INSERT INTO "main"."t2" VALUES ('info1', 3234516);
INSERT INTO "main"."t2" VALUES ('info1', 2442688);

期望得到的最优匹配关系如下：

• 1162775 -> 1162780
• 1145387 -> 1145392
• 1388613 -> 1388620
• 1306413 -> 1306420
• 1792882 -> 1792888
• 1798382 -> 1798388
• 878147 -> 878152
• 2614277 -> 2614284
• 838639 -> 838644
• 3053906 -> 3053912
• 1019579 -> 1019584
• 3234508 -> 3234516
• 2442681 -> 2442688

我希望在SELECT * FROM t1 JOIN t2 ON t1.size (match best fit) t2.readysize的ON子句中实现上述匹配。另外我尝试了以下SQL语句，请问是否合理？

SELECT * FROM t1 JOIN t2 ON ABS(t1.size - t2.readysize) < 10 AND ((t1.size >= t2.readysize) OR (t1.size <= t2.readysize))

我定义了最大差值为10，并通过AND条件限制范围。

你的SQL语句分析 #

这条语句不合理，存在两个问题：

1. ((t1.size >= t2.readysize) OR (t1.size <= t2.readysize))是完全冗余的条件——任何两个数值必然满足其中一个，相当于没加限制，完全可以删掉。
2. 该语句只能过滤出差值小于10的记录，但无法保证每个t1记录只匹配差值最小的那个t2记录。如果存在多个t2记录与某个t1记录的差值都小于10，会返回多条匹配结果，不符合"最优匹配"的需求。

正确的实现方式 #

要实现按最小差值匹配，需要先计算每个t1记录与所有t2记录的差值，再为每个t1记录筛选出差值最小的那条t2记录。可以用窗口函数ROW_NUMBER()来实现：

SELECT t1.size, t1.filename, t2.info, t2.readysize
FROM (
    SELECT 
        t1.*, 
        t2.*,
        ABS(CAST(t1.size AS INTEGER) - CAST(t2.readysize AS INTEGER)) AS diff,
        ROW_NUMBER() OVER (
            PARTITION BY t1.size 
            ORDER BY ABS(CAST(t1.size AS INTEGER) - CAST(t2.readysize AS INTEGER))
        ) AS rn
    FROM t1
    JOIN t2 ON ABS(CAST(t1.size AS INTEGER) - CAST(t2.readysize AS INTEGER)) < 10 -- 可选：提前过滤过大差值，提升效率
) AS temp
WHERE rn = 1;

说明：

• 因为t1.size和t2.readysize是TEXT类型，必须用CAST转成整数再计算差值，否则会按字符串规则运算，结果错误。
• PARTITION BY t1.size表示按t1的每个size分组，ORDER BY diff按差值从小到大排序，ROW_NUMBER()会给每组内的记录编号，rn=1就取每组中差值最小的那条。
• 内层的JOIN条件ABS(...) <10是可选的，用来提前排除差值过大的记录，减少计算量；如果不确定最大差值，也可以去掉这个条件，直接计算所有可能的匹配。

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