为每个用户选择具有最近日期的行

我有一个用户签入和签出时间的表(“ lms _ publications”) ，看起来像这样:

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  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

很接近，但不完美。我知道最后一组 by 不应该在那里，但是如果没有它，它将返回最近的时间，但是不会返回它的相对 IO 值。

有什么想法吗？ 谢谢！

333783 次浏览
小开

也许你可以按用户进行分组，然后按时间进行排序

  SELECT * FROM lms_attendance group by user order by time desc;
小开 
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
小开
最佳答案

质疑:

SQLFIDDLEexample

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)
小开

不需要尝试重新发明车轮，因为这是常见的 每组最大问题。非常好的 提出了解决方案。

我更喜欢没有子查询的最简单的解决方案(见 SQLFiddle，更新贾斯汀的)(因此在视图中很容易使用) :

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还是其他什么-基本上任何保证是唯一的标准都可以在这里完成任务。

小开

已经解决了，不过还有一个办法就是创建两个视图。

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;

点击这里查看它在 SQLFiddle 中的运行情况

小开

基于@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

如果您对具有 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
小开

试试这个查询:

  select id,user, max(time), io
FROM lms_attendance group by user;
小开

这对我很有效:

SELECT user, time FROM
(
SELECT user, time FROM lms_attendance --where clause
) AS T
WHERE (SELECT COUNT(0) FROM table WHERE user = T.user AND time > T.time) = 0
ORDER BY user ASC, time DESC
小开 
 select result from (
select vorsteuerid as result, count(*) as anzahl from kreditorenrechnung where kundeid = 7148
group by vorsteuerid
) a order by anzahl desc limit 0,1
小开

好吧，这可能是一个黑客或容易出错，但不知何故，这是工作以及-

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

如果您使用的是 MySQL 8.0或更高版本，则可以使用 窗口功能:

质疑:

例子

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 |

与使用 贾斯汀提出的解决方案相比，我看到的优势是，它使您能够从子查询中选择每个用户(或每个 id，或每个其他)最新数据的行，而不需要中间视图或表。

如果你运行的是 HANA，那么它的速度也会快7倍

小开

我已经做了同样的事情，如下

选择 t1 * 从 lms _ 考勤 t1 其中 t1.id (选择 max (t2.id)作为 id) 来自 lms _ 考勤 t2 用户组(group BY t2.user)

这也将降低内存利用率。

谢谢。

小开

我已经尝试了一种对我有效的解决方案

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

我有一个非常大的表和所有其他建议在这里需要很长的时间来执行。我想出了一个更快的破解方法。缺点是，如果 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
)

提交答案