sqlite insert触发器如何基于两个值删除表中的旧记录

mzmfm0qo  于 2021-07-24  发布在  Java
关注(0)|答案(1)|浏览(339)

我使用的是sqliteandroid,我有一个包含id和feedid两列的表,我想在插入新记录之后删除同一feedid中的旧项,如果count>10,这就是表

id  feedId
1   1
2   1
3   1
4   1
5   1
6   1
7   1
8   1
9   1
10  1
11  1
12  2
13  2
14  2
15  2
16  2
17  2
18  2
19  2
20  2
21  2
22  2

我在feedid 1中有11条记录,在feedid 2中有11条记录,所以我想从feedid 1中删除1条记录,从feedid 2中删除1条记录
已尝试此查询

CREATE TRIGGER IF NOT EXISTS articles_limiter AFTER INSERT ON articles BEGIN DELETE FROM articles WHERE feedId in (SELECT feedId FROM (SELECT feedId,count(*) as cnt FROM articles GROUP BY feedId HAVING cnt > 10)) ORDER BY id DESC LIMIT -1 OFFSET 10; end;

但只有当有一个feedid的count>10时,它才起作用,但如果有两个或更多feedid的count>10,它就不起作用

vjhs03f7

vjhs03f71#

如果你想每小时10行 feedId ,使用 row_number() :

select a.*
from (select a.*, row_number() over (partition by feedId order by id desc) as seqnum
      from articles a
     ) a
where seqnum <= 10;

相关问题