SQL查询 - 去重结果

Question

我有以下两个表格：

People [*ID*, Name] 
Pet [*PetID*, OwnerID, Species, Name]

OwnerID是ID的外键

我希望数据库列出每个人拥有多少不同物种。例如，如果Bob（ID 1473）拥有一只狗，一只猫和另一只狗，则输出应为：

ID    | No. of Species
----------------------
1473  | 2

我知道这需要相关子查询或外连接，但我不太确定如何实现。希望能得到帮助。

- Sticky

Answer 1

你可以使用 count(distinct ...) 来实现这个功能：

select  People.ID
,       count(distinct Species)
from    People
join    Pet
on      Pet.OwnerID = People.ID
group by
        People.ID

- Andomar

---

"列出每个人" - Andriy M

@Andriy M：我猜应该是“左连接” :) - Andomar

是的，我也是这么想的。但是楼主似乎同意你的选择了。 :) - Andriy M

差不多就行了。Andomar先回答了，所以我接受了他的回答 :) - Sticky

Answer 2

试一下这个

Select ID,[No. of Species] from People 
inner join 
  ( select Count(Species) as [No. of Species],OwnerID from Pet  
   group by OwnerID) d 
on Id = d.OwnerID

- Pranay Rana

---

但是 count(*) 会计算宠物的数量，而不是物种的数量？ - Andomar

Answer 3

select people.name, count(distinct pet.species)
from people, pet
where people.id = pet.ownerid
group by people.name

- StevieG