按contract分区取最小距离eqp且避免设备重复占用的SQL查询问题

你原来用RANK()的写法是独立计算每个合约的最近设备，没有实现「已分配设备不能被后续合约复用」的全局独占约束，属于带资源占用的贪心匹配场景。
匹配规则明确为：

• 按contract数值从小到大的顺序优先处理更早的合约
• 每个合约仅能选择当前未被分配的设备中距离最小的
• 每个设备仅可被分配一次

以下方案适配所有支持递归CTE的SQL引擎（MySQL 8.0+、PostgreSQL、Spark SQL、Hive等）：

WITH 
-- 步骤1：给每个合约下的设备按距离升序排序，同合约内距离越小序号越小
contract_eqp_rn AS (
    SELECT 
        id,
        contract,
        eqp,
        distance,
        ROW_NUMBER() OVER (PARTITION BY contract ORDER BY distance ASC) AS rn
    FROM t1
),
-- 步骤2：给所有合约按顺序编处理序号，保证先处理编号更小的合约
contract_order AS (
    SELECT 
        contract,
        ROW_NUMBER() OVER (ORDER BY contract ASC) AS contract_seq
    FROM (SELECT DISTINCT contract FROM t1) t
),
-- 步骤3：递归逐个处理合约，每次分配未被占用的最近设备
recursive_allocation AS (
    -- 初始化：处理第一个合约，直接取距离最小的设备
    SELECT 
        cer.id,
        cer.contract,
        cer.eqp,
        cer.distance,
        co.contract_seq,
        CAST(cer.eqp AS CHAR(1000)) AS used_eqps
    FROM contract_eqp_rn cer
    JOIN contract_order co ON cer.contract = co.contract
    WHERE co.contract_seq = 1 AND cer.rn = 1
    UNION ALL
    -- 递归处理后续合约
    SELECT 
        cer.id,
        cer.contract,
        cer.eqp,
        cer.distance,
        co.contract_seq,
        CONCAT(ra.used_eqps, ',', cer.eqp) AS used_eqps
    FROM recursive_allocation ra
    JOIN contract_order co ON co.contract_seq = ra.contract_seq + 1
    JOIN contract_eqp_rn cer ON cer.contract = co.contract
    WHERE FIND_IN_SET(cer.eqp, ra.used_eqps) = 0 
    AND cer.rn = (
        SELECT MIN(rn) 
        FROM contract_eqp_rn cer2 
        WHERE cer2.contract = co.contract 
        AND FIND_IN_SET(cer2.eqp, ra.used_eqps) = 0
    )
)
-- 输出最终分配结果
SELECT id, contract, eqp, distance 
FROM recursive_allocation
ORDER BY contract;

如果你的SQL引擎不支持FIND_IN_SET函数，替换为对应字符串包含/数组包含判断即可：

• PostgreSQL：替换为POSITION(cer.eqp IN ra.used_eqps) = 0
• Hive/Spark SQL：替换为array_contains(split(ra.used_eqps, ','), cer.eqp) = false

你提供的样例数据执行上述SQL后，返回结果就是预期的id为1、5的两条记录，对应合约123分配eqp A，合约124分配eqp B。

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