MySQL query count multiple values
Consider the following DB table:
c p
=========
1 'a'
1 'b'
2 'a'
2 'c'
Now, my goal is to retrieve a list of numbers c, for which holds that each number in this list has at least a record with p='a' AND p='b'.
In the example table above, that would be开发者_开发百科 c=1.
Now my question is, how do I accomplish this using one MySQL query?
select t1.c
from MyTable t1
inner join MyTable t2 on t1.c = t2.c
where t1.p = 'a' and t2.p = 'b'
Update:
select c
from MyTable
where p in ('a', 'b', 'c', 'd')
group by c
having count(distinct p) = 4
There are different ways to attack the problem depending on the rules your data follows if any. Without knowing more about your problem, I would do:
SELECT t1.c FROM table t1 INNER JOIN table t2
ON t1.c = t2.c
WHERE t1.p = 'a' AND t2.p = 'b'
精彩评论