如何为每个成员选择最新输入? [英] How can I select the most recent input for each member?

问题描述

假设我有一个MySQL表:

Assume I have a MySQL table:

╔══════╦═════╦═════════╦═════════╗
║ time ║ mid ║ field_1 ║ field_2 ║
╠══════╬═════╬═════════╬═════════╣
║  100 ║   1 ║      32 ║      54 ║
║  100 ║   2 ║       0 ║      34 ║
║  100 ║   3 ║      44 ║      99 ║
║  200 ║   1 ║       0 ║      45 ║
║  200 ║   2 ║       0 ║      45 ║
║  200 ║   3 ║       4 ║      59 ║
║  200 ║   4 ║      45 ║      45 ║
╚══════╩═════╩═════════╩═════════╝

time是UNIX时间戳. mid是会员ID. field_1由成员提供. field_2是自动填充的.

time is a UNIX timestamp. mid is the member id. field_1 is supplied by the member. field_2 is filled automatically.

我希望为所有成员选择具有最新非零field_1的行.因此查询将导致:

I wish to select the row with the most recent non-zero field_1 for all members. So the query would result in:

╔══════╦═════╦═════════╦═════════╗
║ time ║ mid ║ field_1 ║ field_2 ║
╠══════╬═════╬═════════╬═════════╣
║  100 ║   1 ║      32 ║      54 ║
║  200 ║   3 ║       4 ║      59 ║
║  200 ║   4 ║      45 ║      45 ║
╚══════╩═════╩═════════╩═════════╝

我想出的唯一解决方案似乎并不十分优雅:

The only solution I came up with does not seem very elegant:

SELECT *
FROM (
    SELECT *
    FROM t1
    WHERE field_1 > 0
    ORDER BY time DESC
) AS a
GROUP BY mid

有更好的方法吗?

推荐答案

SELECT a.*
FROM t1 AS a
INNER JOIN
(
    SELECT mid, MAX(time) AS maxTime
    FROM t1
    WHERE field_1 <> 0
    GROUP BY mid
) b ON a.mid = b.mid AND a.time = b.maxTime

SQL提琴

这篇关于如何为每个成员选择最新输入?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！