我使用的是 SQL Server 2012,需要一些帮助才能在查询中获得所需的结果.我的数据如下所示:
I'm using SQL Server 2012 and need some help getting the results I need in a query. My data looks like this:
ConsumerID Tag 1000028041 bBROAapp 1000028041 bBROtiva 1000028041 bClsElig 1000028041 bPCAFwd 1000028041 bTOPNoRs 1000028041 bTOPNwRq 1000028041 bTOPActv并且我只想在给定的 ConsumerID 具有我要查找的所有标签值时选择给定的 ConsumerID.
and I want to select a given ConsumerID only if they have all of the tag values I'm looking for.
例如,这将选择具有 3 个标签中的任何一个的所有记录:
For example, this selects all records with any of the 3 tags:
SELECT ConsumerID FROM BorrowerTags WHERE Tag IN('bBROAapp', 'bBROtiva');结果:
1000028041 bBROAapp 1000028041 bBROtiva这没有返回任何结果
SELECT ConsumerID FROM BorrowerTags WHERE Tag = 'bBROAapp' AND Tag = 'bBROtiva';没有结果
我只想要那些 ConsumerID 有两个值的记录 - 而不是一个.
I only want those records where the ConsumerID has both values - not either one.
推荐答案仅当给定的 ConsumerID 具有所有标签值时,我才想选择给定的 ConsumerID
I want to select a given ConsumerID only if they have all of the tag values
您可以使用:
SELECT ConsumerID FROM BorrowerTags WHERE Tag IN ('bBROAapp', 'bBROtiva') GROUP BY ConsumerID HAVING COUNT(DISTINCT Tag) = 2;LiveDemo
如果您想要消费者的完整记录,您可以使用:
If you want entire records for consumer you could use:
SELECT * FROM BorrowerTags WHERE ConsumerID IN (... above query ...)更多推荐
数据库记录具有所有值
发布评论