SQL Server按analogy拆分数据并插入临时表的实现问题

需求概述 #

• 源表包含productName、color、analogy（字符串类型）、psc（数量）字段
• 当analogy为1/1时：
  • 保留原productName不变
  • 新增color为white的条目，analogy保持不变
  • 原条目和新条目的psc均除以2
• 扩展需求：支持其他analogy规则，比如2/1表示原颜色占2份、White占1份，按比例拆分psc

源表 #

期望临时表结果 #

更新后的问题 #

新增Gray、White、Black多颜色场景后，现有查询生成了多余无效条目，不符合预期：

MS SQL Server 2017 Schema Setup #

CREATE TABLE sourceTable (
    productName varchar(50),
    color varchar(50),
    analogy varchar(50),
    psc int
);

INSERT INTO sourceTable (productName, color, analogy, psc) VALUES ('Alpha', 'Gray', '1/1',1000);
INSERT INTO sourceTable (productName, color, analogy, psc) VALUES ('Gama', 'Black', '1/2',1500);
INSERT INTO sourceTable (productName, color, analogy, psc) VALUES ('Gama', 'White', '3/0',1500);

当前查询 #

SELECT t.productName,
x.color,
t.analogy,
CASE x.color 
    WHEN 'Gray' THEN psc * CAST(LEFT(analogy,CHARINDEX('/',analogy) - 1) as int) / (CAST(LEFT(analogy,CHARINDEX('/',analogy) - 1) as int)  + CAST(RIGHT(analogy,CHARINDEX('/',analogy) - 1) as int) )
    WHEN 'Black' THEN psc * CAST(LEFT(analogy,CHARINDEX('/',analogy) - 1) as int) / (CAST(LEFT(analogy,CHARINDEX('/',analogy) - 1) as int)  + CAST(RIGHT(analogy,CHARINDEX('/',analogy) - 1) as int) )
    WHEN 'White' THEN psc * CAST(RIGHT(analogy,CHARINDEX('/',analogy) - 1) as int)  / (CAST(LEFT(analogy,CHARINDEX('/',analogy) - 1) as int)  + CAST(RIGHT(analogy,CHARINDEX('/',analogy) - 1) as int) )
END AS psc
FROM sourceTable t 
CROSS JOIN (VALUES ('Gray'),('White'),('Black')) AS x(color)

当前结果 #

期望结果 #

问题核心：固定枚举颜色的CROSS JOIN会生成无关颜色的多余条目，需要通过逻辑过滤或动态关联有效颜色组合。

解决方案 #

核心思路 #

1. 拆分analogy为分子、分母，计算总份数，避免重复计算
2. 仅生成原颜色和对应目标拆分颜色的条目，过滤无关颜色
3. 针对特殊场景（如分母为0）单独处理逻辑

调整后的查询 #

WITH SplitAnalogy AS (
    SELECT 
        productName,
        color AS originalColor,
        analogy,
        psc,
        CAST(LEFT(analogy, CHARINDEX('/', analogy) - 1) AS INT) AS numerator,
        CAST(RIGHT(analogy, LEN(analogy) - CHARINDEX('/', analogy)) AS INT) AS denominator,
        CAST(LEFT(analogy, CHARINDEX('/', analogy) - 1) AS INT) + CAST(RIGHT(analogy, LEN(analogy) - CHARINDEX('/', analogy)) AS INT) AS totalParts
    FROM sourceTable
)
SELECT 
    productName,
    originalColor AS color,
    analogy,
    CASE WHEN totalParts = 0 THEN psc ELSE psc * numerator / totalParts END AS psc
FROM SplitAnalogy
UNION ALL
SELECT 
    productName,
    -- 根据业务规则定义原颜色对应的拆分目标颜色
    CASE 
        WHEN originalColor IN ('Gray', 'Black') THEN 'White'
        WHEN originalColor = 'White' THEN 'White'
    END AS color,
    analogy,
    CASE 
        WHEN totalParts = 0 THEN 0 
        ELSE psc * denominator / totalParts 
    END AS psc
FROM SplitAnalogy
WHERE denominator > 0 OR originalColor = 'White' -- 处理3/0这类分母为0但需生成0值条目的场景

说明 #

• 用CTESplitAnalogy统一拆分analogy字段，简化后续计算
• 通过UNION ALL分别生成原颜色条目和拆分后的目标颜色条目
• 可通过CASE语句灵活配置不同原颜色对应的目标拆分颜色
• 自动过滤无关颜色的多余条目，完全匹配期望结果

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