如何用单条SQL查询拥有至少两辆不同颜色车辆的所有者的全部车辆牌照号

问题解答 #

该需求完全可以通过单条SQL语句实现，核心思路是先筛选出满足「至少拥有2种不同颜色车辆」的车主ID，再关联原表拿到这些车主的全部车辆号牌信息即可，以下是两种常见实现方案：

方案1：子查询分组筛选（兼容所有支持标准SQL的数据库）

先通过分组聚合筛选符合条件的车主ID，再关联原表获取全量车辆信息：

SELECT v.OWNER_ID, v.LICENSE_PLATE_NUMBER
FROM VEHICLE v
INNER JOIN (
    -- 筛选出至少有2种不同颜色车辆的车主ID
    SELECT OWNER_ID
    FROM VEHICLE
    GROUP BY OWNER_ID
    HAVING COUNT(DISTINCT COLOR) >= 2
) qualified_owners ON v.OWNER_ID = qualified_owners.OWNER_ID

如果需要同时返回车主的姓名等存储在PERSON表的信息，额外增加和PERSON表的关联即可。

方案2：窗口函数实现（支持MySQL 8.0+、PostgreSQL、Oracle等现代数据库）

用窗口函数逐行计算对应车主的不同颜色车辆总数，直接过滤符合条件的记录：

SELECT OWNER_ID, LICENSE_PLATE_NUMBER
FROM (
    SELECT 
        OWNER_ID,
        LICENSE_PLATE_NUMBER,
        -- 按车主ID分区，统计每个车主的不同颜色车辆数量
        COUNT(DISTINCT COLOR) OVER (PARTITION BY OWNER_ID) AS color_cnt
    FROM VEHICLE
) temp
WHERE color_cnt >= 2

两种方案都完全匹配需求：举例中Jack的不同颜色数为2，会返回他名下全部3辆车的号牌；Fred的不同颜色数为1，不会出现在结果集中。

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