MySQL原生实现按列值条件计数 统计员工各生产阶段操作次数的查询方法
可完全通过MySQL原生能力实现,无需拉取全量数据到后端计算
你提出的使用CASE语句实现条件判断的思路完全可行,这是SQL场景下经典的**条件聚合(行转列统计)**实现方式,不需要把全量操作记录拉到后端做循环计数。
实现核心逻辑
- 打通表关联链路:通过
entries.Machine = machine.MachineID关联,即可拿到每一条操作记录对应的机器所属生产阶段,该阶段值和stage表的StageName一一对应 - 按员工ID做分组聚合,为每个生产阶段配置独立的计数判断:当操作记录匹配到对应阶段时计1,未匹配到时计0,最终聚合出每个员工在各阶段的操作次数
- 因为需要统计stage表中所有生产阶段、哪怕员工0操作也要展示对应列,基于固定阶段名写CASE判断的静态查询性能最优,也最易维护
可直接运行的查询语句
SELECT e.Employee, SUM(CASE WHEN m.MachineType = 'Pre-Inspection' THEN 1 ELSE 0 END) AS `Pre-Inspection`, SUM(CASE WHEN m.MachineType = 'Pre-Washing (Jig-Washer)' THEN 1 ELSE 0 END) AS `Pre-Washing (Jig-Washer)`, SUM(CASE WHEN m.MachineType = 'Stripping (Shot)' THEN 1 ELSE 0 END) AS `Stripping (Shot)`, SUM(CASE WHEN m.MachineType = 'Stripping (Liquid)' THEN 1 ELSE 0 END) AS `Stripping (Liquid)`, SUM(CASE WHEN m.MachineType = 'WPC-SKH' THEN 1 ELSE 0 END) AS `WPC-SKH`, SUM(CASE WHEN m.MachineType = 'WPC-Ceramic' THEN 1 ELSE 0 END) AS `WPC-Ceramic`, SUM(CASE WHEN m.MachineType = 'Pre-Lapping (SMAP)' THEN 1 ELSE 0 END) AS `Pre-Lapping (SMAP)`, SUM(CASE WHEN m.MachineType = 'Pre-Lapping (AERO)' THEN 1 ELSE 0 END) AS `Pre-Lapping (AERO)`, SUM(CASE WHEN m.MachineType = 'Pre-Lapping (Hand Lapping)' THEN 1 ELSE 0 END) AS `Pre-Lapping (Hand Lapping)`, SUM(CASE WHEN m.MachineType = 'Pre-Washing (F1 Clean Washer)' THEN 1 ELSE 0 END) AS `Pre-Washing (F1 Clean Washer)`, SUM(CASE WHEN m.MachineType = 'Coating (M1) - Set/Disassemble' THEN 1 ELSE 0 END) AS `Coating (M1) - Set/Disassemble`, SUM(CASE WHEN m.MachineType = 'Coating (M2) - Set/Disassemble' THEN 1 ELSE 0 END) AS `Coating (M2) - Set/Disassemble`, SUM(CASE WHEN m.MachineType = 'Coating (M3) - Set/Disassemble' THEN 1 ELSE 0 END) AS `Coating (M3) - Set/Disassemble`, SUM(CASE WHEN m.MachineType = 'Post-Inspection' THEN 1 ELSE 0 END) AS `Post-Inspection`, SUM(CASE WHEN m.MachineType = 'Post-Lapping (SMAP)' THEN 1 ELSE 0 END) AS `Post-Lapping (SMAP)`, SUM(CASE WHEN m.MachineType = 'Post-Lapping (AERO)' THEN 1 ELSE 0 END) AS `Post-Lapping (AERO)`, SUM(CASE WHEN m.MachineType = 'Post-Lapping (Hand Lapping)' THEN 1 ELSE 0 END) AS `Post-Lapping (Hand Lapping)`, SUM(CASE WHEN m.MachineType = 'Final Inspection' THEN 1 ELSE 0 END) AS `Final Inspection`, SUM(CASE WHEN m.MachineType = 'Packing/Shipping' THEN 1 ELSE 0 END) AS `Packing/Shipping` FROM entries e INNER JOIN machine m ON e.Machine = m.MachineID GROUP BY e.Employee;
结果说明
基于给定的表关联逻辑运行上述SQL,可准确匹配每条操作记录对应的生产阶段:
- 所有MachineID为1的操作会被归类到Pre-Inspection阶段计数,MachineID为2的归类到Pre-Washing (Jig-Washer),以此类推,和machine表中维护的MachineType与stage表StageName的一一对应关系完全一致
- 所有计数逻辑都是基于数据库内的关联规则自动计算,不需要手动遍历记录做累加
可选扩展
如果后续生产阶段会频繁新增、不想手动修改SQL中的CASE判断逻辑,可以通过MySQL动态SQL拼接的方式自动生成统计列,不需要每次调整查询代码,但对于固定的阶段列表,上述静态查询性能更好、维护成本更低。
内容的提问来源于stack exchange,提问作者Isaiah M Fisher
相关产品推荐
相关产品推荐

