SQL实现：获取动物园动物/鸟类ID及anml_bird_flag字段

需求概述 #

从Zoo、Animal、Birds三张表中查询包含zoo_id、country、animal_id、birds_id以及anml_bird_flag的结果集，其中：

• anml_bird_flag = 1：该动物园同时拥有动物和鸟类
• anml_bird_flag = 0：该动物园缺少动物或鸟类中的一种

已通过zoo_id关联三张表得到前4列，需实现anml_bird_flag的逻辑。

表结构 #

Zoo表 #

Animal表 #

Birds表 #

解决方案 #

以下两种方式均可实现anml_bird_flag的逻辑：

方法1：CASE WHEN结合EXISTS子查询 #

直接在主查询中判断当前zoo_id是否在Animal和Birds表中都有记录：

SELECT
    z.zoo_id,
    z.country,
    a.id AS animal_id,
    b.id AS birds_id,
    CASE
        WHEN EXISTS (SELECT 1 FROM Animal an WHERE an.zoo_id = z.zoo_id)
             AND EXISTS (SELECT 1 FROM Birds br WHERE br.zoo_id = z.zoo_id)
        THEN 1
        ELSE 0
    END AS anml_bird_flag
FROM Zoo z
LEFT JOIN Animal a ON z.zoo_id = a.zoo_id
LEFT JOIN Birds b ON z.zoo_id = b.zoo_id
WHERE a.id IS NOT NULL OR b.id IS NOT NULL; -- 过滤既无动物也无鸟类的动物园

方法2：预统计状态后关联查询 #

先通过子查询统计每个动物园的动物/鸟类存在状态，再关联到主查询：

WITH ZooStatus AS (
    SELECT
        zoo_id,
        CASE WHEN EXISTS (SELECT 1 FROM Animal an WHERE an.zoo_id = z.zoo_id) THEN 1 ELSE 0 END AS has_animal,
        CASE WHEN EXISTS (SELECT 1 FROM Birds br WHERE br.zoo_id = z.zoo_id) THEN 1 ELSE 0 END AS has_bird
    FROM Zoo z
)
SELECT
    z.zoo_id,
    z.country,
    a.id AS animal_id,
    b.id AS birds_id,
    CASE WHEN zs.has_animal = 1 AND zs.has_bird = 1 THEN 1 ELSE 0 END AS anml_bird_flag
FROM Zoo z
JOIN ZooStatus zs ON z.zoo_id = zs.zoo_id
LEFT JOIN Animal a ON z.zoo_id = a.zoo_id
LEFT JOIN Birds b ON z.zoo_id = b.zoo_id
WHERE a.id IS NOT NULL OR b.id IS NOT NULL;

结果验证 #

执行上述SQL后，会得到与期望一致的输出：

若存在仅含动物或仅含鸟类的动物园，对应anml_bird_flag会显示0。

内容的提问来源于stack exchange，提问作者Raven Smith