mysql - SQL查询查询某月员工积分总是小于75

Question

我有一个每周更新一次的员工表，它的成绩从 1 到 100，我需要选择当月(过去四个星期)得分总是低于 75 分的员工。如果该员工一周中的任何一个得分超过 75，则他不应选择。

谁能帮忙，如何使用 SQL 查询找到它？

Employee 表包含姓名、ID、等级、薪水、报告日期。

记录每周插入一次。

每周一插入员工记录，报表日期为周一日期。

现在我需要选择 reportDate 列中显示的一个月(四个星期的数据)中成绩都低于 75 的员工。如果一周内任何一名员工的成绩高于 75，则该员工不符合资格。

'employee', 'CREATE TABLE `employee` (\n  `id` int(11) NOT NULL AUTO_INCREMENT,\n  `name` varchar(255) DEFAULT NULL,\n  `reportDate` date NOT NULL,\n  `salary` double NOT NULL,\n  `grade` int(11) NOT NULL,\n  PRIMARY KEY (`id`)\n) ENGINE=InnoDB DEFAULT CHARSET=utf8'

我正在使用 mysql 数据库。

这里员工 'a' 仅符合条件，因为他在所有四个星期内的成绩都低于 75，'b' 在一周内不符合条件，他的成绩大于 75。我只需要找到符合条件的员工的所有记录。

输出应该是这样的

Answer 1

以下查询从关联关系中选择 id 和 name，该关联关系持有员工在过去四个星期内至少有一个 grade 并且该期间的所有级别都小于 75。

select emp.id, emp.name
from emp
join grade
on emp.id = grade.emp_id and grade.date > DATE_SUB(now(),INTERVAL 28 DAY)
group by emp.id
having max(grade) < 75

编辑:

考虑到表的结构，这是改进后的查询:

select employee.id, employee.name
from employee
where employee.reportDate > DATE_SUB(now(),INTERVAL 28 DAY)
group by employee.id
having max(employee.grade) < 75

如果您遇到任何问题，请告诉我，代码未经测试。

编辑2:

根据对问题所做的编辑，这是新查询:

select emp1.id, emp1.name, emp1.reportDate, emp1.salary, emp1.grade
from employee emp1
where emp1.reportDate > DAT_SUB(now(), INTERVAL 28 DAY)
  and not exists (select emp2.id
                  from emp2
                  where emp1.name = emp2.name
                    and emp2.reportDate > DATE_SUB(now(), INTERVAL 28 DAY)
                    and emp2.grade >= 75)
order by salary desc

但是，您的数据库需要一些 normalization , 要实现正常形式，一个缺陷是 redundancy (重复的 name 和 salary 值)，第二个是在名为 employee 的表中，可以实际识别员工的唯一方法是name，这导致了第三个问题:inconsistency ，特别是当 salary 发生变化时(这会影响订单)；或者当 name 发生变化时(例如，结婚/离婚)，当同一名员工将以两个不同的名字报告时。

数据库结构的建议更改:

• 将 employee 表重命名为 report
• 创建一个employee 表，其中每条记录代表一个员工(id，name, 薪水)
• 向 report 表添加一个外键，它将引用 employee 表
• 从 report table 中删除 name 列
• 创建一个salaries 表，它会有一个id，一个employee_id 这将引用 employee 表和 salary 列
• 从 report table 中删除 salary 列
• 重构所有受影响的代码