Finding combinations of specific values

问题:

I don't know how to write the query for below.

My table is

col1 col2 
5   1
5   5
5   6
5   7
4   5
4   8
4   9
4   3
3   3
3   5

I need to select distinct col1 id where both parameters exists in col2. eg. if i send 6,7 it should send me 5

回答1:

Try:

SELECT col1
FROM mytable
WHERE col2 IN (6, 7)
GROUP BY col1
HAVING COUNT(DISTINCT col2) = 2

回答2:

This is probably among the fastest solutions:

SELECT col1  -- already DISTINCT?
FROM   tbl t1
JOIN   tbl t2 USING (col1)
WHERE  t1.col2 = 6
AND    t2.col2 = 7;

Assuming a PRIMARY KEY or UNIQUE constraint on (col1, col2), like it's typically implemented. Else add DISTINCT.

There are many other ways to implement relational division. Here are some:

• How to filter SQL results in a has-many-through relation