在Microsoft SQL Server的GROUP BY中将NULL作为通配符匹配

要实现将NULL视为通配符（即NULL可匹配自身及任何非NULL值）的GROUP BY操作，核心是构造能让兼容行归为同一组的分组逻辑，以下是几种可行方案：

方案1：递归CTE实现兼容分组（适用于复杂匹配场景） #

如果需要让所有无冲突非NULL字段的行归为一组（即只要字段值不矛盾，NULL即可匹配任意值），可以用递归CTE找出所有连通的兼容行：

假设你的表名为MyTable，且有唯一主键ID（如果没有，可先添加自增ID）：

WITH Compatibility AS (
    -- 定义两行兼容的条件：每个字段要么都为NULL，要么其中一个为NULL，要么值相等
    SELECT t1.ID AS ID1, t2.ID AS ID2
    FROM MyTable t1
    JOIN MyTable t2 ON 
        (t1.DNS IS NULL OR t2.DNS IS NULL OR t1.DNS = t2.DNS)
        AND (t1.TYPE IS NULL OR t2.TYPE IS NULL OR t1.TYPE = t2.TYPE)
        AND (t1.PORT IS NULL OR t2.PORT IS NULL OR t1.PORT = t2.PORT)
),
Groups AS (
    -- 初始化分组：每个行自身为一个分组
    SELECT ID1 AS GroupID, ID1 AS MemberID
    FROM Compatibility
    UNION ALL
    -- 递归合并兼容的分组
    SELECT g.GroupID, c.ID2 AS MemberID
    FROM Groups g
    JOIN Compatibility c ON g.MemberID = c.ID1
    WHERE c.ID2 NOT IN (SELECT MemberID FROM Groups WHERE GroupID = g.GroupID)
)
-- 按分组聚合，取每个分组的非NULL值（或保留NULL）
SELECT 
    MAX(CASE WHEN m.DNS IS NOT NULL THEN m.DNS END) AS DNS,
    MAX(CASE WHEN m.TYPE IS NOT NULL THEN m.TYPE END) AS TYPE,
    MAX(CASE WHEN m.PORT IS NOT NULL THEN m.PORT END) AS PORT
FROM Groups g
JOIN MyTable m ON g.MemberID = m.ID
GROUP BY g.GroupID;

方案2：动态SQL忽略NULL字段（适用于按非NULL字段分组） #

如果需求是仅按非NULL字段进行分组（即NULL字段完全不参与分组逻辑），可以用动态SQL自动生成GROUP BY的字段列表：

DECLARE @GroupByFields NVARCHAR(MAX);

-- 筛选出至少存在一个非NULL值的字段
SELECT @GroupByFields = STRING_AGG(COLUMN_NAME, ', ')
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'MyTable'
  AND COLUMN_NAME IN ('DNS', 'TYPE', 'PORT')
  AND EXISTS (SELECT 1 FROM MyTable WHERE COLUMN_NAME IS NOT NULL);

-- 生成并执行最终SQL
DECLARE @SQL NVARCHAR(MAX) = N'SELECT DNS, TYPE, PORT FROM MyTable GROUP BY ' + @GroupByFields;
EXEC sp_executesql @SQL;

方案3：替换NULL为统一标识（适用于简单匹配场景） #

如果仅需要让NULL彼此匹配，同时与某个特定标识匹配（而非所有值），可以将NULL替换为一个特殊标识（如'ALL'）：

SELECT 
    ISNULL(DNS, 'ALL') AS DNS,
    ISNULL(TYPE, 'ALL') AS TYPE,
    ISNULL(PORT, 'ALL') AS PORT
FROM MyTable
GROUP BY 
    ISNULL(DNS, 'ALL'),
    ISNULL(TYPE, 'ALL'),
    ISNULL(PORT, 'ALL');

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