根据日期获取最新条目

本文介绍了根据日期获取最新条目的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我想获取有关用户以及他们在特定日期之前拥有的积分数量的信息。我不知道他们的状态上次记录的时间，所以我必须在日期之前获取最新的条目。

I want to get the info about users and the amount of points they had before a specific date. The problem I can't know when their state was last recorded so I have to get the latest entry before the date.

我最终遇到的查询是像这样（简化的示例）：

The query I've ended up with is something like this (simplified example):

SELECT u.id, p.points, p.timestamp FROM user AS u INNER JOIN points ON u.id = p.user_id WHERE u.group = 'red' AND p.timestamp::date <= '2018-01-01 10:00:00' GROUP BY u.id, u.first_name, u.last_name, mh.gamified_mastery, mh.updated_at ORDER BY mh.updated_at DESC

这给了我一张这样的表：

This gives me a table like this:

id | points | timestamp ----------------------- 1 | 10 | 2018-01-01 9:00:00 1 | 25 | 2018-01-01 8:57:00 1 | 25 | 2018-01-01 8:00:00 2 | 100 | 2018-01-01 7:00:00 2 | 50 | 2018-01-01 6:00:00 2 | 15 | 2018-01-01 5:55:00

我实际上并不希望显示时间戳，这里是为了演示的缘故。我只想要每个玩家的前几名，但我必须按时间戳分组才能使ORDER BY工作。

I don't actually want the timestamp to be displayed, it's here for presentation's sake. I only want the top entries for each player but I have to group by timestamp for the ORDER BY to work.

由于其他限制，我真的需要获得所有这些在一个查询中完成（我知道我可以只用LIMIT 1进行一个单独的查询，然后将它们加入应用程序中，但目前还不行）。

Due to other limitations I really need to get all of this done in one query (I know I could just do a separate query with LIMIT 1 and join them in the app but that's not an option currently).

推荐答案

我想您想在上区别 SELECT DISTINCT ON (u.id) u.id, . . . FROM (SELECT u.id, p.points, p.timestamp FROM user u INNER JOIN points p ON u.id = p.user_id WHERE u.group = 'red' AND p.timestamp::date <= '2018-01-01 10:00:00' GROUP BY u.id, u.first_name, u.last_name, mh.gamified_mastery, mh.updated_at ) pu ORDER BY u.id, mh.updated_at DESC;