sql - 将结果与一些空值连接起来

Question

场景:我有一张表，该表返回我的水果无法销售的市场。最多可以有 4 种不同的条件将市场排除在水果之外。

我的代码是:

SELECT DISTINCT RPin, Variety, SubDivisionId, BlockId
, STUFF((
    SELECT ',' + CAST(ClearanceID as varchar)
    FROM [RPin_ExclusionT] t1
    where t1.RPin = t2.RPin AND t1.Variety = t2.Variety AND t1.SubDivisionId = t2.SubDivisionId AND t1.BlockId = t2.BlockId
    order by t1.ClearanceID 
    FOR XML PATH('')
), 1, 1, '') AS Clearance
FROM [RPin_ExclusionT] t2

例如:

Rpin   Variety   Subdivision   Block   Clearance
1234   039       B             A       RUS
1234   039       B             A       CHN

会导致:

Rpin   Variety   Subdivision   Block   Clearance
1234   039       B             A       RUS, CHN

如果每个字段都有数据，我的代码就可以工作。有时，间隙可能只有 RPin，其他所有内容均为 NULL

Rpin   Variety   Subdivision   Block   Clearance
1234   039       B             A       RUS
1234   039       B             A       CHN
1234   NULL      NULL          NULL    JAP
1234   NULL      NULL          NULL    TWN

我这样做是为了返回与以前相同的结果

Rpin   Variety   Subdivision   Block   Clearance
1234   039       B             A       RUS, CHN
1234   NULL      NULL          NULL    JAP, TWN

但是，任何列中存在 NULL 值的地方都会导致我的清除列中出现 NULL 值。

Rpin   Variety   Subdivision   Block   Clearance
1234   039       B             A       RUS, CHN
1234   NULL      NULL          NULL    NULL

我对 SQL 的了解充其量只是中级，无法完全调整查询以执行上述操作...任何指示将不胜感激。

Answer 1

我更喜欢在子查询之前执行distinct。您需要NULL安全比较:

SELECT RPin, Variety, SubDivisionId, BlockId,
       STUFF( (SELECT ',' + CAST(re2.ClearanceID as varchar(255))
               FROM RPin_ExclusionT re2
               WHERE re2.RPin = re.RPin AND
                     (re2.Variety = re.Variety OR re2.Variety IS NULL and re.Variety IS NULL) AND
                     (re2.SubDivisionId = re.SubDivisionId OR re2.SubDivisionId IS NULL AND re.SubDivisionId IS NULL) AND
                     (re2.BlockId = re.BlockId OR re2.BlockId IS NULL AND re.BlockId IS NULL)
               ORDER BY re2.ClearanceID 
               FOR XML PATH('')
              ), 1, 1, '') AS Clearances
FROM (SELECT DISTINCT RPin, Variety, SubDivisionId, BlockId
      FROM RPin_ExclusionR re
     ) re;