Geek asks and answers

How to execute "WHERE" in row groups?

I have a table that looks like this:

+-----------+----------+
+ person_id + group_id +
+-----------+----------+
+    1      +    10    +
+    1      +    20    +
+    1      +    30    +
+    2      +    10    +
+    2      +    20    +
+    3      +    10    +
+-----------+----------+

I need a query that returns only person_ids with groups 10 AND 20 AND 30 (only person_id: 1). I'm not sure how to do this, since what I see will require me to group the rows using person_id, and then select the rows containing all group_id.

I am looking for something that will preserve the use of keys without resorting to string operations on group_concat()or such.

+3
source share
3 answers

This is what you are looking for:

select person_id from t
where group_id in (10, 20, 30)
group by person_id
having count(distinct group_id) = 3

Effectively, using this solution, the number of values ​​in inshould correspond to the value with which you are comparing the score.

" ", , group_concat, : P

select person_id from t
where group_id in (10, 20, 30)
group by person_id
having group_concat(distinct group_id order by group_id) = '10,20,30'
+5

- :

select person_id
  from ATable
 where group_id IN (10, 20, 30)
 group by person_id
having count(*) = 3
+5
SELECT person_id FROM... WHERE group_id IN(10,20,30) group by person_id
having count(*) = 3
-2

More articles:


All Articles