我有三个表都与下面的结构相关。
模块分类表:
+------------------+----------------+------------+
| 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
的值进行计数。