如何在含多表JOIN的外层SELECT中正确使用STRING_AGG

你的问题根源是嵌入的子查询硬绑定了变量@iIbrId，但外层查询遍历的是mpm_icabinreq_ibr表的匹配行，只有当当前行的a.iIbrId等于@iIbrId时，子查询才会返回数据，其余行都会返回NULL。正确的做法是让子查询关联外层查询的当前行a.iIbrId，而非固定变量。

以下是两种可行的修正方案：

方法1：关联子查询（修正WHERE条件） #

修改子查询的关联条件，绑定外层当前行的a.iIbrId，同时简化冗余的表关联：

DECLARE @iIbrId INTEGER
SET @iIbrId = 631
SELECT 
    a.iIbrId AS "Request Number", 
    iIbsStatus, 
    sPerFirstName + ' ' + sPerLastName AS sPerFullName, 
    a.iIbrRequest, 
    a.iIbrTypId, 
    sTypName,
    a.sIbrDesc, 
    a.dIbrTarget, 
    a.sIbrTransfer, 
    a.iIbrBIN AS BIN, 
    a.sIbrBINExtStart, 
    a.sIbrBINExtEnd, 
    a.iIbrEntIdClient,
    b.iIbrEntIdProcessor AS iPreviousProcessor,
    -- 修正后的关联子查询
    (SELECT STRING_AGG(CONVERT(NVARCHAR(max), sCdmName), ', ') WITHIN GROUP (ORDER BY sCdmName)
     FROM (
         SELECT DISTINCT cdm.sCdmName 
         FROM mpm_icabinreq_cardmark_rcm rcm
         INNER JOIN mpm_cardmark_cdm cdm ON rcm.iRcmCdmId = cdm.iCdmId
         WHERE rcm.iRcmIbrId = a.iIbrId  -- 关联外层当前行的请求ID
     ) x) AS CardMarks
FROM mpm_icabinreq_ibr a
INNER JOIN mpm_ibrstatus_ibs ON a.iIbrId = iIbsIbrId
INNER JOIN mpm_person_per ON iIbsPerId = iPerId
INNER JOIN mpm_type_typ ON iTypId = a.iIbrTypId
LEFT JOIN mpm_entity_ent ON iEntId = a.iIbrEntIdClient
LEFT JOIN mpm_icabinreq_ibr b ON b.iIbrId = a.iIbrParentId
LEFT JOIN mpm_multibank_mbk ON a.iMbkEntId = mpm_multibank_mbk.iMbkEntId
LEFT JOIN mpm_bin_bin ON a.iIbrBin = iBinId
LEFT JOIN mpm_icabinpseudort_irt ON iIrtIbrId = a.iIbrId
WHERE a.iIbrId = @iIbrId  -- 仅查询指定请求的行

方法2：使用OUTER APPLY（更高效清晰） #

用OUTER APPLY替代子查询，逻辑更直观，同时避免子查询重复执行的性能问题：

DECLARE @iIbrId INTEGER
SET @iIbrId = 631
SELECT 
    a.iIbrId AS "Request Number", 
    iIbsStatus, 
    sPerFirstName + ' ' + sPerLastName AS sPerFullName, 
    a.iIbrRequest, 
    a.iIbrTypId, 
    sTypName,
    a.sIbrDesc, 
    a.dIbrTarget, 
    a.sIbrTransfer, 
    a.iIbrBIN AS BIN, 
    a.sIbrBINExtStart, 
    a.sIbrBINExtEnd, 
    a.iIbrEntIdClient,
    b.iIbrEntIdProcessor AS iPreviousProcessor,
    cm.CardMarks
FROM mpm_icabinreq_ibr a
INNER JOIN mpm_ibrstatus_ibs ON a.iIbrId = iIbsIbrId
INNER JOIN mpm_person_per ON iIbsPerId = iPerId
INNER JOIN mpm_type_typ ON iTypId = a.iIbrTypId
LEFT JOIN mpm_entity_ent ON iEntId = a.iIbrEntIdClient
LEFT JOIN mpm_icabinreq_ibr b ON b.iIbrId = a.iIbrParentId
LEFT JOIN mpm_multibank_mbk ON a.iMbkEntId = mpm_multibank_mbk.iMbkEntId
LEFT JOIN mpm_bin_bin ON a.iIbrBin = iBinId
LEFT JOIN mpm_icabinpseudort_irt ON iIrtIbrId = a.iIbrId
-- 关联每个请求对应的CardMarks
OUTER APPLY (
    SELECT STRING_AGG(CONVERT(NVARCHAR(max), sCdmName), ', ') WITHIN GROUP (ORDER BY sCdmName) AS CardMarks
    FROM (
        SELECT DISTINCT cdm.sCdmName 
        FROM mpm_icabinreq_cardmark_rcm rcm
        INNER JOIN mpm_cardmark_cdm cdm ON rcm.iRcmCdmId = cdm.iCdmId
        WHERE rcm.iRcmIbrId = a.iIbrId
    ) x
) cm
WHERE a.iIbrId = @iIbrId

额外优化提示 #

如果希望无CardMarks的行返回空字符串而非NULL，可以用ISNULL函数包裹STRING_AGG结果：

ISNULL(STRING_AGG(CONVERT(NVARCHAR(max), sCdmName), ', ') WITHIN GROUP (ORDER BY sCdmName), '') AS CardMarks

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