如何将公共列（A，B）和（A，C）的2个查询只转换为一个（A，B，C）？ [英] How to turn 2 queries with common columns (A, B) and (A, C) into just one (A, B, C)?

问题描述

我目前有2个查询返回

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

我想知道的是我应该如何做，使我可以在单个查询相同的数据，也就是说，我想要的东西，将（ 生产者，FirstQueryColumn，SecondQueryColumn）。

我如何做？

这是我当前的查询：

  select Producers.name Prod，count（Animals.idanimal）AnimalsBought 
来自AnimalsBought，动物，生产者
其中（AnimalsBought.idanimal = Animals.idanimal）和（Animals.owner = Producers.nif）group by Producers.name; 
 
 select Producers.name产品，计数（Animals.idanimal）AnimalsExploration 
来自AnimalsExploration，动物，生产者
其中（AnimalsExploration.idanimal = Animals.idanimal）和= Producers.nif）group by Producers.name; 
  

如你所见，对于这种情况，加入不会做太多：

> select Producers.name产品，计数（AnimalsBought.idanimal）AnimalsBought，count（AnimalsExploration.idanimal）AnimalsExploration
从生产者，动物， AnimalExploration
其中（AnimalsExploration.idanimal = Animals.idanimal）和（Animals.owner = Producers.nif）group by Producers.name;


还是我做错了？

解决方案

我认为 animals.idanimal 是主键。如果是这样，可以使用左外连接和 count 在目标列上写入查询，以截断 NULLs 。

  select producers.name prod，
 count（animalsbought.idanimal）animalsbought，
 count（animalsexploration .idanimal）动物造价
从生产者
加入动物对动物。所有者=生产者.nif 
左连接animalsbought on animalsbought.idanimal = animals.idanimal 
 left join animalsexploration on animalsexploration.idanimal = animals.idanimal 
 group by producers.name; 
  

I currently have 2 queries that return

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

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

What I'd like to know is how should I go about making it such that I can have in a single query the same data, that is, I want something that'll yield (Producer, FirstQueryColumn, SecondQueryColumn).

How can I do that?

Here are my current queries:

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, Animals, Producers
where (AnimalsExploration.idanimal = Animals.idanimal) and (Animals.owner = Producers.nif) group by Producers.name;

As you can see, for this case a join will not do much:

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

or am I doing something wrong?

解决方案

I suppose animals.idanimal is a primary key. If it is so, one could write the query using left outer join and count on a target column to cut off NULLs.

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;

这篇关于如何将公共列（A，B）和（A，C）的2个查询只转换为一个（A，B，C）？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！