如何查询数据库表中包含指定所有类型的记录？

嘿，这个需求我经常遇到，咱们可以用几种高效的SQL写法来实现，刚好结合你的例子（要找包含A、B、C全部类型的记录）给你拆解一下：

方法1：GROUP BY + HAVING COUNT（最经典高效） #

这是最常用的方案，适合大多数场景，尤其是当你有name和type的联合索引时，性能会非常好。假设你的表名为records，字段是name（记录名称）和type（类型）：

SELECT name
FROM records
WHERE type IN ('A', 'B', 'C')  -- 先过滤出目标类型相关的行
GROUP BY name
HAVING COUNT(DISTINCT type) = 3;  -- 确保分组包含全部3种目标类型

逻辑解释： #

• WHERE子句先筛掉和目标类型无关的行，减少后续计算量；
• GROUP BY name把同一记录的所有类型行聚合到一起；
• HAVING COUNT(DISTINCT type) = 3确保这个分组里刚好包含3种不同的目标类型（因为我们要的是A、B、C全部存在）。

对应你的例子：

• "First"记录的分组过滤后有A、B、C，COUNT结果是3，符合条件；
• "Second"记录的分组只有A、B，COUNT结果是2，被排除。

方法2：多EXISTS子查询（适合类型数量少的场景） #

如果你的目标类型数量不多，或者需要返回记录的其他字段而不想用GROUP BY，可以用多个EXISTS来逐一验证每个类型是否存在：

SELECT DISTINCT r.name
FROM records r
WHERE EXISTS (SELECT 1 FROM records r1 WHERE r1.name = r.name AND r1.type = 'A')
  AND EXISTS (SELECT 1 FROM records r1 WHERE r1.name = r.name AND r1.type = 'B')
  AND EXISTS (SELECT 1 FROM records r1 WHERE r1.name = r.name AND r1.type = 'C');

逻辑解释： #

每个EXISTS子查询都会检查当前记录是否包含对应的类型，只有当三个条件全部满足时，这条记录才会被选中。用DISTINCT是因为一条记录可能对应多个type行，避免重复返回。

方法3：窗口函数（适合需要返回完整记录的场景） #

如果你需要返回记录的所有字段，而不仅仅是name，可以用窗口函数来统计每个记录的目标类型数量：

WITH filtered_records AS (
  SELECT 
    *,
    COUNT(DISTINCT type) OVER (PARTITION BY name) AS target_type_count
  FROM records
  WHERE type IN ('A', 'B', 'C')
)
SELECT DISTINCT *  -- 返回所有字段，DISTINCT去重
FROM filtered_records
WHERE target_type_count = 3;

逻辑解释： #

• 先通过CTE给每个记录（按name分组）计算符合目标类型的数量；
• 然后筛选出数量等于目标类型数的记录，再去重得到最终结果。

最优方案选择 #

• 如果只需要返回记录名称（或分组字段），GROUP BY + HAVING是最优选择，性能最高；
• 如果目标类型数量很少（比如3个以内），EXISTS子查询的可读性更好，性能也不错；
• 如果需要返回完整的记录字段，窗口函数的写法更灵活，不需要额外处理分组后的字段聚合。

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