You need to enable JavaScript to run this app. MySQL原生实现按列值条件计数 统计员工各生产阶段操作次数的查询方法
优惠活动
大模型
产品
解决方案
定价
更多

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

相关产品推荐
方舟 Agent Plan

超全模态模型 × Harness 升级,最新支持 Deepseek-V4.1-Flash、GLM-5.3 系列、Doubao-Seedream-5.0-pro、Kimi-K3 (部分), 限时 9.9 元起

最近更新时间:2026.11.16 03:15:03