如何使用IN将单个值与另一列中逗号分隔的字段进行比较

Question

我有张像这样的桌子。让我们称它为table1：

|attributeMask |
|--------------|
| ,300,302,400 | 
| ,301,500     | 
| ,900,876     | 

这些是varchar值。

然后我有了另一个table2：

 columnNumber  | 
|------|
| 300  | 
| 500  | 
| 900  | 

这些都是整数。

我需要的是一个查询来比较columnNumber和attributeMask中的值。

例如，我需要在attributeMask中找到具有300的行(对于每一行)。第二个columnNumber是500，我将将它与它自己的attributeMask (可以是,299,34,500)中的相应行进行比较)。我正在尝试使用FOR XML PATH或其他函数，但我不知道该做什么。

到目前为止，这是我可以创建的整个查询：

select  top 3  
    t1.[Name]
   ,t1.ColumnNumber
   ,t3.AttributeMask 
   ,t3.changeData 
   ,t3.CreatedOn
   ,t3.ObjectTypeCode
   ,t3.UserId
from metadataschema.attribute     t1
inner join  metadataschema.Entity t2
    on t1.EntityId=t2.EntityId
inner join AuditBase              t3
    on t2.ObjectTypeCode=t3.ObjectTypeCode
where t2.ObjectTypeCode=1 
//this is wrong. but is this part that I need to fix
and convert(varchar,t1.ColumnNumber) in (select t3.AttributeMask from 
AuditBase)
//until here
and t3.CreatedOn BETWEEN '20190902' AND '20190913' 
and t1.[Name] in
(
'address1_postalcode','smart_caixapostal','smart_tipologradouro','smart_complementoprincipal','smart_estado','smart_logradouroprincipal','smart_bairroprincipal',
'new_regiao','smart_numero','address1_city','smart_pais','smart_validarendereco','smart_cepcobranca','smart_caixacobranca','smart_validarendereco_cob',
'smart_tipologradourocobranca','smart_logradourocobranca','smart_numerocobranca','smart_complementocobranca','smart_bairrocobranca','smart_municipiocobranca',
'smart_estadocobranca','territoryid','smart_paiscobranca','smart_cepmkt','smart_logradouromkt','smart_complementomkt','smart_ufmkt','smart_tipodelogradouromkt',
'smart_bairromkt','smart_ddidddmkt','smart_caixapostalmkt','smart_numeromkt','smart_municipiomkt','smart_telefonemkt'
)

Answer 1

有办法做到这一点。但是，我强烈建议您重新考虑在列中使用逗号分隔的值。如果您绝对必须这样做，则可以在通配符中使用LIKE运算符。这将使您能够在属性掩码字符串中搜索值。期待糟糕的表现时，查找许多价值。

inner join AuditBase              t3
    on t2.ObjectTypeCode=t3.ObjectTypeCode
    and t3.AttributeMask LIKE '%' + convert(varchar,t1.ColumnNumber) + '%'
AuditBase)

Answer 2

根据您提供的示例数据，一个条目被存储为<comma><value>。因此，匹配需要反映这一点：

t3.AttributeMask LIKE '%,' + CAST(t1.ColumnNumber AS VARCHAR(10)) + '%'

(CAST vs. CONVERT最终是一个品味问题，但明确指定VARCHAR's长度可以消除默认设置的特定上下文性质。)

然而，当数据仅包含1011时，上述情况与101的匹配情况不一致。因此，我们需要以下内容(或解决相同问题的其他方法)：

(t3.AttributeMask LIKE '%,' + CAST(t1.ColumnNumber AS VARCHAR(10))
    OR (t3.AttributeMask LIKE '%,' + CAST(t1.ColumnNumber AS VARCHAR(10)) + ',%'

这假设AttributeMask只包含逗号和数字。如果不能假设这一点，或者实际上不能假定一个前导逗号，那么每次都需要进行一点数据清理(当然，如果可能的话，最好避免)：

INNER JOIN AuditBase              t3
ON t2.ObjectTypeCode=t3.ObjectTypeCode
WHERE t2.ObjectTypeCode=1 
    AND ',' + REPLACE(t3.AttributeMask, ' ', '') + ','
        LIKE '%,' + CAST(t1.ColumnNumber AS VARCHAR(10)) + ',%'

考虑到LIKE测试的性质，在这个版本中总是在逗号前加上逗号是无害的。