如何在SQL Server中通过循环查询获取指定保单的最终关联保单号

方法1：使用递归CTE（隐式循环逻辑） #

大部分现代数据库（如SQL Server、PostgreSQL、MySQL 8.0+）支持递归CTE，这是处理层级追溯问题的常用方式，内部通过循环遍历层级关系：

WITH RECURSIVE policy_hierarchy AS (
    -- 初始查询：获取目标Policy_No的初始记录
    SELECT 
        Policy_No, 
        Old_Policy_No,
        Old_Policy_No AS final_old_policy
    FROM tbl_account
    WHERE Policy_No IN ('A', 'C', 'E', 'G')
    
    UNION ALL
    
    -- 递归查询：逐层追溯直到Old_Policy_No为Null
    SELECT 
        ph.Policy_No, 
        ta.Old_Policy_No,
        ta.Old_Policy_No AS final_old_policy
    FROM policy_hierarchy ph
    JOIN tbl_account ta ON ph.final_old_policy = ta.Policy_No
    WHERE ta.Old_Policy_No IS NOT NULL
)
-- 取每个Policy_No的最后一层记录（即Old_Policy_No为Null前的有效值）
SELECT 
    Policy_No, 
    final_old_policy AS Old_Policy_No
FROM policy_hierarchy
WHERE final_old_policy IN (
    SELECT Policy_No FROM tbl_account WHERE Old_Policy_No IS NULL
)
ORDER BY Policy_No;

方法2：显式循环的存储过程（以MySQL为例） #

如果要求必须写显式循环的代码，可以用存储过程实现，通过循环逐个追溯每个目标Policy_No的最终上级：

DELIMITER //

CREATE PROCEDURE GetFinalOldPolicy()
BEGIN
    -- 创建临时表存储结果
    CREATE TEMPORARY TABLE IF NOT EXISTS temp_result (
        Policy_No VARCHAR(10),
        Old_Policy_No VARCHAR(10)
    );
    
    -- 定义变量
    DECLARE target_policy VARCHAR(10);
    DECLARE current_old_policy VARCHAR(10);
    DECLARE done INT DEFAULT 0;
    
    -- 游标遍历目标Policy_No
    DECLARE policy_cursor CURSOR FOR 
        SELECT Policy_No FROM tbl_account WHERE Policy_No IN ('A', 'C', 'E', 'G');
    
    -- 游标结束处理
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
    
    -- 打开游标
    OPEN policy_cursor;
    
    -- 循环处理每个目标Policy
    policy_loop: LOOP
        FETCH policy_cursor INTO target_policy;
        IF done = 1 THEN
            LEAVE policy_loop;
        END IF;
        
        -- 初始化当前旧保单号
        SELECT Old_Policy_No INTO current_old_policy FROM tbl_account WHERE Policy_No = target_policy;
        
        -- 循环追溯直到Old_Policy_No为Null
        WHILE current_old_policy IS NOT NULL DO
            SET @next_old_policy = (SELECT Old_Policy_No FROM tbl_account WHERE Policy_No = current_old_policy);
            IF @next_old_policy IS NULL THEN
                LEAVE;
            END IF;
            SET current_old_policy = @next_old_policy;
        END WHILE;
        
        -- 将结果插入临时表
        INSERT INTO temp_result (Policy_No, Old_Policy_No) VALUES (target_policy, current_old_policy);
    END LOOP policy_loop;
    
    -- 关闭游标
    CLOSE policy_cursor;
    
    -- 查询结果
    SELECT * FROM temp_result ORDER BY Policy_No;
    
    -- 删除临时表
    DROP TEMPORARY TABLE IF EXISTS temp_result;
END //

DELIMITER ;

-- 调用存储过程
CALL GetFinalOldPolicy();

说明 #

• 递归CTE写法简洁高效，适合支持该特性的数据库；
• 显式循环的存储过程兼容性更好，适合不支持递归CTE的旧版本数据库；
• 两种方法都能输出你需要的目标结果，可根据使用的数据库类型选择。

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