mysql - 根据每个名称的最旧时间戳选择行

Question

我正在尝试根据每个名称的最旧时间戳来选择行。我提到了这个solution但问题是我的表没有 id 列。我试图添加一个临时自动递增 id 列，但不确定该怎么做。基本上，当时间戳相同时，我想为给定名称选择任何一条可用记录。有人可以帮我解决这个问题吗。

mysql> select * from tblemployee order by name;
+------+-------------+---------------------+
| name | day_of_week | signin_date         |
+------+-------------+---------------------+
| bob  | wednesday   | 2017-08-11 08:11:30 |
| bob  | thursday    | 2017-06-11 11:21:30 | << same timestamp
| bob  | saturday    | 2017-08-28 09:01:30 |
| bob  | wednesday   | 2017-08-11 08:11:30 |
| bob  | monday      | 2017-06-11 11:21:30 | << same timestamp
| tom  | wednesday   | 2017-08-28 23:01:20 |
| tom  | sunday      | 2017-08-29 09:01:30 |
+------+-------------+---------------------+

我的预期结果是这样的

对于“汤姆”

+------+-------------+---------------------+
| name | day_of_week | signin_date         |
+------+-------------+---------------------+
| tom  | wednesday   | 2017-08-28 23:01:20 |

对于 'bob' 因为有两条记录具有相同的时间戳，我可以选择任何一条，而不管 day_of_week。

+------+-------------+---------------------+
| name | day_of_week | signin_date         |
+------+-------------+---------------------+
| bob  | thursday    | 2017-06-11 11:21:30 |

                    (or)

| bob  | monday      | 2017-06-11 11:21:30 |

Answer 1

您可以对 min(signin_date) 的组值使用连接，例如:

  select distinct a.name, a.day_of_week , a.signin_date         
  from tblemployee a
  inner join (
    select name, min(signin_date) as min_date
    from tblemployee
    group by name
  ) t on t.min_date = a.signin_date and a.name = t.name 

如果你有关于 bob the min(signin_date) i 与两个不同日期相关的模糊行，那么你应该使用其中一个

  select a.name, min(a.day_of_week) , a.signin_date         
  from tblemployee a
  inner join (
    select name, min(signin_date) as min_date
    from tblemployee
    group by name
  ) t on t.min_date = a.signin_date and a.name = t.name 
  group by a.name,a.signin_date