mysql - 按 user_id 分组的 SQL 查询，只获取最近的(按时间)，返回有限的对象

Question

这是我关于 stackoverflow 的第一个问题，所以关于如何改进我的问题的任何意见都会不胜感激。还有，我刚开始学sql在我自己的时间，所以具体的答案和解释会有所帮助。提前致谢!

我正在尝试制作一个特定的查询每个唯一的 user_id 行，以便它获得该 user_id 的最新条目。查看其他帖子，我想出了这个查询:

SELECT user_id, max(time) as time 
FROM $session_name 
GROUP BY user_id;

我希望看到的结果是:

[1] => stdClass Object
    (
        [user_id] => 3
        [lat] => 1 
        [lon] => 1
        [time] => 2016-03-27 11:30:24
    )

[2] => stdClass Object
    (
        [user_id] => 1234
        [lat] => 1 
        [lon] => 1
        [time] => 2016-03-27 11:29:46
    )

但我得到的是:

[1] => stdClass Object
    (
        [user_id] => 3
        [time] => 2016-03-27 11:30:24
    )

[2] => stdClass Object
    (
        [user_id] => 1234
        [time] => 2016-03-27 11:29:46
    )

我认为，在我的 SELECT 语句中，我需要输入字段 lat 和 lon，但是在尝试了不同的查询和阅读不同的帖子，sql 条目从未正确执行。

Answer 1

带有窗口函数的子查询应该可以工作，按照这些行:

SELECT
    user_id,
    lat,
    long,
    time
FROM (
    SELECT
        user_id,
        lat,
        long,
        time,
        ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY time DESC) as row_no
     FROM @session_name 
) u
WHERE row_no = 1

或者使用 LAST_VALUE 窗口函数:

SELECT DISTINCT
    user_id,
    LAST_VALUE(lat)  OVER (PARTITION BY user_id ORDER BY time DESC ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) as lat,
    LAST_VALUE(long) OVER (PARTITION BY user_id ORDER BY time DESC ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) as long,
    LAST_VALUE(time) OVER (PARTITION BY user_id ORDER BY time DESC ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) as time
FROM @session_name