计算字段：跨表多条件查询最新值的最优实现方案咨询

问题描述 #

现有两张数据表：

• Table A：包含ITEM ID及物品其他信息
• Table B：包含ITEM ID、OPERATION、SUBOPERATION、VALUE、FLAG IS LAST字段

需要创建视图返回以下内容：

• A.ITEM ID
• Name of OPERATION 1：对应Table B中OPERATION='0040'且SUBOPERATION='0040'的VALUE
• Name of OPERATION 2：对应Table B中OPERATION='0090'且SUBOPERATION='0090'的VALUE

要求仅提取Table B中FLAG IS LAST='LAST'的最新记录，且同一ITEM ID+OPERATION+SUBOPERATION组合结果唯一。原实现使用标量函数逐行查询Table B，担心效率不足，需优化且保持输出格式不变。

原实现的问题 #

原标量函数GetLastResult会逐行调用，每处理Table A的一行就查询一次Table B，即使数据量仅1万条，也会产生大量重复查询，执行效率低下；同时原视图中的LEFT JOIN TABLE_B属于冗余操作，反而可能导致结果出现重复行。

优化方案 #

方案1：使用PIVOT数据透视（适合扩展更多操作的场景） #

先过滤出Table B中符合条件的记录，再通过透视将行转列，最后与Table A关联：

CREATE VIEW dbo.ItemOperationView
AS
WITH FilteredLatestB AS (
    -- 过滤出标记为LAST的记录，确保每个ITEM ID+OP+SUBOP组合唯一
    SELECT 
        [ITEM ID], 
        [OPERATION], 
        [SUBOPERATION], 
        [VALUE]
    FROM [dbo].[TABLE_B]
    WHERE [FLAG IS LAST] = 'LAST'
    -- 若同一组合存在多条LAST记录，通过GROUP BY或ROW_NUMBER确保唯一性
    GROUP BY [ITEM ID], [OPERATION], [SUBOPERATION], [VALUE]
)
SELECT 
    A.[ITEM ID],
    -- 用ISNULL避免空值，可替换为实际业务默认值
    ISNULL(pvt.[0040_0040], '') AS Name_of_OPERATION_1,
    ISNULL(pvt.[0090_0090], '') AS Name_of_OPERATION_2
FROM [dbo].[TABLE_A] A
LEFT JOIN (
    SELECT 
        [ITEM ID],
        -- 拼接OP和SUBOP作为透视列的标识
        CONCAT([OPERATION], '_', [SUBOPERATION]) AS OpSubOpKey,
        [VALUE]
    FROM FilteredLatestB
) B
PIVOT (
    -- 聚合函数仅满足PIVOT语法要求，因已过滤唯一记录，MAX/Min结果一致
    MAX([VALUE])
    FOR OpSubOpKey IN ([0040_0040], [0090_0090])
) pvt ON A.[ITEM ID] = pvt.[ITEM ID]

方案2：多次LEFT JOIN（适合操作数量较少的场景） #

直接关联两次过滤后的Table B，分别获取两个操作对应的值：

CREATE VIEW dbo.ItemOperationView
AS
WITH FilteredLatestB AS (
    SELECT 
        [ITEM ID], 
        [OPERATION], 
        [SUBOPERATION], 
        [VALUE]
    FROM [dbo].[TABLE_B]
    WHERE [FLAG IS LAST] = 'LAST'
    GROUP BY [ITEM ID], [OPERATION], [SUBOPERATION], [VALUE]
)
SELECT 
    A.[ITEM ID],
    ISNULL(Op1.[VALUE], '') AS Name_of_OPERATION_1,
    ISNULL(Op2.[VALUE], '') AS Name_of_OPERATION_2
FROM [dbo].[TABLE_A] A
LEFT JOIN FilteredLatestB Op1 
    ON A.[ITEM ID] = Op1.[ITEM ID] 
    AND Op1.[OPERATION] = '0040' 
    AND Op1.[SUBOPERATION] = '0040'
LEFT JOIN FilteredLatestB Op2 
    ON A.[ITEM ID] = Op2.[ITEM ID] 
    AND Op2.[OPERATION] = '0090' 
    AND Op2.[SUBOPERATION] = '0090'

优化优势 #

• 两种方案均为一次性查询Table B，避免逐行调用函数的重复开销
• 通过CTE提前过滤符合条件的记录，减少后续关联的数据量
• 执行计划更高效，即使数据量增长，性能表现也优于原标量函数方案

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