创建员工错误数据库：如何实现单类别前3个错误不计入总数的高效查询？

核心思路 #

放弃繁琐的逐个类别算术判断，采用条件聚合+GREATEST()函数简化计算，确保查询逻辑通用、高效，自动适配所有64个错误类别。

表结构明确（基于描述整理，可按需调整） #

• error_categories（错误类别表）
  • error_id（主键）：错误类别唯一标识
  • error_content：错误内容描述
• employee_errors（员工错误统计表）
  • employee_id：员工ID
  • employee_name：员工姓名
  • error_id：关联错误类别ID
  • error_count：该类别下累计错误数
  • month_1~month_12：各月错误明细
  • total_errors：未排除前3条的错误总数

具体查询方案 #

1. 单员工单类别有效错误数 #

用GREATEST()直接处理“错误数≤3时计为0”的逻辑，避免冗余判断：

SELECT
  ee.employee_id,
  ee.employee_name,
  ec.error_content,
  ee.error_count,
  GREATEST(ee.error_count - 3, 0) AS valid_error_count
FROM employee_errors ee
JOIN error_categories ec ON ee.error_id = ec.error_id
WHERE ee.employee_id = '员工1' -- 替换为目标员工ID

2. 单员工所有类别有效错误总数 #

按员工分组聚合，一键算出总有效错误数：

SELECT
  ee.employee_id,
  ee.employee_name,
  SUM(GREATEST(ee.error_count - 3, 0)) AS total_valid_errors
FROM employee_errors ee
WHERE ee.employee_id = '员工1'
GROUP BY ee.employee_id, ee.employee_name

3. 全员工全类别有效错误统计（含明细+个人总数） #

一次性输出所有员工的各类别有效数及个人总有效数：

SELECT
  ee.employee_id,
  ee.employee_name,
  ec.error_content,
  ee.error_count,
  GREATEST(ee.error_count - 3, 0) AS valid_error_count,
  SUM(GREATEST(ee.error_count - 3, 0)) OVER (PARTITION BY ee.employee_id) AS employee_total_valid
FROM employee_errors ee
JOIN error_categories ec ON ee.error_id = ec.error_id
ORDER BY ee.employee_id, ec.error_id

方案优势 #

• 通用适配：无需针对64个类别写专属逻辑，新增类别也无需修改查询
• 性能高效：单次计算完成统计，比嵌套算术/多CASE语句性能更优
• 维护简单：逻辑直观，后续调整排除条数（比如改前2条）仅需修改数字3

示例验证 #

针对你给出的场景：员工1在类别1（14个错误）、类别2（4个）、类别3（8个）
执行查询后，有效数分别为11、1、5，总和17，与预期完全一致。

内容的提问来源于stack exchange，提问作者blacktagged