SQL 查询 - 不同的结果

发布于 2024-11-11 14:05:41 字数 344 浏览 2 评论 0原文

我有以下两个表：

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

（OwnerID 是 ID 的外键）

我希望数据库列出每个人以及他们拥有多少不同物种。例如，如果 Bob (ID 1473) 拥有一只狗、一只猫和另一只狗，则输出应该是：

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

我意识到这需要相关子查询或外连接，但我不太确定如何做到这一点。任何帮助将不胜感激。

原文

I have the following two tables:

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

(OwnerID is a foreign key of ID)

I would like the database to list each person and how many different species they own. For example, if Bob (ID 1473) owned a dog, cat and another dog the output should be:

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

I realize that this would require correlated sub-queries or outer joins, but I'm not exactly sure how to do that. Any help would be appreciated.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

习惯成性 2024-11-18 14:05:41

您可以使用 count(distinct ...) 来实现：

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

You could use count(distinct ...) for that:

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

回复收藏 0 原文

淡忘如思 2024-11-18 14:05:41

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

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

回复收藏 0 原文

痴骨ら 2024-11-18 14:05:41

试试这个

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

try this

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

回复收藏 0 原文

~没有更多了~