mysql使用组的另一种方式

Question

我在做一些时间匹配，但太花时间了。

我有个例子

表aaa

id | emp_id|shift_in               | shift_out             | actual_timein | actual_timout 
1  | 123   | "2014-10-11 01:00:00" | "2014-10-11 01:00:00" | null          | null

表bbb状态0=out : 1=in

id| emp_id | status | timelog | 
1 | 123    | 1      |  "2014-10-11 01:03:00"
2 | 123    | 1      |  "2014-10-11 00:48:00"
3 | 123    | 1      |  "2014-10-10 21:14:00"
4 | 123    | 1      |  "2014-10-10 23:47:00"
1 | 123    | 1      |  "2014-10-11 08:01:00"
1 | 123    | 1      |  "2014-10-11 08:02:00"
1 | 123    | 1      |  "2014-10-11 08:03:00"

我所做的是更新表bbb。

actual_timein，然后是actual_timeout的另一条语句

UPDATE aaa c
  INNER JOIN(
      select *, MIN(time_log) as my_time from aaa w
         inner join bbb b on b.emp_id = a.emp_id and status = 1
and time_log < shift_in and TIME_TO_SEC(TIMEDIFF(a.shift_in, b.time_log))/3600  < 2
where b.timelog between '2014-10-01' and  '2014-10-31'
group by a.emp_id order by b.timelog asc
    ) d on d.emp_id = c.emp_id 
set c.actual_timein = d.my_time

假设代码有效:) ..。

还有别的办法吗？

如果我有200名员工，表中每15天将有3000名员工，附表200*15。

谢谢..。

Answer 1

这是您的查询：

UPDATE aaa c INNER JOIN
       (select *, MIN(time_log) as my_time
        from aaa w inner join
             bbb b
             on b.emp_id = a.emp_id and b.status = 1 and time_log < shift_in and 
                TIME_TO_SEC(TIMEDIFF(a.shift_in, b.time_log))/3600  < 2
        where b.timelog between '2014-10-01' and '2014-10-31'
        group by a.emp_id
        order by b.timelog asc
      ) d
      on d.emp_id = c.emp_id 
    set c.actual_timein = d.my_time;

初始观察：*是不必要的(在聚合查询中也是不明智的)。order by是不必要的。所以，试试这个版本：

UPDATE aaa c INNER JOIN
       (select a.emp_id MIN(time_log) as my_time
        from aaa w inner join
             bbb b
             on b.emp_id = a.emp_id and b.status = 1 and b.time_log < shift_in and 
                TIME_TO_SEC(TIMEDIFF(a.shift_in, b.time_log))/3600  < 2
        where b.timelog between '2014-10-01' and '2014-10-31'
        group by a.emp_id
      ) d
      on d.emp_id = c.emp_id 
    set c.actual_timein = d.my_time;

其次，索引可能会有所帮助。对于内部查询，我建议使用bbb(status, time_log, emp_id)。