SQL:忽略一些返回的行，删除其他行

Question

我有这张表:

| Column        | Type                           |
+---------------+--------------------------------+         
| id            | integer                        |
| recipient_id  | integer                        | 
| is_read       | boolean                        | 
| updated_at    | timestamp(0) without time zone | 

我必须使用此特定规则从该表中删除项目:

• 对于每个 recipient_id，我们保留 5 个最后阅读的项目，并删除旧的阅读项目。

我试图用 RECURSIVE WITH 语句来改变我的想法，但惨遭失败。我已经以编程方式实现了我的解决方案，但我想知道是否有合适的纯 SQL 解决方案。

Answer 1

DELETE FROM tbl t
USING (
    SELECT id, row_number() OVER (PARTITION BY recipient_id
                                  ORDER BY updated_at DESC) as rn
    FROM   tbl
    WHERE  is_read
  ) x
WHERE  x.rn > 5
AND    x.id = t.id;

JOIN 通常比 IN 表达式更快，尤其是在项目数量较多的情况下。
并使用 row_number(), not rank() !