SQL 查询以获取给定键的每个实例的最新行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1140064/
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
SQL query to get most recent row for each instance of a given key
提问by alanc10n
I'm trying to get the ip, user, and most recent timestamp from a table which may contain both the current ip for a user and one or more prior ips. I'd like one row for each user containing the most recent ip and the associated timestamp. So if a table looks like this:
我试图从一个表中获取 ip、用户和最近的时间戳，该表可能包含用户的当前 ip 和一个或多个先前的 ip。我希望每个用户都有一行，其中包含最新的 ip 和相关的时间戳。所以如果一个表看起来像这样：
username | ip | time_stamp
--------------|----------|--------------
ted | 1.2.3.4 | 10
jerry | 5.6.6.7 | 12
ted | 8.8.8.8 | 30
I'd expect the output of the query to be:
我希望查询的输出是：
jerry | 5.6.6.7 | 12
ted | 8.8.8.8 | 30
Can I do this in a single sql query? In case it matters, the DBMS is Postgresql.
我可以在单个 sql 查询中执行此操作吗？如果重要，DBMS 是 Postgresql。
回答by Chris Nielsen
Try this:
尝试这个：
Select u.[username]
,u.[ip]
,q.[time_stamp]
From [users] As u
Inner Join (
Select [username]
,max(time_stamp) as [time_stamp]
From [users]
Group By [username]) As [q]
On u.username = q.username
And u.time_stamp = q.time_stamp
回答by Cristi S.
Nice elegant solution with ROW_NUMBER window function (supported by PostgreSQL - see in SQL Fiddle):
带有 ROW_NUMBER 窗口函数的漂亮优雅的解决方案（由 PostgreSQL 支持 - 请参阅SQL Fiddle）：
SELECT username, ip, time_stamp FROM (
SELECT username, ip, time_stamp,
ROW_NUMBER() OVER (PARTITION BY username ORDER BY time_stamp DESC) rn
FROM Users
) tmp WHERE rn = 1;
回答by Kim Gr?sman
Something like this:
像这样的东西：
select *
from User U1
where time_stamp = (
select max(time_stamp)
from User
where username = U1.username)
should do it.
应该这样做。
回答by user2323503
Both of the above answers assume that you only have one row for each user and time_stamp. Depending on the application and the granularity of your time_stamp this may not be a valid assumption. If you need to deal with ties of time_stamp for a given user, you'd need to extend one of the answers given above.
以上两个答案都假设每个用户和时间戳只有一行。根据应用程序和时间戳的粒度，这可能不是一个有效的假设。如果您需要处理给定用户的 time_stamp 关系，则需要扩展上面给出的答案之一。
To write this in one query would require another nested sub-query - things will start getting more messy and performance may suffer.
在一个查询中编写它需要另一个嵌套的子查询 - 事情将开始变得更加混乱并且性能可能会受到影响。
I would have loved to have added this as a comment but I don't yet have 50 reputation so sorry for posting as a new answer!
我很想将其添加为评论，但我还没有 50 名声望，因此很抱歉将其作为新答案发布！
回答by err1
Can't post comments yet, but @Cristi S's answer works a treat for me.
还不能发表评论，但@Cristi S 的回答对我来说是一种享受。
In my scenario, I needed to keep only the most recent 3 records in Lowest_Offers for all product_ids.
在我的场景中，我只需要在 Lowest_Offers 中为所有 product_id 保留最近的 3 条记录。
Need to rework his SQL to delete - thought that this would be ok, but syntax is wrong.
需要重新编写他的 SQL 以删除 - 认为这可以，但语法错误。
DELETE from (
SELECT product_id, id, date_checked,
ROW_NUMBER() OVER (PARTITION BY product_id ORDER BY date_checked DESC) rn
FROM lowest_offers
) tmp WHERE > 3;
回答by jawz101
I've been using this because I'm returning results from another table. Though I'm trying to avoid the nested join if it helps w/ one less step. Oh well. It returns the same thing.
我一直在使用它，因为我从另一个表返回结果。虽然我试图避免嵌套连接，如果它有助于少一步。那好吧。它返回同样的东西。
select
users.userid
, lastIP.IP
, lastIP.maxdate
from users
inner join (
select userid, IP, datetime
from IPAddresses
inner join (
select userid, max(datetime) as maxdate
from IPAddresses
group by userid
) maxIP on IPAddresses.datetime = maxIP.maxdate and IPAddresses.userid = maxIP.userid
) as lastIP on users.userid = lastIP.userid
