Database Record Has All Values

问题:

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

and I want to select a given ConsumerID only if they have all of the tag values I'm looking for.

For example, this selects all records with any of the 3 tags:

SELECT ConsumerID
FROM   BorrowerTags
WHERE  Tag IN('bBROAapp', 'bBROtiva');

Results:

  1000028041    bBROAapp
  1000028041    bBROtiva

This returns no results

SELECT ConsumerID
FROM   BorrowerTags
WHERE  Tag = 'bBROAapp'
AND    Tag = 'bBROtiva';

No Results

I only want those records where the ConsumerID has both values - not either one.

回答1:

I want to select a given ConsumerID only if they have all of the tag values

You could use:

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 ...)

回答2:

You can use INTERSECT to get your expected result:

SELECT DISTINCT ConsumerID FROM BorrowerTags WHERE Tag = 'bBROAapp'
INTERSECT 
SELECT DISTINCT ConsumerID FROM BorrowerTags WHERE Tag = 'bBROtiva'

Result:

1000028041