sqlite 相当于 row_number() over ( partition by ...? [英] sqlite equivalent of row_number() over ( partition by ...?
问题描述
我想知道是否可以使用单个 sqlite 语句执行以下操作:
I'd like to know if it's possible to do the following using a single sqlite statement:
我的桌子看起来像这样:
My table looks something like this:
|AnId|UserId|SomeDate|SomeData|
|123 |A |1/1/2010|aadsljvs|
| 87 |A |2/9/2010|asda fas|
|193 |A |2/4/2010|aadsljvs|
|927 |A |7/3/2010|aadsasdf|
|816 |B |1/1/2010|aa32973v|
|109 |B |7/5/2010|aaasfd10|
| 39 |B |1/3/2010|66699327|
...
每一行都有一个唯一的 ID、一个用户 ID、一个日期时间值和一些其他数据.
Each row has a unique id, a user id, a datetime value, and some other data.
我想删除记录,以便根据 SomeDate每个用户保留最新的 10 条记录.
I'd like to delete records so I keep the latest 10 records per user, based on SomeDate.
在 sql server 中我会使用这样的东西:
In sql server I'd use something like this:
delete d
from data d
inner join (
select UserId
, AnId
, row_number() over ( partition by UserId order by SomeDate desc )
as RowNum
from data
) ranked on d.AnId = ranked.AnId
where ranked.RowNum > 10
有没有办法在 sqlite 中做到这一点?有多个记录具有相同 SomeDate 的边缘情况并不是特别担心,例如如果我保留所有这些记录就好了.
Is there a way to do this in sqlite? The edge case where there are several records with the same SomeDate isn't a particular worry, e.g. if I keep all those records that'd be fine.
推荐答案
我知道这个问题很老了,但是下面的 SQLite 语句将执行 Rory 最初在一个语句中要求的操作 - 删除给定 UserId 的所有记录不是该 UserId 的 10 条最新记录(基于 SomeDate).
I know this question is old, but the following SQLite statement will do what Rory was originally asking for in one statement - Delete all records for a given UserId that are not the 10 most recent records for that UserId (based on SomeDate).
DELETE FROM data
WHERE AnId IN (SELECT AnId
FROM data AS d
WHERE d.UserId = data.UserId
ORDER BY SomeDate DESC
LIMIT -1 OFFSET 10)
这篇关于sqlite 相当于 row_number() over ( partition by ...?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!