Несколько условий MySQL для предложения Group By /
У меня есть три таблицы, которые все взаимосвязаны со следующей структурой.
Таблица категорий модуля:
+------------------+----------------+------------+
| ModuleCategoryID | ModuleCategory | RequireAll |
+------------------+----------------+------------+
| 90 | Cat A | YES |
| 91 | Cat B | NO |
+------------------+----------------+------------+
Таблица ModuleCategorySkill:
+------------------+---------+
| 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 |
+------------------+-------+
Я пытаюсь сгруппировать по ModuleCategoryID и получить количество сотрудников, которые отслеживают навыки.
Обычно я могу сделать следующий запрос, чтобы получить числа:
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
Тем не менее, у меня есть столбец RequireAll в таблице ModuleCategory, который, если он установлен на «ДА», должен считать сотрудников только как 1, только если у них есть все навыки в категории. Если для этого параметра установлено значение NO, он может считать каждую строку в обычном порядке и увеличивать количество на количество строк, по которым он группирует.
Я могу добиться этого, написав отдельные запросы для каждого modulecategoryID и используя счетчик ()> 1 (который найдет для меня любого, кто обладает всеми навыками для ModuleCategoryID 90). Если бы было 3 навыка, то мне пришлось бы изменить его на Имеющий счет ()> 2. Если нет никого, кто имеет все указанные навыки, счет должен быть 0.
Мне нужен динамический способ сделать это, так как существует много данных, и написание одного запроса для каждого ModuleCategoryID не является правильным подходом.
Кроме того, я использую PHP, поэтому я могу перебрать и создать строку SQL, которая может помочь мне достичь этого. Но я знаю, что у меня возникнут проблемы с производительностью больших таблиц с большим количеством навыков и ID модуля.
Любое руководство о том, как этого добиться, высоко ценится.
Решение
Вы можете сделать это, присоединившись к общему количеству категорий, а затем используя условное агрегирование:
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;
Это работает в предположении, что сотруднику не будет назначено одно и то же умение дважды, если это может произойти, этот запрос может быть изменен, чтобы поддержать его, но мне кажется, что это неправильный сценарий.
Здесь у нас есть внутренний запрос, в котором собрана вся необходимая информация (имя категории, имя сотрудника, требуются ли все навыки, сколько навыков в группе на одного сотрудника и сколько их в общей группе), с внешним запросом, который использует условный счетчик для изменения порядка подсчета строк на основе значения requireall,
Другие решения
Других решений пока нет …