在子句- mysql中:选择具有一定数量关系行的人员

Question

我有两个关系表person和event_registration，我想选择所有注册了10个或更少事件的人。我发现了一个似乎是正确的查询。它可以通过以下查询找到这些人:

  SELECT person_id 
    FROM event_registration 
GROUP BY person_id 
  HAVING COUNT(*) <= 10

注意,event_registration。Person_id与匹配person.id的表行相关。现在我想使用这个查询搜索人表，但我的查询似乎崩溃mysql。为什么?

SELECT id 
  FROM person 
 WHERE id IN (
       SELECT person_id 
         FROM event_registration 
     GROUP BY person_id 
       HAVING COUNT(*) <= 10
 )

我想改变查询的原因是因为我也在寻找其他属性，所以(更接近)最终查询将看起来像:

SELECT * 
  FROM person 
 WHERE name LIKE '%something%' 
   AND AGE > 20 
   AND id IN (
       SELECT person_id 
         FROM event_registration 
     GROUP BY person_id 
       HAVING COUNT(*) <= 10
 )

当然这不起作用，因为mysql似乎崩溃了。我做错了什么?

Answer 1

SELECT distinct id FROM person 
WHERE id IN
    (SELECT person_id 
     FROM event_registration 
     GROUP BY person_id HAVING COUNT(*) <= 10)