SQL Server中如何结合WHERE条件与GROUP BY拼接列值

你的问题根源很明确：主查询通过a.AssetSeqNumber IN (1,2,3,4,5)过滤了数据，但子查询只匹配了AssetType = a.AssetType，没有同步这个ID过滤条件，所以子查询会拉取该资产类型下的所有ID，包括不在指定列表里的101、102。

下面是三种可行的解决方法：

方法1：子查询添加相同的ID过滤条件 #

直接在子查询里加上和主查询一致的AssetSeqNumber IN (1,2,3,4,5)条件，确保只拼接符合要求的ID：

SELECT
    AssetType, 
    COUNT(AssetSeqNumber) AS count,
    STUFF((SELECT DISTINCT ',' + CAST(AssetSeqNumber AS varchar(100))
           FROM Asset
           WHERE AssetType = a.AssetType 
             AND AssetSeqNumber IN (1, 2, 3, 4, 5) -- 添加此条件
           FOR XML PATH ('')), 1, 1, '') AS AssetIds
FROM 
    Asset AS a
WHERE
    a.AssetSeqNumber IN (1, 2, 3, 4, 5)
GROUP BY 
    AssetType

方法2：先用CTE过滤目标数据集 #

先把符合条件的数据提取到公共表表达式（CTE）中，后续的统计和拼接都基于这个过滤后的数据集，避免重复写过滤条件：

WITH FilteredAssets AS (
    SELECT AssetSeqNumber, AssetType
    FROM Asset
    WHERE AssetSeqNumber IN (1, 2, 3, 4, 5)
)
SELECT
    AssetType, 
    COUNT(AssetSeqNumber) AS count,
    STUFF((SELECT DISTINCT ',' + CAST(AssetSeqNumber AS varchar(100))
           FROM FilteredAssets
           WHERE AssetType = fa.AssetType 
           FOR XML PATH ('')), 1, 1, '') AS AssetIds
FROM 
    FilteredAssets AS fa
GROUP BY 
    AssetType

方法3：使用SQL Server 2019支持的STRING_AGG函数（推荐） #

SQL Server 2019及以上版本支持STRING_AGG函数，专门用于字符串拼接，语法更简洁直观，不需要嵌套子查询和STUFF：

SELECT
    AssetType, 
    COUNT(AssetSeqNumber) AS count,
    STRING_AGG(DISTINCT CAST(AssetSeqNumber AS varchar(100)), ',') AS AssetIds
FROM 
    Asset
WHERE
    AssetSeqNumber IN (1, 2, 3, 4, 5)
GROUP BY 
    AssetType

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