如何在SQLite数据库中查询拥有指定所有邮箱的同名数据行

需要从指定表中，查询所有满足**同一name对应的邮箱同时包含"d@example.com"和"a@example.com"**的数据行，预期返回id为1和4的两行。

表结构与示例数据 #

假设表名为user_info，数据如下：

解决方案 #

可以通过以下SQL语句实现需求：

SELECT u.*
FROM user_info u
JOIN (
    SELECT name
    FROM user_info
    WHERE email IN ('d@example.com', 'a@example.com')
    GROUP BY name
    HAVING COUNT(DISTINCT email) = 2
) qualified_names ON u.name = qualified_names.name
WHERE u.email IN ('d@example.com', 'a@example.com');

逻辑解释 #

1. 子查询部分：先过滤出邮箱属于目标列表的行，按name分组后，通过COUNT(DISTINCT email) = 2确保该name同时拥有两个指定邮箱（用DISTINCT避免同一邮箱重复计数的情况），得到符合条件的name集合。
2. 主查询：将原表与合格name集合关联，再过滤出邮箱属于目标列表的行，最终得到所有符合要求的数据行。

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