Oracle分组和重复检查

Question

我一直在抓我的头。

这个问题让我更接近了，但我的情况更复杂一些。How do I find duplicate values in a table in Oracle?

假设我有一个表EMPLOYEE，我想知道哪些员工在多个部门工作。我的桌子上有员工的身份证，他们工作的部门。员工在多个部门工作时，其员工id将被列为多个记录。我不想只数数列出两次的员工。我需要知道在这个部门列表中有多少工作，而在这个部门列表中有多少工作。

因此，例如，如果我的表是：

 Employee ID | Department
 1             Accounting
 1             Marketing
 2             Accounting
 3             Finance
 4             Programming

部门名单A是

• 会计核算
• 金融

部门名单B是

• 市场营销
• 编程

那么查询的结果将是

 Employee ID | Department
 1             Accounting
 1             Marketing

或

 Employee ID | Count(Department)
 1             2

因为雇员1在A和B名单中的部门工作。

Answer 1

我会用一个映射来处理这个问题：

with mapping as (
      select 'Accounting' as department, 'A' as grouping from dual union all
      select 'Finance' as department, 'A' as grouping from dual union all
      select 'Marketing' as department, 'B' as grouping from dual union all
      select 'Programming' as department, 'B' as grouping from dual
     )
select employeeid, count(distinct m.grouping)
from t join
     mapping m
     on t.department = m.department
group by employeeid;

注意:您可能希望将此信息存储在另一个表中，或者作为一个属性存储在Departments表中。

如果你想让单干部门自己绘制地图：

select t.employeeid, count(distinct coalesce(m.grouping, t.department))
from t join
     mapping m
     on t.department = m.department
group by t.employeeid;

如果希望将不在表中的其他部门分组在一起：

select t.employeeid, count(distinct coalesce(m.grouping, 'UNKNOWN MAPPING'))
from t join
     mapping m
     on t.department = m.department
group by t.employeeid;

Answer 2

如果我理解您想要的是什么，您可以在HAVING子句中使用条件聚合：

SELECT Employee_ID, COUNT(DISTINCT Department) AS Dept_Count
FROM YourTable
GROUP BY Employee_ID
HAVING MAX(CASE WHEN Department IN ('Accounting','Finance') THEN 1 ELSE 0 END) = 1
  AND  MAX(CASE WHEN Department IN ('Marketing','Programming') THEN 1 ELSE 0 END) = 1