如何编写SQL查询剔除存在重复字段值的分组下的全部记录

现有user_colour表数据如下：

Name      Colour
Mark      Red
Mark      Yellow
Mark      Red
Sarah     Blue
Sarah     White

需要按Name字段分组，若某分组内的Colour字段存在重复值，则剔除该分组的全部记录，仅返回无重复Colour值的分组的所有记录。上述示例的期望返回结果如下：

Sarah     Blue
Sarah     White

通用兼容写法（支持所有主流SQL数据库） #

SELECT Name, Colour
FROM user_colour
WHERE Name IN (
    SELECT Name
    FROM user_colour
    GROUP BY Name
    HAVING COUNT(DISTINCT Colour) = COUNT(*)
);

逻辑说明：子查询先对Name分组，通过判断去重后的Colour数量等于分组总记录数，筛选出无重复Colour的Name列表，外层查询再取出这些Name对应的全量记录。

窗口函数写法（适用于MySQL 8.0+、PostgreSQL、SQL Server、Oracle等支持窗口函数的数据库） #

WITH colour_stat AS (
    SELECT
        Name,
        Colour,
        COUNT(*) OVER(PARTITION BY Name) AS total_num,
        COUNT(DISTINCT Colour) OVER(PARTITION BY Name) AS distinct_num
    FROM user_colour
)
SELECT Name, Colour
FROM colour_stat
WHERE total_num = distinct_num;

逻辑说明：通过CTE一次扫描全表统计每个Name分组的总记录数和去重Colour数量，直接筛选符合条件的记录即可，性能优于子查询写法。

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