sql – 如何将具有公共列(A,B)和(A,C)的2个查询转换为一个(A,B,C

我目前有2个返回的查询

PRODUCER                       FirstQueryColumn       
------------------------------ ---------------------- 
aaaaaaaaaaaa                   1                      
bbbbbbbbbbb                    1                      

PRODUCER                       SecondQueryColumn      
------------------------------ ---------------------- 
aaaaaaaaaaaa                   2                      
bbbbbbbbbbb                    1

我想知道的是我应该如何制作它以便我可以在一个查询中获得相同的数据,也就是说,我想要的东西会产生(Producer,FirstQueryColumn,SecondQueryColumn).

我怎样才能做到这一点？

这是我目前的查询：

select Producers.name Prod,count(Animals.idanimal) AnimalsBought
from AnimalsBought,Animals,Producers
where (AnimalsBought.idanimal = Animals.idanimal) and (Animals.owner = Producers.nif) group by Producers.name;

select Producers.name Prod,count(Animals.idanimal) AnimalsExploration
from AnimalsExploration,Producers
where (AnimalsExploration.idanimal = Animals.idanimal) and (Animals.owner = Producers.nif) group by Producers.name;

如您所见,对于这种情况,连接不会做太多：

select Producers.name Prod,count(AnimalsBought.idanimal) AnimalsBought,count(AnimalsExploration.idanimal) AnimalsExploration
from Producers,AnimalsBought,AnimalsExploration
where (AnimalsExploration.idanimal = Animals.idanimal) and (Animals.owner = Producers.nif) group by Producers.name;

或者我做错了什么？

解决方法

select producers.name prod,count(animalsbought.idanimal) animalsbought,count(animalsexploration.idanimal) animalsexploration
from producers
  join animals on  animals.owner = producers.nif
  left join animalsbought on animalsbought.idanimal = animals.idanimal
  left join animalsexploration on animalsexploration.idanimal = animals.idanimal
group by producers.name;

（编辑：威海站长网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!