如何使用同一列创建Pivot查询？求助修正现有SQL查询问题

嘿，看你的问题，核心是用行号关联不同test_key_id的记录导致了数据错位，而且直接用test_recipe_id关联也有偏差对吧？我来帮你拆解问题，给出靠谱的解决方案。

先说说你原查询的核心问题 #

你用row_number() over (order by id)来关联t1到t5的表，这完全忽略了test_recipe_id这个唯一能把同组数据绑定在一起的键——不同test_recipe_id的记录id是连续的，行号会把它们混在一起，数据不乱才怪！如果用test_recipe_id关联还是有问题，大概率是你的数据里存在同一个test_recipe_id对应多个相同test_key_id的记录，或者部分test_key_id的记录缺失，得先排查这个。

方案一：用SQL Server原生PIVOT运算符实现行转列 #

这种方式适合标准的行转列场景，先筛选出需要的test_key_id，再转成你要的列：

WITH PivotData AS (
    SELECT
        test_recipe_id,
        test_key_id,
        LTRIM(RTRIM(value)) AS clean_value
    FROM [leakOpportunities].[dbo].[test_recipe_value]
    WHERE test_key_id IN (1, 2, 23, 24, 25) -- 只保留需要的key
)
SELECT
    test_recipe_id,
    [23] AS model_num,
    [25] AS revision,
    [24] AS value,
    -- 处理你的计算逻辑，加判断避免空值转换错误
    CASE 
        WHEN [24] IS NOT NULL AND [1] IS NOT NULL AND [2] IS NOT NULL
        THEN CAST([24] AS INT) - (CAST([24] AS INT) - CAST([1] AS INT) - CAST([2] AS INT))
        ELSE NULL -- 也可以改成0之类的默认值，看业务需求
    END AS calculated_value1,
    CASE 
        WHEN [24] IS NOT NULL AND [1] IS NOT NULL AND [2] IS NOT NULL
        THEN CAST([24] AS INT) - CAST([1] AS INT) - CAST([2] AS INT)
        ELSE NULL
    END AS calculated_value2
FROM PivotData
PIVOT (
    MAX(clean_value) -- 只要每个test_recipe_id+test_key_id唯一，用MAX/MIN都可以
    FOR test_key_id IN ([1], [2], [23], [24], [25])
) AS PivotResult
-- 可选：过滤掉所有字段都为空的无效行
WHERE [23] IS NOT NULL OR [25] IS NOT NULL OR [24] IS NOT NULL OR [1] IS NOT NULL OR [2] IS NOT NULL;

方案二：条件聚合（更灵活，适合复杂计算） #

如果你需要做更复杂的逻辑，条件聚合比PIVOT更灵活，而且兼容性更好：

SELECT
    test_recipe_id,
    MAX(CASE WHEN test_key_id = 23 THEN LTRIM(RTRIM(value)) END) AS model_num,
    MAX(CASE WHEN test_key_id = 25 THEN LTRIM(RTRIM(value)) END) AS revision,
    MAX(CASE WHEN test_key_id = 24 THEN LTRIM(RTRIM(value)) END) AS value,
    -- 计算逻辑，用判断处理空值，防止转换失败
    CASE
        WHEN MAX(CASE WHEN test_key_id = 24 THEN value END) IS NOT NULL
             AND MAX(CASE WHEN test_key_id = 1 THEN value END) IS NOT NULL
             AND MAX(CASE WHEN test_key_id = 2 THEN value END) IS NOT NULL
        THEN CAST(MAX(CASE WHEN test_key_id = 24 THEN value END) AS INT) 
             - (CAST(MAX(CASE WHEN test_key_id = 24 THEN value END) AS INT) 
                - CAST(MAX(CASE WHEN test_key_id = 1 THEN value END) AS INT) 
                - CAST(MAX(CASE WHEN test_key_id = 2 THEN value END) AS INT))
        ELSE NULL
    END AS calculated_value1,
    CASE
        WHEN MAX(CASE WHEN test_key_id = 24 THEN value END) IS NOT NULL
             AND MAX(CASE WHEN test_key_id = 1 THEN value END) IS NOT NULL
             AND MAX(CASE WHEN test_key_id = 2 THEN value END) IS NOT NULL
        THEN CAST(MAX(CASE WHEN test_key_id = 24 THEN value END) AS INT) 
             - CAST(MAX(CASE WHEN test_key_id = 1 THEN value END) AS INT) 
             - CAST(MAX(CASE WHEN test_key_id = 2 THEN value END) AS INT)
        ELSE NULL
    END AS calculated_value2
FROM [leakOpportunities].[dbo].[test_recipe_value]
WHERE test_key_id IN (1, 2, 23, 24, 25)
GROUP BY test_recipe_id
-- 可选：过滤掉没有核心字段的行
HAVING MAX(CASE WHEN test_key_id = 23 THEN value END) IS NOT NULL 
       OR MAX(CASE WHEN test_key_id = 25 THEN value END) IS NOT NULL 
       OR MAX(CASE WHEN test_key_id = 24 THEN value END) IS NOT NULL;

关键修正点和排查建议 #

• 必须用test_recipe_id作为核心关联/分组键：这是同组数据的唯一标识，行号完全不具备关联性，这是你原查询的致命问题。
• 处理重复记录：如果同一个test_recipe_id下有多个相同test_key_id的记录，用MAX()或MIN()可以取唯一值；如果需要保留所有重复，你得先确定规则（比如取最新的id对应的记录）。
• 空值处理：一定要加判断避免空值转换错误，不然会导致整个查询报错。
• 排查数据偏差原因：如果用test_recipe_id还是有问题，先运行下面的SQL看看是不是有重复的test_recipe_id+test_key_id组合：

SELECT test_recipe_id, test_key_id, COUNT(*) AS record_count
FROM [leakOpportunities].[dbo].[test_recipe_value]
WHERE test_key_id IN (1,2,23,24,25)
GROUP BY test_recipe_id, test_key_id
HAVING COUNT(*) > 1;

这个查询会找出所有重复的组合，这大概率是关联偏差的根源。

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