如何编写SQL查询筛选同时关联Prod1和Prod2的账户数据

现有如下账户-产品关联表：

需要筛选出同时关联了Prod1和Prod2的账户，最终仅保留这些账户关联Prod1、Prod2的记录，结果如下：

请问如何编写SQL实现该需求？

方法1：子查询筛选符合条件的账户 #

先找出同时拥有Prod1和Prod2的账户，再关联原表获取对应产品记录（把your_table替换成你的实际表名）：

SELECT t.Account, t.Product
FROM your_table t
WHERE t.Account IN (
    SELECT Account
    FROM your_table
    WHERE Product IN ('Prod1', 'Prod2')
    GROUP BY Account
    HAVING COUNT(DISTINCT Product) = 2
)
AND t.Product IN ('Prod1', 'Prod2');

逻辑说明 #

• 子查询里先过滤出产品为Prod1或Prod2的记录，按账户分组后，用COUNT(DISTINCT Product)=2确保该账户同时持有两个目标产品；
• 外层查询只保留这些账户的Prod1、Prod2记录，自动排除其他无关产品。

方法2：自连接匹配 #

通过两次自连接找到同时关联Prod1和Prod2的账户，再拉取对应记录：

SELECT t.Account, t.Product
FROM your_table t
JOIN (
    SELECT a.Account
    FROM your_table a
    JOIN your_table b ON a.Account = b.Account
    WHERE a.Product = 'Prod1' AND b.Product = 'Prod2'
) valid_accounts ON t.Account = valid_accounts.Account
WHERE t.Product IN ('Prod1', 'Prod2');

逻辑说明 #

• 子查询通过自连接，匹配出同时存在Prod1和Prod2记录的账户；
• 外层查询基于这些有效账户，筛选出目标产品的记录。

方法3：窗口函数统计（适用于MySQL 8+、PostgreSQL等支持窗口函数的数据库） #

用窗口函数按账户统计目标产品数量，再筛选符合条件的记录：

WITH account_product_stats AS (
    SELECT 
        Account, 
        Product,
        COUNT(CASE WHEN Product IN ('Prod1', 'Prod2') THEN 1 END) OVER (PARTITION BY Account) AS target_product_count
    FROM your_table
)
SELECT Account, Product
FROM account_product_stats
WHERE target_product_count = 2 AND Product IN ('Prod1', 'Prod2');

逻辑说明 #

• 先通过窗口函数按账户分组，统计每个账户拥有Prod1/Prod2的总数量；
• 最后筛选出数量为2（即同时持有两个产品）且产品是Prod1/Prod2的记录。

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