SQL子查询多值报错及sortID匹配异常问题求助

问题说明 #

当前插入语句存在两个核心问题：

• 使用TOP 1会随机获取第一条sortID，无法匹配分组对应的正确值
• 移除TOP 1后触发「Subquery returned more than 1 value」错误，因单个ChargeCode对应多条@DataSetWithSortID记录

解决方案 #

通过预分组关联JOIN替代子查询，同时用聚合函数处理多值冲突，确保每个分组返回唯一有效的sortID。修改后的SQL如下：

INSERT INTO @FinalResultTable
SELECT 
    -- 第一部分：固定Account的应收记录
    COALESCE(sort1.sortID, 0) AS Posting_ID,
    'GL' AS DataCategory,
    dt.POL_ETS,
    NULL AS BLNo,
    '*first-12200 Receivables : Contract Assets' AS Account,
    SUM(dt.credit) AS Debit,
    0 AS Credit,
    dt.Customer_ID,
    dt.Vessel,
    dt.Voyage,
    NULL AS RevenueBranch,
    NULL AS POLCode,
    NULL AS Accounting_Period,
    0 AS IsPosted,
    NULL AS Date_Ingested,
    NULL AS Date_Posted,
    NULL AS User_Posted
FROM @DetailedTable dt
LEFT JOIN (
    -- 按关联维度预分组，取唯一sortID（用MAX/MIN按需选择）
    SELECT 
        customerID,
        VesselCode,
        VoyageNo,
        ChargeCode,
        MAX(sortID) AS sortID
    FROM @DataSetWithSortID
    WHERE ChargeCode = '*first-12200 Receivables : Contract Assets'
    GROUP BY customerID, VesselCode, VoyageNo, ChargeCode
) sort1 ON dt.Customer_ID = sort1.customerID 
        AND dt.Vessel = sort1.VesselCode 
        AND dt.Voyage = sort1.VoyageNo
GROUP BY dt.customer_id, dt.vessel, dt.voyage, dt.POL_ETS, sort1.sortID

UNION ALL

SELECT 
    -- 第二部分：动态Account的对应记录
    COALESCE(sort2.sortID, 0) AS Posting_ID,
    'GL' AS DataCategory,
    dt.POL_ETS,
    NULL AS BLNo,
    dt.Account,
    0 AS Debit,
    SUM(dt.credit) AS Credit,
    dt.Customer_ID,
    dt.Vessel,
    dt.Voyage,
    dt.RevenueBranch,
    dt.POLCode,
    NULL AS Accounting_Period,
    0 AS IsPosted,
    NULL AS Date_Ingested,
    NULL AS Date_Posted,
    NULL AS User_Posted
FROM @DetailedTable dt
LEFT JOIN (
    -- 按全关联维度预分组，避免多值返回
    SELECT 
        customerID,
        VesselCode,
        VoyageNo,
        ChargeCode,
        MAX(sortID) AS sortID
    FROM @DataSetWithSortID
    GROUP BY customerID, VesselCode, VoyageNo, ChargeCode
) sort2 ON dt.Customer_ID = sort2.customerID 
        AND dt.Vessel = sort2.VesselCode 
        AND dt.Voyage = sort2.VoyageNo
        AND dt.Account = sort2.ChargeCode
GROUP BY dt.Customer_ID, dt.Vessel, dt.Voyage, dt.POL_ETS, dt.account, dt.RevenueBranch, dt.POLCode, sort2.sortID

关键修改点 #

• 替换子查询为JOIN：将原本的关联子查询改为预分组的LEFT JOIN，确保每个分组匹配到对应维度的sortID，而非随机取第一条
• 解决多值冲突：在@DataSetWithSortID的子查询中按customerID, VesselCode, VoyageNo, ChargeCode分组，用MAX()（或MIN()，根据业务规则选择）聚合sortID，保证每个维度组合仅返回一个值
• 处理无匹配场景：用COALESCE设置无匹配时的默认值，避免返回NULL（可根据业务需求调整默认值）

内容的提问来源于stack exchange，提问作者Kevin Joseph G. Jurado