SQL efficient way to join a table where all values

问题:

table 1
item    group_id
123     aaaa
432     bbbb
534     aaaa
765     cccc
656     aaaa
656     cccc
111     cccc

group_id    group_name
aaaa        groupA
bbbb        groupB
cccc        groupC

I want to find all groups that contain both 765 and 656 - in this instance GroupC contains both of those values.

Is there a way that avoids a bunch of nested queries or AND statements? Say if table 1 can have thousands of pairings and I have to find the groups that contain all of a list of 30 items It would be ugly to write a really big query to answer the question.

I wish I could say "where in (765,656)" but have it behave differently. If there was a bunch of items to match on it would get messy.

SQL Fiddle http://sqlfiddle.com/#!9/6581eb/9

Sample data (in case SQL Fiddle is inaccessible for some reason):

create table table1(itemID int,groupID varchar(4));
insert into table1(itemID,groupID)
values
  (123,'aaaa')
  ,(432,'bbbb')
  ,(534,'aaaa')
  ,(765,'cccc')
  ,(656,'aaaa')
  ,(656,'cccc')
  ,(111,'cccc');

create table table2(groupID varchar(4),groupName varchar(6));
insert into table2(groupID,groupName)
values
  ('aaaa','groupA')
  ,('bbbb','groupB')
  ,('cccc','groupC');

回答1:

The simplest thing would be to use group by and having:

SELECT group_name
FROM table2 g
JOIN table1 t on g.group_id = t.group_id
WHERE t.item in (765,656)
GROUP BY group_name
HAVING COUNT(DISTINCT t.item) = 2

回答2:

SELECT DISTINCT t1.group_name 
                    FROM table1 t1 join table1 t2 join groups g
                        ON t1.group_id = t2.group_id 
                           and t1.item = 765 
                           and t2.item = 656
                           and g.group_id = t1.group_id

回答3:

You can use the INTERSECT operator. INTERSECT returns the shared distinct values from both tables. In practice I've found this faster than other methods.

In this example I put intersect in-between the queries below:

SELECT T2.groupName 
  FROM table1 T1 INNER JOIN table2 T2 ON T1.groupID = T2.groupID
 WHERE T1.itemID = 765

INTERSECT 

SELECT T2.groupName 
  FROM table1 T1 INNER JOIN table2 T2 ON T1.groupID = T2.groupID
 WHERE T1.itemID = 656

Provides output:

groupName
---------
groupC