SQL如何查询获取指定列值不重复的对应全部行数据

原有写法评估 #

你写的关联查询逻辑正确，可以输出预期结果，核心问题是需要对Color表做两次扫描：一次用于分组聚合统计每个颜色的出现次数，一次用于关联匹配取对应ID，数据量较大时执行效率偏低。另外子查询中不需要返回count(*)字段，仅返回name即可减少不必要的数据传输。

优化后的原有写法版本：

SELECT c3.id, c2.name AS Color
FROM (
    SELECT name
    FROM color 
    GROUP BY name
    HAVING count(*) = 1
) c2 INNER JOIN color c3 on c3.name = c2.name

最优实现方案 #

根据你使用的数据库版本，可以二选一：

1. 现代数据库（支持窗口函数：MySQL8.0+/PostgreSQL/SQL Server等）优先选窗口函数方案 #

仅需一次全表扫描即可完成计算，不需要表关联，执行效率最高，可读性更好：

SELECT id, name AS Color
FROM (
    SELECT 
        id,
        name,
        COUNT(*) OVER (PARTITION BY name) AS name_count
    FROM Color
) t
WHERE name_count = 1;

2. 旧版数据库（如MySQL5.x）选EXISTS子查询方案 #

如果给name字段建了索引，该方案性能远优于原有关联写法：

SELECT id, name AS Color
FROM Color c1
WHERE NOT EXISTS (
    SELECT 1 
    FROM Color c2 
    WHERE c2.name = c1.name AND c2.id != c1.id
);

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