MYSQL:如何获取table1的具体数据以及获取table2和table3的最新数据

Question

表 1:员工表

+------------+----------+-----------+-------+-------+
| EmployeeID | LastName | FirstName | ID_SC | ID_ES |
+------------+----------+-----------+-------+-------+
| 100000000  | Vallente | Rhea Mae  | 7     | 2     |
+------------+----------+-----------+-------+-------+
| 100000001  | Margallo | Matt      | 7     | 2     |
+------------+----------+-----------+-------+-------+

表 2:tblbadge

+---------+------------+--------------+--------+------+
| BadgeNo | EmployeeID | Deactivation | ID_Rem | ID_S |
+---------+------------+--------------+--------+------+
| 1111111 | 100000001  | 2015-01-02   | 9      | 2    |
+---------+------------+--------------+--------+------+
| 2222222 | 100000001  | 2014-01-02   | 9      | 2    |
+---------+------------+--------------+--------+------+

表 3:员工事件表

+------------+------------+---------------+-------+
| EmployeeID | ADate      | AttritionDate | ID_AT |
+------------+------------+---------------+-------+
| 100000001  | 2015-01-01 | 2015-01-02    | 1     |
+------------+------------+---------------+-------+
| 100000001  | 2014-01-01 | 2014-01-02    | 1     |
+------------+------------+---------------+-------+

输出计划:

+------------+----------+-----------+-------+-------+---------+--------------+--------+------+------------+---------------+-------+
| EmployeeID | LastName | FirstName | ID_SC | ID_ES | BadgeNo | Deactivation | ID_Rem | ID_S | ADate      | AttritionDate | ID_AT |
+------------+----------+-----------+-------+-------+---------+--------------+--------+------+-----------------------+----+-------+
| 100000001  | Margallo | Matt      | 7     | 2     | 1111111 | 2015-01-02   | 9      | 2    | 2015-01-01 | 2015-01-02    | 1     |
+------------+----------+-----------+-------+-------+---------+--------------+--------+------+------------+---------------+-------+

问题:一个查询中最新的 BadgeNo 和 AttritionDate 如何假设 tblemployee 链接到 tblemployeeactivity 同样适用于 tblemployee 和 tblbadge，但是 tblbadge 和 tblemployeeactivity 在 SQL 中没有链接在一起。

我必须在 tblemployee 上获取员工的完整详细信息，并获取每个表上的最新数据(tblbadge 和 tblemployeeactivity)，请参阅输出计划，TIA。

PS. Also I have to filter AttritionDate by Year and Month

Answer 1

此查询可以生成所需的结果:

SELECT e.employeeid, e.lastname, e.firstname, e.id_sc, e.id_es,
       q1.badgeno, q1.deact, q1.id_rem, q1.id_s, 
       q2.adate, q2.attritiondate, q2.id_at
FROM tblemployee e
INNER JOIN 
    (SELECT b.badgeno, b.employeeid, MAX(b.deactivation) AS deact, b.id_rem, b.id_s
     FROM tblbadge b
     GROUP BY b.employeeid) q1
     ON q1.employeeid = e.employeeid
 INNER JOIN
    (SELECT a.employeeid, MAX(a.adate) AS adate, a.attritiondate, a.id_at
     FROM tblemployeeactivity a
     GROUP BY a.employeeid) q2
     ON e.employeeid = q2.employeeid

SQLFiddle:http://sqlfiddle.com/#!9/64a64a/17