mysql - 随机删除未知数量的记录

Question

我有一个表，名为items。
结构是: {name, id, category_id}

表中有未知数量的记录，我需要将每个类别的记录数量限制为 MAX 数量，例如 20。

伪

DELETE FROM items
WHERE (SELECT COUNT(category_id) FROM items)> 20
LIMIT (SELECT COUNT(category_id) FROM items) - 20

Answer 1

DELETE FROM items 
WHERE (category_id,id) not in (
  SELECT category_id, id FROM items  a WHERE id IN (
     SELECT id FROM items s 
     WHERE a.category_id = s.category_id
     ORDER BY category_id LIMIT 20) 
  ORDER BY  id)