拥有2个产品的员工列表

Question

有人能帮帮我吗?

我有表雇员。我想找出拥有2种产品(P1,P2)的员工列表。

Emp_id    Prd_id
E1         P1
E1         P2
E2         P1
E2         P2
E2         P3
E3         P1
E3         P3
E4         P1
E4         P2

因此，我希望输出为

Emp_id
E1
E4

Answer 1

SELECT emp_id
FROM employee
GROUP BY emp_id
HAVING COUNT(CASE WHEN prd_id 'p1' THEN 1 END) > 0 -- p1 exists
AND COUNT(CASE WHEN prd_id 'p2' THEN 1 END) > 0 -- p2 exists
AND COUNT(CASE WHEN prd_id NOT IN ('p1', 'p2') THEN 1 END) = 0 -- but not other

更容易扩展到更多的产品:

SELECT emp_id
FROM 
( -- get a unique list first
SELECT DISTINCT emp_id, prd_id
FROM employee
) AS dt
GROUP BY emp_id
HAVING SUM(CASE WHEN prd_id IN ('p1', 'p2') THEN 1 ELSE -1 END) = 2

Answer 2

如果要求找到拥有任意两种产品的员工，您可以使用having条件来计数:

SELECT   emp_id
FROM     employees
GROUP BY emp_id
HAVING   COUNT(*) = 2

如果这些乘积必须是p1和p2，您可以在其上添加另一个条件:

SELECT   emp_id
FROM     employees
GROUP BY emp_id
HAVING   COUNT(*) = 2 AND
COUNT(CASE WHEN prd_id IN ('p1', 'p2') THEN 1 END) = 2