每组仅保留200条最新记录
每组仅保留200条最新记录
提问于 2014-09-16 23:36:53
我有一个表，其中包含id | name | date | group等列。
我想要做的是删除每组计数超过200的所有旧记录。
例如，我有一个名为"shoes“的组，它有400条记录，"giftcard”有300条记录，"electronics“有100条记录，等等。
因此，在运行SQL查询后，我想要的是每个组(鞋子、礼品卡、电子产品等)的数量小于或等于200。要删除的记录是由日期或id (自动递增)标识的旧记录。因此，将删除"shoes“组中的200条记录，这些记录比所保存的记录更旧，或者id少于所保存的记录。
Stack Overflow用户
发布于 2014-09-17 01:32:02
这种类型的问题在MySQL中有点不方便，因为它们没有实现像ROW_NUMBER()这样的SQL-99窗口函数。在8.0版之前，MySQL不支持此功能。
这里有一个解决方案，它可以在SQL5.7和更早版本的单个MySQL语句中工作，并且只能为大于第200个的每个组的成员进行选择。它使用一个名为user variables的MySQL特性，在从一行到另一行的查询过程中，该特性会保留它们的值。
DELETE f FROM foo AS f
JOIN (SELECT id, IF(@g = `group`, @rn:=@rn+1, @rn:=1) AS row_number, @g:=grp
FROM foo, (SELECT @g:=null, @rn:=0) _init
ORDER BY `group`, date desc) AS r
ON f.id = r.id AND r.row_number > 200;在运行此命令(或任何删除数据的命令)之前，我建议您了解它是如何工作的，并使用等效的SELECT命令对其进行测试，以确保它选择的是您想要删除的行。
我用一个较小的数据集测试了这一点。这是我在没有过滤的情况下运行它时的数据：
SELECT f.id, f.`group`, r.row_number FROM foo AS f
JOIN (SELECT id, IF(@g = `group`, @rn:=@rn+1, @rn:=1) AS row_number, @g:=grp
FROM foo, (SELECT @g:=null, @rn:=0) _init
ORDER BY `group`, date desc) AS r
ON f.id = r.id;
+----+--------+------------+
| id | group | row_number |
+----+--------+------------+
| 1 | 1 | 1 |
| 2 | 1 | 2 |
| 3 | 1 | 3 |
| 5 | 1 | 4 |
| 11 | 1 | 5 |
| 4 | 2 | 1 |
| 10 | 2 | 2 |
| 8 | 2 | 3 |
| 7 | 3 | 1 |
| 6 | 3 | 2 |
| 12 | 3 | 3 |
| 9 | 4 | 1 |
+----+--------+------------+下面的SELECT跳过每个组的前两个：
SELECT f.id, f.`group`, r.row_number FROM foo AS f
JOIN (SELECT id, IF(@g = `group`, @rn:=@rn+1, @rn:=1) AS row_number, @g:=grp
FROM foo, (SELECT @g:=null, @rn:=0) _init
ORDER BY `group`, date desc) AS r
ON f.id = r.id AND r.row_number > 2;
+----+-------+------------+
| id | group | row_number |
+----+-------+------------+
| 3 | 1 | 3 |
| 5 | 1 | 4 |
| 11 | 1 | 5 |
| 8 | 2 | 3 |
| 12 | 3 | 3 |
+----+-------+------------+页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/25872968
复制相关文章
相似问题
腾讯云开发者
