我目前有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;
或者我做错了什么？
解决方法
我认为animals.idanimal是主键.如果是这样,可以使用左外连接编写查询并在目标列上计数以切断NULL.
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;
