SQL Server中实现种群类型递归转换的方案咨询

需求说明 #

需要实现基于百分比的物品种群迭代转换逻辑：输入初始物种类型和种群数量，按照预设的转换规则（存储在populationChanges表中）逐层转换，直到没有可转换的物种类型，最终返回各最终物种的种群数量。

示例数据 #

CREATE TABLE populationChanges 
(
    id INT, 
    originaltype VARCHAR(50),
    NewType VARCHAR(50),
    percentOfPopulation DECIMAL(5,4)
)

INSERT INTO [dbo].[populationChanges] (id, [originaltype],[NewType], [percentOfPopulation])
VALUES (1, 'trout', 'bass', 0.4000),
       (2, 'trout', 'shark',0.6000),
       (3, 'shark', 'clownFish', 0.3000),
       (4, 'shark', 'PufferFish', 0.7000),
       (5, 'PufferFish', 'Tuna', 0.7000),
       (6, 'PufferFish', 'Coy', 0.1000),
       (7, 'PufferFish', 'Shrimp', 0.2000),
       (8, 'coy', 'lobster', 1.0000),
       (9, 'Shrimp', 'King Crab', 0.6000)

示例场景：

• 初始1000只Shrimp：转换为600只King Crab（无后续转换）
• 初始1000只PufferFish：先转为700只Tuna、100只Coy、200只Shrimp；Coy继续转为100只lobster，Shrimp转为120只King Crab；最终得到700只Tuna、100只lobster、120只King Crab。

实现思路：递归CTE #

利用SQL Server的**递归CTE（公共表表达式）**来模拟逐层转换过程，适合处理最多20层的层级转换需求：

1. 锚点成员：初始化输入的初始物种和数量，作为递归的起点。
2. 递归成员：关联populationChanges表，计算每个转换后的新物种数量，同时记录当前转换层级，直到达到最大层级或无后续转换规则。
3. 最终筛选：过滤出没有后续转换规则的物种，汇总其种群数量得到最终结果。

具体实现代码 #

-- 输入参数
DECLARE @initialType VARCHAR(50) = 'PufferFish';
DECLARE @initialCount INT = 1000;

WITH RecursivePopulation AS (
    -- 锚点：初始种群
    SELECT 
        CurrentType = @initialType,
        Population = CAST(@initialCount AS DECIMAL(18,4)),
        Level = 0
    UNION ALL
    -- 递归：执行层级转换
    SELECT 
        pc.NewType AS CurrentType,
        rp.Population * pc.percentOfPopulation AS Population,
        rp.Level + 1
    FROM RecursivePopulation rp
    JOIN populationChanges pc 
        ON rp.CurrentType = pc.originaltype
    WHERE rp.Level < 20 -- 限制最大转换层级，避免循环递归
)
-- 筛选无后续转换的最终物种，汇总数量
SELECT 
    CurrentType AS FinalSpeciesType,
    SUM(Population) AS FinalPopulationCount
FROM RecursivePopulation rp
WHERE NOT EXISTS (
    SELECT 1 
    FROM populationChanges pc 
    WHERE pc.originaltype = rp.CurrentType
)
GROUP BY CurrentType
ORDER BY CurrentType;

关键注意事项 #

• 精度处理：使用DECIMAL类型存储种群数量，避免整数运算导致的精度丢失。
• 循环防护：通过Level < 20限制最大转换层级，防止因循环转换规则（如A→B、B→A）导致的无限递归。
• 复用性：可将上述逻辑封装为存储过程，方便传入不同的初始类型和数量参数调用。

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