mysql - mysql 中获取员工最新时间列表的最佳查询是什么？

Question

我有一个巨大的表，每次当我需要其中的特定数据时，数据库都会挂起。我想收集每个员工的最新时间等信息。

数据库就像:

+----------+-------+----------+| emp_name | time  | location |+----------+-------+----------+| Rama     | 1:05  |   aa     || Rama     | 2:10  |   bb     || Arju     | 4:30  |   cc     || Rama     | 6:00  |   dd     |+----------+-------+----------+

And I need output like:

+----------+-------+----------+| emp_name | time  | location |+----------+-------+----------+| Arju     | 4:30  |   cc     || Rama     | 6:00  |   dd     |+----------+-------+----------+

I've tried:
1)

SELECT emp_name, time, location FROM tbl_emp 
WHERE time IN (SELECT max(time) FROM tbl_emp GROUP BY emp_name);

2)

SELECT emp_name, time, location FROM tbl_emp AS att 
LEFT JOIN tbl_emp AS tmp ON tmp.emp_name=att.emp_name AND tmp.time>att.time
WHERE tmp.time is NULL 
GROUP BY att.employee_id;

但这两者都会导致查询太慢。

Answer 1

使用分组依据和聚合

SELECT emp_name, max(time)
FROM tbl_emp
group by emp_name