拥有至少10只狗的人？

Question

我有以下表格：

 person( sin, name, address)
 owner(dogowner_id, dog_id, is_primary_owner_of_dog)
 dog( dog_no, breed_id )
 dog_breed( breed_id, breed )

你如何去寻找所有的人谁是主要(主要的所有者)至少10个黄色实验室？我对此感到非常困惑，我会非常感激的。

编辑:请不要嵌套查询和聚合！

Answer 1

一种方法是利用IN表上的person：

SELECT * FROM person
WHERE sin IN (
    SELECT dogowner_id FROM owner o
        JOIN dog d ON o.dog_id = d.dog_no
        JOIN dog_breed b ON b.breed_id = d.breed_id
    WHERE is_primary_owner_of_dog AND
        b.breed = 'Yellow Lab'
    GROUP BY dogowner_id
    HAVING COUNT(dogowner_id) >= 10
)

内部查询将确定“主所有者”至少拥有10条狗，然后外部查询将返回相应的person记录。

Answer 2

select count(*), dogowner
from owner 
  inner join dog on dog.dog_no = owner.dog_id
  inner join dob_breed on dog.breed_id = dog_breed.breed_id
where dog_breed.breed = 'Yellow Lab'
group by dogowner
having count(*) >=10

Answer 3

你用join和group by来做这件事

select dogowner, count(*) as numyellowlabs
from owner o
     dog d
     on o.dog_id = d.dog_no join
     dog_breed db 
     on d.breed_id = db.breed_id
where breed = 'Yellow Lab'
group by dogowner
having count(*) >=10;