是否有一种方法可以使用单个MySQL查询合并两个覆盖和乘结果的表？

Question

我有三个表，第一个存储大陆，第二个存储默认存在的动物，第三个存储特定大陆和动物的第二个表的例外。

大陆(限制为3以简化结果)

| continent_code |
|----------------|
| EU             |
| OC             |
| AN             |

animals_default_presence

| animal_id | animal     | presence |
|-----------|------------|----------|
| 1         | dog        | 1        |
| 2         | kangaroo   | 0        |
| 3         | polar bear | 0        |

continent_animals_presence_exceptions

| continent_code | animal_id | presence |
|----------------|-----------|----------|
| OC             | 2         | 1        |
| AN             | 1         | 0        |
| AN             | 3         | 1        |

其结果是总结了生活在那里的所有大陆和动物：

| continent_code | animal_id |
|----------------|-----------|
| EU             | 1         |
| OC             | 1         |
| OC             | 2         |
| AN             | 3         |

我可以通过一个MySQL查询获得这样的结果吗？

Answer 1

您可以使用union all。我认为以下是你所需要的：

select c.continent_code, adp.animal_id
from continent c cross join
     animals_default_presence adp 
where adp.presence = 1 and
      (c.continent_code, adp.animal_id) not in 
            (select cape.continent_code, cape.animal_id
             from continent_animals_presence_exceptions cape
             where cape.presence = 0
            )
union all
select cape.continent_code, cape.animal_id
from continent_animals_presence_exceptions cape
where cape.presence = 1;

这里是db<>fiddle。

Answer 2

首先，你需要在动物和大陆之间做一个CROSS JOIN来获得它们的所有可能的组合。现在，您可以根据动物和大陆对异常表执行LEFT JOIN。

最后，可以使用一个稍微复杂的WHERE条件来筛选出所需的结果集。我们要么考虑默认存在为1的行，而且它们没有例外，OR，那些默认存在为0的行，但是为它们定义了一个异常。

SELECT c.continent_code,
       cape.animal_id
FROM   (SELECT c.continent_code,
               a.animal_id,
               a.presence AS default_presence
        FROM   animals_default_presence AS a
               CROSS JOIN continents AS c
              ) AS all_combos
       LEFT JOIN continent_animals_presence_exceptions AS cape
              ON cape.continent_code = default_present_combos.continent_code
                 AND cape.animal_id = default_present_combos.animal_id
WHERE  ( all_combos.default_presence = 1
         AND ( cape.presence = 1
                OR cape.presence IS NULL ) )
        OR ( all_combos.default_presence = 0
             AND cape.presence = 1 ) 

使用UNION ALL**:**的早期版本

一种方法是利用UNION ALL。在其中一个SELECT查询中，您可以获得SELECT和continent_code的所有组合，其中1是默认存在的。但是，我们需要使用LEFT JOIN .. WHERE .. IS NULL进行“反连接”，以消除在特定大陆上默认动物的“存在”被定义为的异常。

在另一个SELECT查询中，我们只获得默认存在为的动物的组合，但它们在某些大陆上的存在是1，正如在异常中定义的那样。

SELECT 
  c.continent_code, cape.animal_id 
FROM 
  (SELECT c.continent_code, a.animal_id 
   FROM animals_default_presence AS a 
   CROSS JOIN continents AS c 
   WHERE a.presence = 1) AS default_present_combos 
   LEFT JOIN continent_animals_presence_exceptions AS cape 
          ON cape.continent_code = default_present_combos.continent_code 
             AND cape.animal_id = default_present_combos.animal_id 
             AND cape.presence = 0 
WHERE cape.animal_id IS NULL

UNION ALL 

SELECT 
  cape.continent_code, cape.animal_id 
FROM animals_default_presence AS a 
JOIN continent_animals_presence_exceptions AS cape 
  ON cape.animal_id = a.animal_id 
     AND cape.presence = 1 
WHERE a.presence = 0