查询所有以my开头分组中均存在的content的SQL实现

先把你的表结构和初始化数据整理得更清晰一点（注意group是SQL关键字，定义表的时候最好加引号转义）：

/* 创建test表 */
CREATE TABLE test(
    Id integer PRIMARY KEY,
    "group" text,
    content text
);

/* 插入初始化数据 */
INSERT INTO test VALUES(1,'mygroup','foobar');
INSERT INTO test VALUES(2,'myothergroup','foobar');
INSERT INTO test VALUES(3,'myothergroup','foobaz');
INSERT INTO test VALUES(4,'gr1','foobaz');
INSERT INTO test VALUES(5,'gr0','foobaz');
COMMIT;

要实现“找出所有在所有以‘my’开头的group分组中都存在的content值”这个需求，核心思路是先确定目标分组的总数，再筛选出在每个目标分组里都出现的content，对应的SQL语句如下：

SELECT content
FROM test
WHERE "group" LIKE 'my%'
GROUP BY content
HAVING COUNT(DISTINCT "group") = (
    -- 统计所有以my开头的分组数量
    SELECT COUNT(DISTINCT "group")
    FROM test
    WHERE "group" LIKE 'my%'
);

语句解释： #

1. 子查询部分：先算出所有以my开头的分组总数，这里你的数据里是2个（mygroup和myothergroup）。
2. 主查询过滤：先筛选出所有属于my开头分组的记录，排除掉gr1、gr0这些无关分组的数据。
3. 分组统计：按content分组，统计每个content出现在多少个不同的my开头分组里。
4. 筛选结果：用HAVING子句选出那些统计数等于分组总数的content——这就意味着这个content在每一个my开头的分组里都存在。

执行这个查询后，会返回foobar，因为它在mygroup和myothergroup里都有；而foobaz只在myothergroup里出现，所以不会被选中，完全符合需求。

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