如何使用多个AND条件，其中我使用内部连接组合了三个表

how to use multiple And condition where I combined three tables using inner join(如何使用多个AND条件，其中我使用内部连接组合了三个表)

问题描述

SELECT z.name, a.name, a.type, a.gender,
(
    SELECT
    COUNT(a.type)
    FROM animal a
 )
FROM zoo z
INNER JOIN zoo_animal_map m 
ON z.id = m.zoo_id
INNER JOIN animal a 
ON a.id = m.animal_id
WHERE a.type="Tiger" AND a.type ="Elephant" AND a.type =" Leopard";

推荐答案

我认为您需要IN

SELECT 
  zoo.name   AS zoo_name
, ani.type   AS animal_type
, ani.gender AS animal_gender
, ani.name   AS animal_name
FROM zoo_animal_map AS map
JOIN zoo AS zoo
  ON zoo.id = map.zoo_id
JOIN animal AS ani 
  ON ani.id = map.animal_id
WHERE ani.type IN ('Tiger', 'Elephant', 'Leopard')
ORDER BY zoo.name, ani.type, ani.gender, ani.name

没有既是老虎又是大象的动物。
不过，我不太确定是不是有豹子。

但是如果您想要查找所有3种类型的动物园呢？
然后按动物园分组，使用条件聚合可能适合您。

SELECT *
FROM
(
    SELECT 
      map.zoo_id
    , zoo.name AS zoo_name
    , COUNT(CASE 
            WHEN ani.type = 'Tiger'
            THEN ani.id
            END) AS Tigers
    , COUNT(CASE 
            WHEN ani.type = 'Elephant'
            THEN ani.id
            END) AS Elephants
    , COUNT(CASE 
            WHEN ani.type = 'Leopard'
            THEN ani.id
            END) AS Leopards
    , COUNT(CASE 
            WHEN ani.type = 'Tiger' 
             AND ani.gender LIKE 'F%' 
            THEN ani.id
            END) AS FemaleTigers
    , COUNT(CASE 
            WHEN ani.type = 'Elephant' 
             AND ani.gender LIKE 'F%' 
            THEN ani.id
            END) AS FemaleElephants
    , COUNT(CASE 
            WHEN ani.type = 'Leopard'
             AND ani.gender LIKE 'F%' 
            THEN ani.id
            END) AS FemaleLeopards
    , COUNT(DISTINCT ani.type) AS AnimalTypes
    FROM zoo_animal_map AS map
    JOIN zoo AS zoo
      ON zoo.id = map.zoo_id
    JOIN animal AS ani 
      ON ani.id = map.animal_id
    GROUP BY map.zoo_id, zoo.name
) AS zoos
WHERE Tigers > 0
  AND Elephants > 0
  AND Leopards > 0
ORDER BY zoo_name

db<；>；小提琴演示here

这篇关于如何使用多个AND条件，其中我使用内部连接组合了三个表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持编程学习网！