MySQL 选择每个用户最近日期的行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/17038193/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Select row with most recent date per user
提问by Keith
I have a table ("lms_attendance") of users' check-in and out times that looks like this:
我有一个用户签入和签出时间的表（“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
I'm trying to create a view of this table that would output only the most recent record per user id, while giving me the "in" or "out" value, so something like:
我正在尝试创建此表的视图，该视图将仅输出每个用户 ID 的最新记录，同时为我提供“输入”或“输出”值，例如：
id user time io
2 9 1370931664 out
3 6 1370932128 out
5 12 1370933037 in
I'm pretty close so far, but I realized that views won't accept subquerys, which is making it a lot harder. The closest query I got was :
到目前为止我已经很接近了，但我意识到视图不会接受子查询，这使它变得更加困难。我得到的最接近的查询是：
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`
But what I get is :
但我得到的是：
id user time io
3 6 1370932128 out
1 9 1370931664 out
5 12 1370933037 in
4 12 1370932128 out
Which is close, but not perfect. I know that last group by shouldn't be there, but without it, it returns the most recent time, but not with it's relative IO value.
这很接近，但并不完美。我知道最后一个 group by 不应该在那里，但是如果没有它，它会返回最近的时间，而不是它的相对 IO 值。
Any ideas? Thanks!
有任何想法吗？谢谢！
回答by Justin
Query:
询问：
SELECT t1.*
FROM lms_attendance t1
WHERE t1.time = (SELECT MAX(t2.time)
FROM lms_attendance t2
WHERE t2.user = t1.user)
Result:
结果：
| ID | USER | TIME | IO |
--------------------------------
| 2 | 9 | 1370931664 | out |
| 3 | 6 | 1370932128 | out |
| 5 | 12 | 1370933037 | in |
Solution which gonna work everytime:
每次都会工作的解决方案：
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)
回答by TMS
No need to trying reinvent the wheel, as this is common greatest-n-per-group problem. Very nice solution is presented.
无需尝试重新发明轮子，因为这是常见的每组最大 n 个问题。提出了非常好的解决方案。
I prefer the most simplistic solution (see SQLFiddle, updated Justin's) without subqueries (thus easy to use in views):
我更喜欢没有子查询的最简单的解决方案（请参阅 SQLFiddle，更新 Justin's）（因此易于在视图中使用）：
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
This also works in a case where there are two different records with the same greatest value within the same group - thanks to the trick with (t1.time = t2.time AND t1.Id < t2.Id). All I am doing here is to assure that in case when two records of the same user have same time only one is chosen. Doesn't actually matter if the criteria is Idor something else - basically any criteria that is guaranteed to be unique would make the job here.
这也适用于在同一组中有两个具有相同最大值的不同记录的情况 - 多亏了(t1.time = t2.time AND t1.Id < t2.Id). 我在这里所做的只是确保在同一用户的两个记录具有相同时间的情况下只选择一个。标准是Id什么或其他标准实际上并不重要- 基本上任何保证唯一的标准都可以在这里工作。
回答by user1792210
Based in @TMS answer, I like it because there's no need for subqueries but I think ommiting the 'OR'part will be sufficient and much simpler to understand and read.
基于@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
if you are not interested in rows with null times you can filter them in the WHEREclause:
如果您对空时间的行不感兴趣，您可以在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
回答by davmos
Already solved, but just for the record, another approach would be to create two views...
已经解决了，但只是为了记录，另一种方法是创建两个视图......
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;
回答by Konstantin XFlash Stratigenas
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
回答by chetan
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
回答by Nicolas Brauer
If your on MySQL 8.0 or higher you can use Window functions:
如果您使用的是 MySQL 8.0 或更高版本，则可以使用窗口函数：
Query:
询问：
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;
Result:
结果：
| ID | USER | TIME | IO |
--------------------------------
| 2 | 9 | 1370931664 | out |
| 3 | 6 | 1370932128 | out |
| 5 | 12 | 1370933037 | in |
The advantage I see over using the solution proposed by Justinis that it enables you to select the row with the most recent data per user (or per id, or per whatever) even from subqueries without the need for an intermediate view or table.
我看到的使用Justin 提出的解决方案的优势在于，它使您能够从子查询中选择每个用户（或每个 id，或每个）具有最新数据的行，而无需中间视图或表。
And in case your running a HANA it is also ~7 times faster :D
如果您运行 HANA，它也会快 7 倍：D
回答by kev
Ok, this might be either a hack or error-prone, but somehow this is working as well-
好吧，这可能是黑客攻击或容易出错，但不知何故这也有效-
SELECT id, MAX(user) as user, MAX(time) as time, MAX(io) as io FROM lms_attendance GROUP BY id;
回答by Sugan
Try this query:
试试这个查询：
select id,user, max(time), io
FROM lms_attendance group by user;
回答by Alvaro Sifuentes
This worked for me:
这对我有用：
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
