如何通过SQL筛选John的房产及周边符合坐标范围的地址？

数据表格 #

需求描述 #

我有一个包含house_owner、coordinate x、coordinate y字段的数据库表，上面是该表的简化视图。我需要筛选出所有owner为john的地址，以及john地址周边的地址——也就是要选中前3行，因为Paul的住宅紧邻John的第一处住宅（第二行）。我知道自己写的SQL代码有问题，子查询返回结果顺序混乱导致无法正常运行，但不知道正确写法。我把“附近”定义为坐标值上下浮动0.002，后来还在数据库里新增了coor x min、coor x max、coor y min、coor y max列用来定义区域范围，但不确定这些列能不能派上用场。

错误SQL代码 #

SELECT * FROM houses_table WHERE ((CAST(coor_y AS DECIMAL(10,6)) BETWEEN 
       (SELECT CAST(coor_y AS DECIMAL(10, 6)) - 0.002 
        FROM houses_table  WHERE house_owner IN ('john')) 
       AND (SELECT CAST(coor_y AS DECIMAL(10, 6)) + 0.002 
            FROM houses_table  
            WHERE house_owner IN ('john'))) AND (CAST(coor_x AS DECIMAL(10,6)) BETWEEN
       (SELECT CAST(coor_x AS DECIMAL(10, 6)) - 0.002 
        FROM houses_table 
        WHERE house_owner IN ('john'))
   AND (SELECT CAST(coor_x AS DECIMAL(10,6)) + 0.002 
        FROM houses_table 
        WHERE house_owner IN ('john'))));

错误原因 #

你的SQL里的子查询会返回多条结果（因为john有两个地址），但BETWEEN需要单个上下限值，这直接导致语法错误；就算没报错，逻辑也完全不对——数据库不知道该用john的哪个地址的坐标来计算范围。

正确解决方案 #

方法1：JOIN关联筛选 #

直接把john的地址作为基准，和原表关联筛选符合范围的记录：

SELECT DISTINCT h.*
FROM houses_table h
LEFT JOIN houses_table john_houses 
  ON john_houses.house_owner = 'john'
  AND CAST(REPLACE(h.coor_x, ',', '.') AS DECIMAL(10,6)) 
    BETWEEN CAST(REPLACE(john_houses.coor_x, ',', '.') AS DECIMAL(10,6)) - 0.002
    AND CAST(REPLACE(john_houses.coor_x, ',', '.') AS DECIMAL(10,6)) + 0.002
  AND CAST(REPLACE(h.coor_y, ',', '.') AS DECIMAL(10,6)) 
    BETWEEN CAST(REPLACE(john_houses.coor_y, ',', '.') AS DECIMAL(10,6)) - 0.002
    AND CAST(REPLACE(john_houses.coor_y, ',', '.') AS DECIMAL(10,6)) + 0.002
WHERE h.house_owner = 'john' OR john_houses.house_owner IS NOT NULL;

• 用REPLACE把坐标里的逗号换成小数点，确保DECIMAL转换正常（数据库通常用.做小数分隔符）
• DISTINCT避免同一地址被多次匹配（比如同时靠近john的两个地址）
• 条件包含john自己的地址，以及和john地址坐标差在±0.002内的地址

方法2：EXISTS子查询判断 #

逻辑更简洁，检查当前记录是否是john的，或者是否存在john的地址和它坐标差符合要求：

SELECT *
FROM houses_table h
WHERE h.house_owner = 'john'
   OR EXISTS (
       SELECT 1
       FROM houses_table john_houses
       WHERE john_houses.house_owner = 'john'
         AND ABS(CAST(REPLACE(h.coor_x, ',', '.') AS DECIMAL(10,6)) - CAST(REPLACE(john_houses.coor_x, ',', '.') AS DECIMAL(10,6))) <= 0.002
         AND ABS(CAST(REPLACE(h.coor_y, ',', '.') AS DECIMAL(10,6)) - CAST(REPLACE(john_houses.coor_y, ',', '.') AS DECIMAL(10,6))) <= 0.002
   );

• ABS计算坐标差的绝对值，只要x、y方向差都不超过0.002就算附近
• 无需去重，EXISTS只要匹配一次就返回true，不会重复输出

利用新增区域列优化 #

如果新增的coor x min/max等列是当前地址的范围，可以直接用它们判断区域重叠：

SELECT *
FROM houses_table h
WHERE h.house_owner = 'john'
   OR EXISTS (
       SELECT 1
       FROM houses_table john_houses
       WHERE john_houses.house_owner = 'john'
         -- 检查x坐标范围是否重叠
         AND (CAST(REPLACE(h.coor_x, ',', '.') AS DECIMAL(10,6)) BETWEEN CAST(REPLACE(john_houses.coor_x_min, ',', '.') AS DECIMAL(10,6)) AND CAST(REPLACE(john_houses.coor_x_max, ',', '.') AS DECIMAL(10,6))
              OR CAST(REPLACE(john_houses.coor_x, ',', '.') AS DECIMAL(10,6)) BETWEEN CAST(REPLACE(h.coor_x_min, ',', '.') AS DECIMAL(10,6)) AND CAST(REPLACE(h.coor_x_max, ',', '.') AS DECIMAL(10,6)))
         -- 检查y坐标范围是否重叠
         AND (CAST(REPLACE(h.coor_y, ',', '.') AS DECIMAL(10,6)) BETWEEN CAST(REPLACE(john_houses.coor_y_min, ',', '.') AS DECIMAL(10,6)) AND CAST(REPLACE(john_houses.coor_y_max, ',', '.') AS DECIMAL(10,6))
              OR CAST(REPLACE(john_houses.coor_y, ',', '.') AS DECIMAL(10,6)) BETWEEN CAST(REPLACE(h.coor_y_min, ',', '.') AS DECIMAL(10,6)) AND CAST(REPLACE(h.coor_y_max, ',', '.') AS DECIMAL(10,6)))
   );

• 只要x、y方向的区域有重叠，就算是附近地址
• 复用已计算的范围列，能提升查询效率

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