SQL Select Data with condition on multiple rows

问题:

Suppose that I have a table called "tblTemp" with the following data

ID        Name  
1           A  
2           B  
3           C  
4           A  
4           B  
5           A  
5           B  
5           C  
6           C  
6           B

I want to get ID from name of A&B only not A&B&C like below:

4          A 

or

4          B

How can I do like this in sql?

I try the following sql but it return row 5 as well:

SELECT     tblTemp.ID, tblTemp.Name
FROM         tblTemp INNER JOIN
                  tblTemp AS tbltemp_1 ON tblTemp.ID = tbltemp_1.ID
WHERE     (tblTemp.Name = 'A') AND (tbltemp_1.Name = 'B')

回答1:

One of the ways to compare sets is to take the count of group, filter groups by search set, and see if number of matches per group equals original number of group members:

select tblTemp.ID
  from tblTemp
 inner join
 (
   select ID,
          count(*) GroupCount
     from tblTemp
    group by ID
   having count(*) = 2
 ) g
   on tblTemp.ID = g.ID
 where tblTemp.Name in ('A', 'B')
 group by tblTemp.Id, g.GroupCount
having count (*) = g.GroupCount

This should work on both MySql and Sql Server.

You can play with this code @ Sql Fiddle.

回答2:

try:

SELECT distinct ID
FROM tblTemp a
     LEFT JOIN tblTemp b
           ON a.ID = b.ID AND 
              b.name  = 'C'
WHERE b.ID IS NULL;

回答3:

select id from table
group by id
having min(Name)='A' and max(Nmae)='B'

回答4:

 SELECT ID, Name FROM tblTemp WHERE (Name = 'A' OR Name = 'B') and ID not in(SELECT ID FROM tblTemp WHERE Name = 'C') 

回答5:

Do you just want a list of distinct IDs that have A or B but not C?

SELECT distinct ID
FROM tblTemp
WHERE Name = 'A' or Name = 'B'

EXCEPT

SELECT distinct ID
FROM tblTemp
WHERE Name = 'C'

My above solution work for 'A' and/or 'B', but I notice you actually want 'A' and 'B' with no or. In that case:

SELECT distinct ID
FROM tblTemp as T1
INNER JOIN tblTemp as T2
ON T1.ID = T2.ID
WHERE T1.Name = 'A' and T2.Name = 'B'

EXCEPT

SELECT distinct ID
FROM tblTemp
WHERE Name = 'C'

this is an extension of your original code and is perhaps not as elegant as @Madhivanan's solution but it is more general should A B and C change to words for example.