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和名称，该关系让员工在过去四个星期中至少拥有一个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

编辑：

考虑到table的结构，这是改进的查询：

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

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

EDIT2：

根据问题所做的编辑，这是一个新的查询：

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

但是，您的数据库需要一些归一化，要实现正常形式，一个缺陷是冗余度 (重复name和salary值)，第二个缺陷是，在一个名为employee的表中，实际可以识别员工的唯一方法是name，这导致了第三个问题：不一致，特别是当salary被更改(这将影响顺序)；或者当name被更改(例如，婚礼/离婚)时，相同的员工将以两个不同的名字报告。

建议对数据库结构进行更改：

• 将employee表重命名为report
• create a employee table，其中每条记录代表员工(id、name、salary)
• 将foreign key添加到report表中，该表将引用employee表
• 从name table中删除table列
• create a salaries table，它将有一个id，一个引用employee表的employee_id和一个salary列
• 从salary table中删除table列
• 重构所有受影响的代码

Answer 2

使用带有WHERE函数的DATEDIFF子句中的子查询来定义日期范围。

SELECT name
FROM employeetable
WHERE (SELECT grade 
       FROM employeetable 
       WHERE AND DATEDIFF(mm,datefield, GETDATE())=1
       AND DATEDIFF(yy,datefield, GETDATE())=0) < 75
OR (((SELECT grade 
      FROM employeetable 
      WHERE AND DATEDIFF(ww,datefield, GETDATE())=1
      AND DATEDIFF(yy,datefield, GETDATE())=0) < 75)
    AND
    ((SELECT grade 
      FROM employeetable 
      WHERE AND DATEDIFF(ww,datefield, GETDATE())=2
      AND DATEDIFF(yy,datefield, GETDATE())=0) < 75)
    AND
    ((SELECT grade 
      FROM employeetable 
      WHERE AND DATEDIFF(ww,datefield, GETDATE())=3
      AND DATEDIFF(yy,datefield, GETDATE())=0) < 75)
    AND
    ((SELECT grade 
      FROM employeetable 
      WHERE AND DATEDIFF(ww,datefield, GETDATE())=4
      AND DATEDIFF(yy,datefield, GETDATE())=0) < 75))