mysql - 在根据特定条件格式化正确的 SQL 查询时需要帮助

Question

这是我当前的表sandbox：

| id | parent_id | title | text |
|----|-----------|-------|------|
| 1  | 11        | some  | text |
| 2  | 11        | some  | text |
| 3  | 11        | some  | text |
| 4  | 11        | some  | text |
| 5  | 11        | some  | text |
| 6  | 14        | some  | text |
| 7  | 14        | some  | text |
| 8  | 19        | some  | text |
| 9  | 19        | some  | text |

我想要实现的是根据特定的 parent_id 保留 N 个最近的行，同时保留具有不同 parent_id 的其他行。

例如，最近 3 个 parent_id = 11 的表应如下所示：

| id | parent_id | title | text |
|----|-----------|-------|------|
| 3  | 11        | some  | text |
| 4  | 11        | some  | text |
| 5  | 11        | some  | text |
| 6  | 14        | some  | text |
| 7  | 14        | some  | text |
| 8  | 19        | some  | text |
| 9  | 19        | some  | text |

我已经尝试过了：

DELETE FROM `sandbox`
WHERE id <= (
  SELECT id
  FROM (
    SELECT id
    FROM `sandbox`
    WHERE parent_id = 11
    ORDER BY id DESC
    LIMIT 1 OFFSET 3 <-- keep this many records
  ) foo
)

但后来我松开了其他行，得到这样的表格：

| id | parent_id | title | text |
|----|-----------|-------|------|
| 3  | 11        | some  | text |
| 4  | 11        | some  | text |
| 5  | 11        | some  | text |

任何有关正确 SQL 查询的帮助将不胜感激:)

标签： mysqlsql

Answer 1

包含parent_id = 11在 WHERE 子句中并使用 NOT IN 以避免删除 3 个最近的行：

DELETE FROM `sandbox`
WHERE parent_id = 11
AND id NOT IN (
  SELECT id
  FROM (
    SELECT id
    FROM `sandbox`
    WHERE parent_id = 11
    ORDER BY id DESC
    LIMIT 3
  ) foo
)