SQL进阶技巧：如何根据座位距离查找员工？| 员工座位安排问题

会飞的一十六 2025-01-06

点击上方【蓝色】字体关注我们

01 场景描述

假设座位表 employees 里增加了坐标信息（例如 x_coordinate 和 y_coordinate 字段表示座位在平面上的坐标位置），现在要找出距离某个特定员工（给定其 employee_id）距离在一定范围内的其他员工。

02 数据准备

-- 创建表
CREATE TABLE employees (
    employee_id INT,
    x_coordinate INT,
    y_coordinate INT
);


-- 插入示例数据
INSERT INTO employees VALUES
(1, 1, 1),
(2, 1, 3),
(3, 2, 2),
(4, 3, 1),
(5, 3, 3),
(6, 4, 2);

03 问题分析

步骤一：使用分析函数计算每个员工与其他员工的坐标差值

我们要计算每个员工与其他员工在 x
轴和 y
轴方向上的坐标差值，这里使用分析函数来实现，方便后续计算距离。

-- 使用分析函数计算坐标差值
SELECT 
    e1.employee_id AS employee_id_1,
    e2.employee_id AS employee_id_2,
    e1.x_coordinate - e2.x_coordinate AS x_diff,
    e1.y_coordinate - e2.y_coordinate AS y_diff
FROM employees e1
JOIN employees e2 ON e1.employee_id!= e2.employee_id
ORDER BY e1.employee_id, e2.employee_id;

在这个步骤中，通过自连接 employees 表（e1 和 e2 分别代表同一张表的不同别名，用于关联比较不同员工之间的情况），并要求 employee_id 不相等（避免员工与自己比较），然后使用减法运算计算出两个员工之间在 x 轴和 y 轴方向上的坐标差值，为下一步计算距离做准备。按照 employee_id 排序只是为了让结果展示更有条理，方便查看。

步骤二：使用分析函数基于坐标差值计算距离

假设我们要计算平面直角坐标系下的欧几里得距离，使用公式

在 SQL 中可以使用 POWER
函数（计算幂次方，相当于平方操作）和 SQRT
函数（计算平方根）来实现距离计算，同时结合分析函数来对每一对员工的距离进行计算。

-- 基于坐标差值计算距离
SELECT 
    employee_id_1,
    employee_id_2,
    SQRT(POWER(x_diff, 2) + POWER(y_diff, 2)) AS distance
FROM (
    SELECT 
        e1.employee_id AS employee_id_1,
        e2.employee_id AS employee_id_2,
        e1.x_coordinate - e2.x_coordinate AS x_diff,
        e1.y_coordinate - e2.y_coordinate AS y_diff
    FROM employees e1
    JOIN employees e2 ON e1.employee_id!= e2.employee_id
) subquery;

在这个步骤中，先在子查询里复用了第一步计算坐标差值的结果，然后在外层查询中，根据欧几里得距离公式，使用 POWER 函数对坐标差值分别求平方后相加，再用 SQRT 函数计算平方根，得到每一对员工之间的距离值，这是整个距离计算的核心步骤。

步骤三：根据设定的距离范围筛选出符合条件的员工

假设我们要找出距离 employee_id
为 1
的员工距离在 3
以内（包含 3
）的其他员工，通过 WHERE
子句来设定距离范围条件进行筛选。

-- 根据距离范围筛选员工
SELECT 
    employee_id_1,
    employee_id_2
FROM (
    SELECT 
        employee_id_1,
        employee_id_2,
        SQRT(POWER(x_diff, 2) + POWER(y_diff, 2)) AS distance
    FROM (
        SELECT 
            e1.employee_id AS employee_id_1,
            e2.employee_id AS employee_id_2,
            e1.x_coordinate - e2.x_coordinate AS x_diff,
            e1.y_coordinate - e2.y_coordinate AS y_diff
        FROM employees e1
        JOIN employees e2 ON e1.employee_id!= e2.employee_id
    ) subquery
) final_subquery
WHERE (employee_id_1 = 1 OR employee_id_2 = 1) AND distance <= 3;

在这一步中，先在子查询里复用了第二步计算距离的结果，然后在外层的 WHERE 子句中，一方面设定了要与 employee_id 为 1 的员工进行距离比较（通过 employee_id_1 = 1 OR employee_id_2 = 1 来实现，因为距离是相互的，所以两个方向都要考虑），另一方面设定了距离小于等于 3 的范围条件，最终筛选出符合要求的员工组合，也就是找出了距离指定员工在设定距离范围内的其他员工。

04 小结

本文利用 SQL 分析了一种根据座位距离查找员工的问题，具体核心解题思路如下：

坐标差值计算思路

自连接实现对比：通过对 employees 表进行自连接（使用不同别名 e1 和 e2 来代表同一张表的不同实例），实现将每个员工与其他员工的数据进行两两对比，从而为后续计算坐标差值做准备。同时，通过限定连接条件 e1.employee_id!= e2.employee_id 排除员工与自己对比的无效情况。

分析函数辅助计算差值：在连接后的查询中，直接使用减法运算计算出每一对员工在 x 轴（x_diff 字段）和 y 轴（y_diff 字段）方向上的坐标差值，这一步为后续计算距离提供了基础数据，并且利用自连接结合简单运算的方式，能系统地获取所有员工两两之间的坐标差异情况，便于后续整体分析。

距离计算思路

运用数学公式转化为 SQL 函数操作：依据平面直角坐标系下的欧几里得距离公式，在 SQL 中巧妙地运用 POWER 函数来实现对坐标差值的平方操作（分别计算 x_diff 和 y_diff 的平方），然后将两个平方值相加，再通过 SQRT 函数对相加后的结果取平方根，从而准确地计算出每一对员工之间的距离值。

借助分析函数批量计算：使用分析函数结合上述的函数操作，能够针对第一步自连接后生成的所有员工两两组合的数据，批量地完成距离计算，无需手动逐个去计算每一对员工的距离，提高了计算效率且保证了计算的全面性，使得每个员工与其他员工之间的距离都能得到呈现，方便后续进行筛选操作。

筛选符合距离范围员工的思路

嵌套查询复用中间结果：采用多层嵌套查询的方式，在最内层的子查询里完成坐标差值的计算，中间层子查询基于坐标差值完成距离的计算，而最外层查询则复用中间层计算出的距离结果以及员工组合信息，通过这样的嵌套结构清晰地组织代码逻辑，使得每一步的中间结果都能被合理利用，也便于理解整个数据的流转和处理过程。

利用 WHERE 子句精准筛选：在最外层查询中，通过 WHERE 子句设定精准的筛选条件。一方面，通过 (employee_id_1 = 1 OR employee_id_2 = 1) 来明确是与指定的 employee_id 为 1 的员工进行距离比较（考虑到距离的相互性，所以两个员工 ID 对应的情况都要包含）；另一方面，使用 distance <= 3 设定距离范围限制，只保留那些距离在 3 以内（包含 3 ）的员工组合，从而最终筛选出符合题目要求的员工信息，实现了根据座位距离查找员工的目标。

总体而言，整个解题思路是围绕着先构建计算基础（坐标差值），再基于基础数据进行核心指标（距离）的计算，最后依据特定条件（距离范围和指定员工）筛选出所需结果，并且充分利用了 SQL 的自连接、分析函数以及嵌套查询等特性来有条理地实现各步骤逻辑，高效准确地解决问题。

往期精彩

SQL进阶技巧：如何查找每个部门里坐在角落位置的员工？| 员工座位安排问题

数势科技智能分析AI Agent 何以在市场中脱颖而出？

解锁SQL无限可能：如何利用HiveSQL实现0-1背包问题？

数仓建模：一种动态字段表模型设计方法与应用

SQL进阶技巧：如何根据工业制程参数计算良品率？

数据科学与SQL：如何利用SQL计算线性回归系数？