SQL查询：如何筛选仅饲养鸟类宠物的客户？

嘿，这问题我熟！要找出只养鸟类宠物的客户，关键是要确保客户养的每一种宠物都属于Bird字段为Y的类型，不能混进非鸟类的。先给你理清楚思路，再上SQL代码：

首先先明确两张表的结构（我整理成更清晰的格式了）：

Pets表 #

customer_pets表 #

核心思路是：把客户的宠物类型拆成单独的行，关联Pets表判断是否为鸟类，最后筛选出所有宠物都是鸟类的客户。

下面针对不同数据库给你写具体的SQL：

适用于PostgreSQL的版本

WITH split_pets AS (
    -- 拆分逗号分隔的宠物类型，同时去掉空格
    SELECT 
        Customer,
        TRIM(pet) AS pet_type
    FROM customer_pets,
         unnest(string_to_array(pet_type, ',')) AS pet
),
customer_bird_check AS (
    -- 关联Pets表，获取每个宠物的鸟类标记，统一大小写避免匹配失败
    SELECT 
        sp.Customer,
        p.Bird
    FROM split_pets sp
    JOIN Pets p ON UPPER(sp.pet_type) = UPPER(p.Pet_Type)
)
-- 分组后筛选：没有非鸟类宠物的客户
SELECT Customer
FROM customer_bird_check
GROUP BY Customer
HAVING COUNT(CASE WHEN Bird != 'Y' THEN 1 END) = 0;

适用于MySQL的版本

MySQL没有直接的字符串拆分函数，用递归CTE来处理：

WITH RECURSIVE split_pets AS (
    -- 初始化拆分，取第一个宠物类型
    SELECT 
        Customer,
        TRIM(SUBSTRING_INDEX(pet_type, ',', 1)) AS pet_type,
        TRIM(SUBSTRING(pet_type, LENGTH(SUBSTRING_INDEX(pet_type, ',', 1)) + 2)) AS remaining
    FROM customer_pets
    UNION ALL
    -- 递归拆分剩余的宠物类型
    SELECT 
        Customer,
        TRIM(SUBSTRING_INDEX(remaining, ',', 1)),
        TRIM(SUBSTRING(remaining, LENGTH(SUBSTRING_INDEX(remaining, ',', 1)) + 2))
    FROM split_pets
    WHERE remaining != ''
),
customer_bird_check AS (
    SELECT 
        sp.Customer,
        p.Bird
    FROM split_pets sp
    JOIN Pets p ON UPPER(sp.pet_type) = UPPER(p.Pet_Type)
)
SELECT Customer
FROM customer_bird_check
GROUP BY Customer
HAVING SUM(CASE WHEN Bird != 'Y' THEN 1 ELSE 0 END) = 0;

运行后就能得到john和brad，正好符合你的预期！

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