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

Question

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";

Answer 1

我认为您需要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

没有既像老虎又像大象的动物
但不确定这些豹子。

但是，如果你想找到所有三种类型的动物园
然后按zoo进行分组，使用条件聚合可能对您有用。

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

zoo_name		野生动物园
野生动物园
The Wild Zoo

您的问题与联接无关，您有三个AND条件相互矛盾
只需将其替换为OR，它就会在中工作