MySQL/MariaDB：如何查询拥有恰好1辆非马自达汽车的Person？

数据库结构 #

Person表 #

| id |
|----|
| 1  |
| 2  |
| 3  |

Car表 #

| person_id | car   |
|-----------|-------|
| 1         | Ford  |
| 2         | Mazda |
| 3         | Ford  |
| 3         | Mazda |
| 3         | BMW   |

需求 #

编写SQL语句查询出拥有恰好1辆汽车且该汽车不是马自达的Person ID，预期输出为1。

注意事项 #

• 若使用GROUP BY，必须使用完全分组（full group by），不允许修改SQL配置，否则会触发错误：

  "In aggregated query without GROUP BY, expression #1 of SELECT list contains nonaggregated column 'X'; this is incompatible with sql_mode=only_full_group_by"

解决方案 #

方案一：分组统计直接过滤 #

SELECT p.id
FROM Person p
JOIN Car c ON p.id = c.person_id
GROUP BY p.id
HAVING COUNT(c.car) = 1 AND MAX(c.car) != 'Mazda';

说明

1. 通过JOIN关联两张表，获取用户与车辆的对应关系；
2. GROUP BY p.id按用户ID分组，满足完全分组要求；
3. HAVING子句同时验证两个条件：
   • COUNT(c.car) = 1：确认用户仅拥有1辆车；
   • MAX(c.car) != 'Mazda'：因只有1辆车，MAX/MIN结果即为该车品牌，直接排除马自达。

方案二：子查询筛选差集 #

SELECT id
FROM Person
WHERE id IN (
    SELECT person_id
    FROM Car
    GROUP BY person_id
    HAVING COUNT(*) = 1
)
AND id NOT IN (
    SELECT person_id
    FROM Car
    WHERE car = 'Mazda'
);

说明

1. 第一个子查询筛选出所有仅拥有1辆车的用户ID；
2. 第二个子查询筛选出所有拥有马自达汽车的用户ID；
3. 最终取两个集合的差集，得到符合要求的用户ID。

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