Mysql中的三个表

Question

我有三张桌子--雇员，晋升和惩罚雇员的桌子--这样的结构

Id       int
Fullname     varchar
...............
promotionDate       date

升级的表结构类似于id int emp_id int directorateDate date

惩罚的表格结构是这样的

id                  int
emp_id              int
direcotorateDate    date

假设雇员表有200份记录，每个月有一组员工有晋升(服务一年后)，我想得到当月获得晋升的所有员工的名单，我可以很容易地通过这个查询得到该列表

SELECT * 
FROM employee 
WHERE MONTH(promotionDate) = MONTH(CURRENT_DATE())
   AND YEAR(promotionDate) = YEAR(CURRENT_DATE())

我的问题是，我想从惩罚和晋升表中分别计算出本年度每个员工得到的惩罚和晋升的数量，我做了这个查询，但没有得到正确的结果。

SELECT e.fullname , COUNT(punish.emp_id) as siza ,COUNT(pro.emp_id) as supas
  FROM emp_employee as e
  LEFT JOIN emp_punishment as punish on punish.emp_id=e.id
  LEFT JOIN emp_promotion as pro on e.id=pro.emp_id
 WHERE ((MONTH(e.promotionDate) = MONTH(CURRENT_DATE())
   AND YEAR(e.promotionDate) = YEAR(CURRENT_DATE()))
   AND ( YEAR(punish.directorate_date) = YEAR(CURRENT_DATE()) )
   AND ( YEAR(pro.directorate_date) = YEAR(CURRENT_DATE()) )
 GROUP BY e.fullname;

有什么帮助吗。

Answer 1

通过直接连接这3个表，您将得到重复的行。

Group by emp_id和聚合各自的表emp_punishment和emp_promotion，并将结果连接到表emp_employee。

select e.fullname, coalesce(pu.siza, 0) siza, coalesce(pr.supas, 0) supas
from emp_employee as e 
left join (
  select emp_id, count(*) siza
  from emp_punishment
  where year(directorate_date) = year(CURRENT_DATE)
  group by emp_id
) pu on pu.emp_id = e.id
left join (
  select emp_id, count(*) supas
  from emp_promotion
  where year(directorate_date) = year(CURRENT_DATE)
  group by emp_id
) pr on pr.emp_id = e.id 

我只使用了条件：

where year(directorate_date) = year(CURRENT_DATE())

因为在你的问题中你说：

我想从惩罚和晋升中统计一下今年在获得的惩罚和晋升的人数，。

Answer 2

移除MONTH()函数，并将每个条件移到各自的位置，而不是在WHERE子句中，应该可以解决这个问题(因为，它们被视为具有当前样式的INNER JOIN )。

只将公共列e.promotionDate保留在WHERE子句中：

SELECT e.fullname,
       COUNT(punish.emp_id) as siza ,
       COUNT(pro.emp_id) as supas
  FROM emp_employee as e
  LEFT JOIN emp_punishment as punish 
    ON punish.emp_id=e.id
   AND  YEAR(punish.directorate_date) = YEAR(CURRENT_DATE())
  LEFT JOIN emp_promotion as pro 
    ON e.id=pro.emp_id
   AND YEAR(pro.directorate_date) = YEAR(CURRENT_DATE()))
 WHERE YEAR(e.promotionDate) = YEAR(CURRENT_DATE())
 GROUP BY e.fullname;