如何查询employee表中仅持有P1、P2两款产品的员工ID

需求说明 #

现有employee表存储员工ID与关联产品ID的对应关系，需要筛选仅持有P1、P2两款产品，且没有其他关联产品的员工ID。

实现方法 #

方法1：分组聚合校验（兼容性最高，支持所有主流数据库） #

核心逻辑：按员工ID分组后校验两个条件：

• 该员工关联的不同产品总数恰好为2
• 该员工没有关联P1、P2之外的其他产品

SQL语句如下：

SELECT Emp_id
FROM employee
GROUP BY Emp_id
HAVING COUNT(DISTINCT Prd_id) = 2
   AND SUM(CASE WHEN Prd_id NOT IN ('P1', 'P2') THEN 1 ELSE 0 END) = 0;

如果表中同一个员工不会重复关联同一款产品，可去掉COUNT函数中的DISTINCT关键字提升查询效率

如果你使用MySQL数据库，也可以用简化写法：

SELECT Emp_id
FROM employee
GROUP BY Emp_id
HAVING COUNT(DISTINCT Prd_id) = 2
   AND MAX(Prd_id IN ('P1', 'P2')) = 1;

方法2：正向匹配+反向排除（逻辑更直观） #

通过子查询排除持有其他产品的员工，再校验是否同时持有P1、P2两款产品：

SELECT DISTINCT Emp_id
FROM employee e1
WHERE Prd_id IN ('P1', 'P2')
  AND NOT EXISTS (
    SELECT 1 
    FROM employee e2 
    WHERE e2.Emp_id = e1.Emp_id 
      AND e2.Prd_id NOT IN ('P1', 'P2')
)
GROUP BY Emp_id
HAVING COUNT(DISTINCT Prd_id) = 2;

输出结果 #

以上语句执行后将返回符合要求的员工ID：

Emp_id
E1
E4

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