执行此查询的其他方法？

Question

我有以下表格：

Animals (animal_id, producer_id, ...)
Producers (prod_id, ...)
BoughtAnimals (animal_id (fk), ...)

我想做一个查询，告诉我每个生产商有多少动物，以及购买了多少动物。经过深思熟虑，我尝试了以下方法：

select Producers.name, count (distinct A1.animal_id), count(distinct BoughtAnimals.animal_id)
from Producers, Animals A1, Animals A2, BoughtAnimals
where
  Producers.nif = A1.producer_id and
  Producers.nif = A2.producer_id and
  BoughtAnimals.animal_id = A2.animal_id
group by Producers.name;

但我只是通过反复试验才做到的，而且我发现很难同时推理多个 Animal 表。还有其他方法可以进行此查询吗？或者这是通常的做法？

Answer 1

尝试这样的事情

select p.name,
    sum(case when ba.anyfield is not null then 1 else 0 end) bought_count,
    count(1) total_count
from Producers p join Animals a on (p.nif = a.producer_id)
    left join BoughtAnimals ba using (animal_id)
group by p.name;

Answer 2

使用简单的 JOIN，然后您可以将“COUNT”放入 HAVING 语句中。请参阅 LEFT / INNER JOIN 和 HAVING 的文档，具体取决于您的 SGDB。

Answer 3

我现在忽略生产者表；您需要的所有关键数据都在其他两个表中。一旦这部分正确，您就可以在生产者表上进行内部联接来获取您需要的其他详细信息。

select a1.producer_id, 
       count(a1.animal_id) as num_animals, 
       count(b1.animal_id) as num_bought
from animals a1
left join boughtanimals b1 on (b1.animal_id = a1.animal_id)
group by producer_id;

我不清楚最后一列是否更适合命名为“num_bought”或“num_sold”。同样不清楚的是，考虑到某些动物要么被购买，要么被出售，生产者“拥有”动物意味着什么。