如何在SQL中将员工支出关联至最近的预算负责人

要解决从经理链字符串中匹配首个存在于t_budget的预算负责人ID，核心是拆分ID路径字符串，逐个检查每个ID是否在预算负责人列表中，然后取第一个匹配的结果。以下是几种主流SQL方言的实现方式：

通用思路 #

• 将ID_Path_Up按/拆分，得到单个ID的列表，同时保留每个ID在原路径中的顺序（确保取最近的负责人）。
• 将拆分后的ID与t_budget表关联，筛选出存在的ID。
• 对每个员工的支出记录，取筛选结果中顺序最靠前的ID作为匹配的预算负责人。

MySQL 实现示例 #

假设我们有t_spend表（包含employee_id、amount、ID_Path_Up字段）和t_budget表（包含budget_owner字段）：

SELECT
    s.employee_id,
    s.amount,
    s.ID_Path_Up,
    -- 取第一个匹配的预算负责人ID
    SUBSTRING_INDEX(
        SUBSTRING_INDEX(
            s.ID_Path_Up,
            '/',
            b.match_pos
        ),
        '/',
        -1
    ) AS matched_budget_owner
FROM t_spend s
-- 生成1到N的数字序列，覆盖可能的ID数量（这里假设最多10层经理链）
JOIN (
    SELECT 1 AS match_pos UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL
    SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL
    SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9 UNION ALL SELECT 10
) pos
-- 检查当前位置的ID是否在预算负责人列表中
ON EXISTS (
    SELECT 1 FROM t_budget
    WHERE budget_owner = SUBSTRING_INDEX(SUBSTRING_INDEX(s.ID_Path_Up, '/', pos.match_pos), '/', -1)
)
-- 按员工分组，取最小的匹配位置（即路径中最靠前的ID）
GROUP BY s.employee_id, s.amount, s.ID_Path_Up
HAVING match_pos = MIN(match_pos);

PostgreSQL 实现示例 #

PostgreSQL支持string_to_array和unnest with ordinality，更方便拆分带顺序的字符串：

WITH split_paths AS (
    SELECT
        s.employee_id,
        s.amount,
        s.ID_Path_Up,
        -- 拆分路径为ID和对应的位置
        unnest(string_to_array(s.ID_Path_Up, '/')) AS owner_id,
        ordinality AS path_pos
    FROM t_spend s
)
SELECT
    sp.employee_id,
    sp.amount,
    sp.ID_Path_Up,
    sp.owner_id AS matched_budget_owner
FROM split_paths sp
JOIN t_budget b ON sp.owner_id = b.budget_owner
-- 对每个员工，取路径位置最小的匹配ID
QUALIFY ROW_NUMBER() OVER (
    PARTITION BY sp.employee_id, sp.amount
    ORDER BY sp.path_pos ASC
) = 1;

SQL Server 实现示例 #

利用STRING_SPLIT的ordinal参数（SQL Server 2022+支持）：

WITH split_paths AS (
    SELECT
        s.employee_id,
        s.amount,
        s.ID_Path_Up,
        value AS owner_id,
        ordinal AS path_pos
    FROM t_spend s
    CROSS APPLY STRING_SPLIT(s.ID_Path_Up, '/', 1) -- 1表示返回序号
)
SELECT
    sp.employee_id,
    sp.amount,
    sp.ID_Path_Up,
    sp.owner_id AS matched_budget_owner
FROM split_paths sp
INNER JOIN t_budget b ON sp.owner_id = b.budget_owner
WHERE EXISTS (
    SELECT 1 FROM split_paths sp2
    WHERE sp2.employee_id = sp.employee_id
    AND sp2.path_pos < sp.path_pos
    AND EXISTS (SELECT 1 FROM t_budget b2 WHERE b2.budget_owner = sp2.owner_id)
) = 0; -- 确保是第一个匹配的ID

关键说明 #

• 所有方案都避免了循环，通过字符串拆分+关联筛选+取最小位置实现需求。
• 需要根据实际使用的SQL方言选择对应方案，同时注意经理链的最大层数（MySQL中需要调整数字序列的数量）。

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