php - mysql left join好像不行

Question

我正在尝试连接两个表。第一个表包含员工信息，如员工编号名称等。第二个表包含时钟信息，如员工编号、状态( checkin 或 checkout )、日期时间行上次更新、 checkin 时间、 checkout 时间、小时数天。

我有这样的东西，但数据库中有真实信息

employees_tbl(
   employee_id INT NOT NULL AUTO_INCREMENT,
   employee_first VARCHAR(30) NOT NULL,
   employee_last VARCHAR(30) NOT NULL,
   PRIMARY KEY ( employee_id )
);
insert into employee (employee_id, employee_first, employee_last) values (1, First, Name);
insert into employee (employee_id, employee_first, employee_last) values (2, Second, Name);
insert into employee (employee_id, employee_first, employee_last) values (3, Third, Name);
insert into employee (employee_id, employee_first, employee_last) values (4, Fourth, Name);
insert into employee (employee_id, employee_first, employee_last) values (5, Fifth, Name);

employee number, status(clocked in or out), datetime row was last updated, time clocked in, time clocked out, hours
transactions_tbl(
   employee_id INT NOT NULL
   status VARCHAR(3) NOT NULL,
   datetime DATETIME NOT NULL,
   clockin TIME,
   clockout TIME,
   hours TIME,
   PRIMARY KEY ( employee_id )
);

INSERT INTO transactions (employee_id, status, datetime, clockin, clockout, hours) VALUES (1, "OUT", "2015-08-10 05:00:00", "2015-08-10 04:00:00", "2015-08-10 05:00:00", "01:00:00");
INSERT INTO transactions (employee_id, status, datetime, clockin, clockout, hours) VALUES (2, "IN", "2015-08-11 05:00:00", "2015-08-11 04:00:00", , );
INSERT INTO transactions (employee_id, status, datetime, clockin, clockout, hours) VALUES (3, "IN", "2015-08-11 05:00:00", "2015-08-10 04:00:00", "2015-08-10 05:00:00", "01:00:00");

我正在寻找的输出是每个员工都应该有他们的名字，如果他们当天打卡上类或下类，则应该显示信息。如果他们当天没有签到或 checkout ，它应该只显示他们最后一次签到或 checkout 的时间。它应该始终显示员工的最后记录，这意味着最近的事件。如果他们从未签到或签退过，它仍应显示他们的姓名。

我使用的查询是:

'SELECT employee_id, employee_first FROM employees e left join (SELECT * FROM transactions ORDER BY datetime DESC) as t1 on e.employee_id = t1.employee_id GROUP BY t1.employee_id'

然而，它只给我前 3 名员工，而没有给第 4 名及以后的员工。

我正在用 mysql 和 php 编写代码。我会在此处将结果显示为表格，但不太清楚如何正确设置表格格式。一切都乱成一团，我什至无法分辨。但希望你能明白这一点。

Answer 1

这是您的查询:

SELECT e.employee_id, e.employee_first
FROM employees e left join
     (SELECT * FROM transactions ORDER BY datetime DESC) as t1
     on e.employee_id = t1.employee_id
GROUP BY t1.employee_id;

它有几个主要错误。第一个是在 GROUP BY 中使用 t1.employee_id。这是来自第二个 表而不是第一个，因此它可能是NULL。其次，您在子查询中有一个 ORDER BY，显然希望它执行某些操作。我还假设您确实需要 t1 中的列。

根据你的描述，我想你想要:

SELECT e.employee_id, e.employee_first, t.*
FROM employees e left join
     (SELECT employee_id, MAX(datetime) as maxdt
      FROM transactions 
      GROUP BY employee_id
     ) tmax
     on e.employee_id = tmax.employee_id left join
     transactions t
     on tmax.employee_id = t.employee_id and tmax.maxdt = t.datetime;