我有三个表都与下面的结构相关。
模块分类表:

+------------------+----------------+------------+
| ModuleCategoryID | ModuleCategory | RequireAll |
+------------------+----------------+------------+
|               90 | Cat A          | YES        |
|               91 | Cat B          | NO         |
+------------------+----------------+------------+

模块类别技能表:
+------------------+---------+
| ModuleCategoryID | SkillID |
+------------------+---------+
|               90 |    1439 |
|               90 |    3016 |
|               91 |    1440 |
|               91 |    3016 |
+------------------+---------+

EmployeeSkill Table:
+---------+---------+
| EmpName | SkillID |
+---------+---------+
| Emp1    |    1439 |
| Emp1    |    3016 |
| Emp2    |    1440 |
| Emp2    |    3016 |
| Emp3    |    1439 |
| Emp4    |    3016 |
+---------+---------+

期望输出:
+------------------+-------+
| ModuleCategory   | Count |
+------------------+-------+
|            Cat A |     1 |
|            Cat B |     3 |
+------------------+-------+

我正在尝试按模块类别ID进行分组,并获取具有被跟踪技能的员工的计数。
通常,我可以执行以下查询以获取号码:
select mc.ModuleCategory, Count(*) as Count from ModuleCategory as mc
join ModuleCategorySkill as mcs on mc.ModuleCategoryID = mcs.ModuleCategoryID join EmployeeSkill as es on es.SkillID= mcs.SkillID
group by mc.ModuleCategoryID

但是,我在modulecategory表中有一列requirell,如果设置为“yes”,则只有当员工拥有该类别中的所有技能时,才应将其计为1。如果设置为“否”,则可以正常计算每一行,并按其分组的行数增加计数。
我可以通过为每个modulecategoryid编写单独的查询并使用having count()>1来实现这一点(这将为我找到所有掌握modulecategoryid 90所有技能的人)。如果有3种技能,我就必须将其更改为count()>2。如果没有任何人具有指定的所有技能,则计数应为0。
我需要一种动态的方法来实现这一点,因为有很多数据,并且为每个modulecategoryid编写一个查询不是正确的方法。
另外,我正在使用php,因此我可以循环并创建一个sql字符串来帮助我实现这一点。但我知道我会在拥有大量技能和modulecategoryid的大表上遇到性能问题。
任何关于如何做到这一点的指导都是非常值得赞赏的。

最佳答案

您可以通过加入总类别计数,然后使用条件聚合来完成此操作:

select modulecategory,
       count(case when requireall = 'yes'
               then if(s = t, 1, null)
               else s
             end)
from (
select modulecategory,empname, requireall, count(*) s, min(q.total) t
  from employeeskill e
    inner join modulecategoryskill mcs
      on e.skillid = mcs.skillid
    inner join modulecategory mc
      on mcs.modulecategoryid = mc.modulecategoryid
    inner join (
      select modulecategoryid, count(*) total
        from modulecategoryskill
        group by modulecategoryid
    ) q
    on mc.modulecategoryid = q.modulecategoryid
  group by modulecategory, empname
  ) qq
group by modulecategory;

demo here
这是在假设一个雇员不会被分配两次相同的技能的情况下操作的,如果这是可能发生的事情,这个查询可以改变以支持它,但对我来说,这似乎是一个失败的场景。
我们这里有一个内部查询,它整理我们需要的所有信息(类别名称、员工姓名、是否需要所有技能、每个员工在组中有多少技能以及组中总共有多少技能),外部查询使用条件计数来更改行将根据requireall的值进行计数。

10-08 01:44