SQL按多值分组筛选同时含A、B两种type的instance实例

现有一张数据表，表结构与样例数据如下：

需要编写SQL SELECT语句，通过多值分组逻辑，筛选出同时拥有"A"、"B"两种type取值的instance，预期查询返回结果为["ins_1"]。

分组聚合写法（推荐，兼容性最强） #

核心思路是按instance分组后，统计组内type为A、B的去重数量，数量等于2即代表两个类型都存在，MySQL、PostgreSQL、Hive、SQL Server等主流SQL引擎都支持该写法：

SELECT instance
FROM target_table -- 替换为实际表名
WHERE type IN ('"A"', '"B"') 
-- 注：样例中type字段存储值自带外层双引号，如果实际存储值为A/B不带引号，将条件改为 IN ('A','B') 即可
GROUP BY instance
HAVING COUNT(DISTINCT type) = 2;

如果业务规则确认同一个instance下不会出现重复的type记录，可以将COUNT(DISTINCT type)替换为COUNT(*)，减少去重计算提升查询效率。

自连接写法 #

如果后续需要同时取出A、B类型对应的value字段，可以使用自连接写法：

SELECT a.instance
FROM (SELECT instance FROM target_table WHERE type = '"A"') a
INNER JOIN (SELECT instance FROM target_table WHERE type = '"B"') b
ON a.instance = b.instance;

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