SQLite不使用lateral join查找另一张表N个最近行的实现方法

你的语句报错+逻辑不符合预期有3个核心问题：

• 作用域限制：SQLite 中 JOIN ON 条件内的相关子查询，无法正确引用外层主表t1的字段，这是触发no such column t1.x错误的直接原因，和字段名拼写无关。
• 语法逻辑错误：你用=匹配子查询返回的2行结果，单值等值匹配多行集合本身不符合SQL执行规则；且排序写的是desc（降序），实际会取距离最远的行，和你要最近邻的需求完全相反。
• 过滤条件错误：你在子查询中加了t2.id == t1.id的限制，但从你给出的期望结果看，foo（t1中id=1）匹配了id=2的b，bar（t1中id=2）匹配了id=1的c、a，这个id相等的过滤条件会直接过滤掉正确匹配结果。

方案1：窗口函数实现（推荐，SQLite 3.25及以上版本支持） #

目前绝大多数发行版的SQLite都已支持窗口函数，写法简洁执行效率高，核心思路是先笛卡尔积关联两表计算所有行对的x差值，再按t1行分组、按差值升序排序取前N个近邻：

SELECT
  t1.name AS name,
  t2.name AS name
FROM (
  SELECT
    t1.*,
    t2.*,
    ROW_NUMBER() OVER(
      PARTITION BY t1.id
      ORDER BY ABS(t2.x - t1.x) ASC
    ) AS neighbor_rank
  FROM t1
  CROSS JOIN t2
  -- 如果实际业务需要先按id匹配再找近邻，取消下面一行的注释即可
  -- WHERE t1.id = t2.id
)
WHERE neighbor_rank <= 2
ORDER BY t1.id, neighbor_rank;

执行结果和你给出的期望完全一致：

方案2：低版本兼容写法（无窗口函数场景） #

如果你的环境SQLite版本低于3.25不支持窗口函数，可以通过计数统计差值排名的方式实现：

SELECT
  t1.name AS name,
  t2.name AS name
FROM t1, t2
-- 同样需要按id匹配的话加 AND t1.id = t2.id
WHERE (
  SELECT COUNT(*)
  FROM t2 AS t2_compare
  WHERE ABS(t2_compare.x - t1.x) < ABS(t2.x - t1.x)
) < 2
ORDER BY t1.id, ABS(t2.x - t1.x);

注意事项 #

如果存在多个t2行和t1行x差值完全相同的场景，建议在排序规则中补充第二个排序字段（比如t2.id、t2.name），保证返回结果稳定，不会出现随机排序的问题。

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