SQL sqlite 相当于 row_number() over ( partition by ...?
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4074257/
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
sqlite equivalent of row_number() over ( partition by ...?
提问by Rory
I'd like to know if it's possible to do the following using a single sqlite statement:
我想知道是否可以使用单个 sqlite 语句执行以下操作：
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|
...
Each row has a unique id, a user id, a datetime value, and some other data.
每行都有一个唯一的 id、一个用户 id、一个日期时间值和一些其他数据。
I'd like to delete records so I keep the latest 10 records per user, based on SomeDate.
我想删除记录，以便根据 SomeDate 保留每个用户的最新 10 条记录。
In sql server I'd use something like this:
在 sql server 中，我会使用这样的东西：
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
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中做到这一点？有多个记录具有相同 SomeDate 的边缘情况并不是特别担心，例如，如果我保留所有这些记录就可以了。
采纳答案by Jett
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).
我知道这个问题很老，但是下面的 SQLite 语句将执行 Rory 在一个语句中最初要求的操作 - 删除给定 UserId 的所有记录，这些记录不是该 UserId 的 10 个最新记录（基于 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)
回答by Angoranator777
I needed to fetch the second row for each "object" in a table with a 1 to many relationship to the "object" table.
我需要为表中的每个“对象”获取第二行，该表与“对象”表具有 1 对多的关系。
Usually in SQL this will be done using ROW_NUMBER() OVER(PARTITION BY object_id ORDER BY primary_id DESC)
通常在 SQL 这将使用 ROW_NUMBER() OVER(PARTITION BY object_id ORDER BY primary_id DESC) 完成
In Sqlite I had to come up with this voodoo sub-query to get the same result
在 Sqlite 中，我不得不想出这个 voodoo 子查询来获得相同的结果
SELECT object_id, MAX(_id)
FROM (SELECT object_id, _id
FROM aTable
EXCEPT
SELECT object_id, MAX(_id)
FROM aTable
GROUP BY object_id)
GROUP BY object_id;
Note: The _id is the primary key of aTable and the object table has a 1 to many relationship with the queried table
注意：_id是aTable的主键，对象表和被查询的表是一对多的关系
回答by Ralf Bertling
As of Sqlite 3.25 window functions are supported. See https://www.sqlite.org/windowfunctions.htmlfor details.
从 Sqlite 3.25 开始支持窗口函数。有关详细信息，请参阅https://www.sqlite.org/windowfunctions.html。
回答by Jawad Khan
If you already haven't got the answer. If it's one table, then you don't need any joins. You can just use:
如果你还没有得到答案。如果是一张表，则不需要任何连接。你可以只使用：
Delete From data
where AnId not in (Select AnId
from data
Order by SomeDate DESC
Limit 10)
回答by sbirch
This might be prohibitively expensive (perhaps only do it when a user inserts a new record?) but how about this:
这可能非常昂贵（也许只有在用户插入新记录时才这样做？）但是这样怎么样：
for user in users:
user-records = select * from records where user=user
if user-records.length > 10:
delete from records where user=user and date<user-records[10]
(in a mix of SQL and pseudocode)
（混合使用 SQL 和伪代码）
