我有一个包含两列id和feedId的表，如果count>，我想在插入新记录后删除同一个feedId中的旧项； 这是桌子
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有计数时才算工作 10，但如果有两个或更多feedId具有计数>gt； 10
如果希望每个feedid有10行，请使用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;
