按用户选择最近日期的行

Question

我有一个名为“lms_attendance”的表格，记录了用户签到和签退的时间，如下所示：

id  user    time    io (enum)
1   9   1370931202  out
2   9   1370931664  out
3   6   1370932128  out
4   12  1370932128  out
5   12  1370933037  in

我希望创建一个视图，该视图仅输出每个用户ID的最新记录，并提供“in”或“out”值，例如：

我试图创建一个视图来仅输出每个用户id最近的记录，同时给出“in”或“out”值，因此类似于：

id  user    time    io
2   9   1370931664  out
3   6   1370932128  out
5   12  1370933037  in

我目前已经非常接近了，但我意识到视图不接受子查询，这使得它变得更加困难。我最接近的查询是：

select 
    `lms_attendance`.`id` AS `id`,
    `lms_attendance`.`user` AS `user`,
    max(`lms_attendance`.`time`) AS `time`,
    `lms_attendance`.`io` AS `io` 
from `lms_attendance` 
group by 
    `lms_attendance`.`user`, 
    `lms_attendance`.`io`

但我得到的是:

id  user    time    io
3   6   1370932128  out
1   9   1370931664  out
5   12  1370933037  in
4   12  1370932128  out

看起来已经很接近了，但还不完美。我知道最后一个group by不应该在那里，但是如果没有它，就会返回最近的时间，但没有其相对的IO值。

有什么想法吗？ 谢谢！

- Keith

---

可能是重复的问题：如何从MySQL表中选择最新日期记录集？ - Barmar

回到手册中，你会发现它提供了有和没有子查询（相关和无关）的解决方案来解决这个问题。 - Strawberry

@Barmar，从技术上讲，正如我在我的答案中指出的那样，这是所有带有[tag:greatest-n-per-group]标签的700个问题的重复。 - Tomas

@Prodikl，'io（enum）'是什么？ - Monica Heddneck

这对我来说可行，但太慢了；如果我执行“select * from data”，它只需要0.15秒。如果我按照上面的算法执行，需要23秒！ - beep_check

Answer 1

查询：

SQLFIDDLE示例

SELECT t1.*
FROM lms_attendance t1
WHERE t1.time = (SELECT MAX(t2.time)
                 FROM lms_attendance t2
                 WHERE t2.user = t1.user)

结果：

| ID | USER |       TIME |  IO |
--------------------------------
|  2 |    9 | 1370931664 | out |
|  3 |    6 | 1370932128 | out |
|  5 |   12 | 1370933037 |  in |

请注意，如果一个用户有多条记录具有相同的“最大”时间，则上述查询将返回多条记录。如果您只想每个用户返回1条记录，请使用以下查询：

SQLFIDDLEExample

SELECT t1.*
FROM lms_attendance t1
WHERE t1.id = (SELECT t2.id
                 FROM lms_attendance t2
                 WHERE t2.user = t1.user            
                 ORDER BY t2.id DESC
                 LIMIT 1)

- Justin

---

哇！不仅这个可行，用这个查询创建了一个包含子查询的视图也是被允许的。之前，当我试图创建包含子查询的视图时，它并不允许我这么做。为什么一个可以但另一个不行有什么规则吗？ - Keith

非常奇怪。非常感谢！也许是因为我的子查询是一个伪表，我在其中选择了FROM，在这个例子中它被用在了WHERE子句中。 - Keith

无需子查询！此外，如果有两个记录的时间完全相同，则此解决方案将无法工作。不需要每次都试图重新发明轮子，因为这是一个常见的问题 - 相反，选择已经经过测试和优化的解决方案 - 请参阅@Prodikl的答案。 - Tomas

啊，谢谢你的见解！明天我上班时会尝试这个新代码。 - Keith

如果记录具有完全相同的时间，那么此解决方案确实有效，因为查询定位到具有最大id的记录。这意味着表中的时间是插入时间，这可能不是一个好的假设。您的解决方案代替比较时间戳，并且当两个时间戳相同时，您也返回具有最大id的行。因此，您的解决方案还假定此表中的时间戳与插入顺序有关，这是两个查询中最大的缺陷。 - WebWanderer

Answer 2

不需要试图重新发明轮子，因为这是一个常见的“分组取最大N个元素”问题。非常好的解决方案已被提出。

我更喜欢最简单的解决方案（请参阅SQLFiddle，更新了Justin的代码），它没有子查询（因此在视图中使用很容易）：

SELECT t1.*
FROM lms_attendance AS t1
LEFT OUTER JOIN lms_attendance AS t2
  ON t1.user = t2.user 
        AND (t1.time < t2.time 
         OR (t1.time = t2.time AND t1.Id < t2.Id))
WHERE t2.user IS NULL

如果在同一组中有两个不同的记录具有相同的最大值，这种方法也适用 - 多亏了使用(t1.time = t2.time AND t1.Id < t2.Id)技巧。我在这里做的只是确保在同一用户的两个记录具有相同时间的情况下，只选择一个记录。实际上，无论标准是Id还是其他什么标准 - 基本上任何能够保证唯一性的标准都可以胜任。

- Tomas

---

最大值使用t1.time < t2.time，而最小值则是t1.time > t2.time，这与我的初始直觉相反。 - None

@J.Money，因为存在隐含的否定：您选择了t1中所有记录，这些记录没有与t2中符合t1.time < t2.time条件的相应记录匹配 :-) - Tomas

“WHERE t2.user IS NULL”这句话有点奇怪，它的作用是什么？ - tumultous_rooster

由Justin发布的被接受的答案可能更加优化。被接受的答案在表的主键上使用了一个反向索引扫描，然后是一个限制，然后是对表的序列扫描。因此，通过添加额外的索引可以大大优化被接受的答案。这个查询也可以通过索引进行优化，因为它执行了两个序列扫描，但还包括对序列扫描结果和另一个序列扫描的哈希和“哈希反连接”的哈希。我很想知道哪种方法真正更优。 - WebWanderer

@TMS 感谢您的回复！在我的情况下，似乎没有OR部分也可以工作。我有993个带有“Year”整数列的项目，其范围从2006年到2016年，因此只有AND（t1.Year < t2.Year）部分应该有很多重复项，但是没有。我的查询看起来像这样：SELECT t1.* FROM MyTable AS t1 LEFT JOIN MyTable AS t2 ON t1.Id = t2.Id AND t1.Series = t2.Series AND (t1.Year < t2.Year) WHERE t2.Id IS NULL AND t2.Series IS NULL（我有一个复合主键（Id，Series））。 - Oleg Kuts

Answer 3

根据@TMS的答案，我喜欢它是因为不需要子查询，但我认为省略'OR'部分就足够简单易懂了。

SELECT t1.*
FROM lms_attendance AS t1
LEFT JOIN lms_attendance AS t2
  ON t1.user = t2.user 
        AND t1.time < t2.time
WHERE t2.user IS NULL

如果您不感兴趣那些时间为空的行，您可以在 WHERE 子句中对它们进行过滤：

SELECT t1.*
FROM lms_attendance AS t1
LEFT JOIN lms_attendance AS t2
  ON t1.user = t2.user 
        AND t1.time < t2.time
WHERE t2.user IS NULL and t1.time IS NOT NULL

- user1792210

---

如果两个记录可以具有相同的“时间”，则省略“OR”部分是一个非常糟糕的想法。 - Tomas

出于性能考虑，我会避免使用这种解决方案。正如@OlegKuts所提到的那样，在中大型数据集上，这种方法会变得非常缓慢。 - Peter Meadley

Answer 4

如果你使用的是MySQL 8.0或更高版本，你可以使用窗口函数：

查询：

DBFiddleExample

SELECT DISTINCT
FIRST_VALUE(ID) OVER (PARTITION BY lms_attendance.USER ORDER BY lms_attendance.TIME DESC) AS ID,
FIRST_VALUE(USER) OVER (PARTITION BY lms_attendance.USER ORDER BY lms_attendance.TIME DESC) AS USER,
FIRST_VALUE(TIME) OVER (PARTITION BY lms_attendance.USER ORDER BY lms_attendance.TIME DESC) AS TIME,
FIRST_VALUE(IO) OVER (PARTITION BY lms_attendance.USER ORDER BY lms_attendance.TIME DESC) AS IO
FROM lms_attendance;

结果：

| ID | USER |       TIME |  IO |
--------------------------------
|  2 |    9 | 1370931664 | out |
|  3 |    6 | 1370932128 | out |
|  5 |   12 | 1370933037 |  in |

我认为相对于 Justin 提出的 解决方案，这种方法的优势在于它使您能够从子查询中选择每个用户（或每个 id，或每个其他条件）最新数据的行，而无需中间视图或表。如果你正在运行 HANA，它还可以快 ~7 倍 :D。

- whme

---

您是否需要为想要提取的每个字段都添加 FIRST_VALUE()？ - Stevoisiak

由于OP询问每个用户最近日期的值，这需要按日期排序并取第一个值。如果您没有将结果集通过窗口函数减少到1行，那么使用它就没有意义，我猜测。 - whme

我更想知道的是，有没有一种方法可以避免在每个要提取的值上重复使用FIRST_VALUE()和PARTITION BY <x> ORDER BY <y> DESC？ - Stevoisiak

我认为是这样，但我不确定。也许这会成为一个好的SO问题？ - whme

Answer 5

已经解决，但是仅供参考，另一种方法是创建两个视图...

CREATE TABLE lms_attendance
(id int, user int, time int, io varchar(3));

CREATE VIEW latest_all AS
SELECT la.user, max(la.time) time
FROM lms_attendance la 
GROUP BY la.user;

CREATE VIEW latest_io AS
SELECT la.* 
FROM lms_attendance la
JOIN latest_all lall 
    ON lall.user = la.user
    AND lall.time = la.time;

INSERT INTO lms_attendance 
VALUES
(1, 9, 1370931202, 'out'),
(2, 9, 1370931664, 'out'),
(3, 6, 1370932128, 'out'),
(4, 12, 1370932128, 'out'),
(5, 12, 1370933037, 'in');

SELECT * FROM latest_io;

点击此处在 SQL Fiddle 上查看实例

- davmos

---

谢谢您的跟进！是的，如果没有更简单的方法，我将创建多个视图。再次感谢。 - Keith

Answer 6

我尝试了一个对我有效的解决方案

    SELECT user, MAX(TIME) as time
      FROM lms_attendance
      GROUP by user
      HAVING MAX(time)

- Mukeem Ahmad

Answer 7

好的，这可能是一种黑客或容易出错的方法，但不知何故也能正常工作-

SELECT id, MAX(user) as user, MAX(time) as time, MAX(io) as io FROM lms_attendance GROUP BY id;

- kev

Answer 8

我有一张非常大的表格，所有其他建议在执行时都需要很长时间。我想出了这种hacky方法，速度要快得多。缺点是，如果max(date)行对于该用户具有重复日期，则会返回两个日期。

SELECT * FROM mb_web.devices_log WHERE CONCAT(dtime, '-', user_id) in (
    SELECT concat(max(dtime), '-', user_id) FROM mb_web.devices_log GROUP BY user_id
)

- I wrestled a bear once.

Answer 9

select b.* from 

    (select 
        `lms_attendance`.`user` AS `user`,
        max(`lms_attendance`.`time`) AS `time`
    from `lms_attendance` 
    group by 
        `lms_attendance`.`user`) a

join

    (select * 
    from `lms_attendance` ) b

on a.user = b.user
and a.time = b.time

- chetan

---

谢谢。我知道可以使用子查询来完成，但我希望将其转换为视图，而且据我所知，在视图中不允许使用子查询。我需要将每个子查询都转换为视图吗？ - Keith

`join(select * from lms_attendance ) b` = `join lms_attendance b` - azerafati

Answer 10

我已经做了和下面类似的事情

SELECT t1.* FROM lms_attendance t1 WHERE t1.id in (SELECT max(t2.id) as id FROM lms_attendance t2 group BY t2.user)

这也会减少内存使用。

谢谢。

- Rohit Ramani