每组仅保留200条最新记录
我有一个表，其中包含id | name | date | group等列。
我想要做的是删除每组计数超过200的所有旧记录。
例如，我有一个名为"shoes“的组，它有400条记录，"giftcard”有300条记录，"electronics“有100条记录，等等。
因此，在运行SQL查询后，我想要的是每个组(鞋子、礼品卡、电子产品等)的数量小于或等于200。要删除的记录是由日期或id (自动递增)标识的旧记录。因此，将删除"shoes“组中的200条记录，这些记录比所保存的记录更旧，或者id少于所保存的记录。
回答 3
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用户
发布于 2014-09-16 23:43:06
运行此psuedo-SQL
SELECT shoes.id FROM shoes ORDER BY Date DESC LIMIT 200然后解析其中的结果(数组..(1、2等) --调用此$IDS )
DELETE FROM shoes WHERE ID NOT IN ($IDS)编辑:要在SQL查询中完成所有操作，有两种可能的方法。
1。DELETE FROM shoes WHERE ID NOT IN (SELECT shoes.id FROM shoes ORDER BY Date DESC LIMIT 200) --是的，你可以做到。注意。正如Bill建议的那样，首先将其作为SELECT * FROM shoes WHERE ID NOT IN (SELECT shoes.id FROM shoes ORDER BY Date DESC LIMIT 200)运行，以确保它选择了您想要删除的正确内容！
2。我不太了解DECLARE，但是您可以先声明@IDs = SELECT shoes.id FROM shoes ORDER BY Date DESC LIMIT 200，然后声明DELETE FROM shoes WHERE ID NOT IN (@IDS)
这两个都是未经测试的。顺便说一下，您应该使用SQLFiddle来设置模拟模式信息，这样当人们需要帮助时，他们就可以测试他们的查询。
Stack Overflow用户
发布于 2014-09-17 01:03:37
这将是一个SQL Server解决方案
Select * from (
Select *, ROW_NUMBER() OVER (Partition By [Group] order by Date) RN
from table) t1
inner join (
Select [GROUP], COUNT(*) as Cnt
from table
group by [Group]
) a on a.[Group] = t1.[Group]
where t1.RN <= 200
and a.Cnt >= 200编辑：
这里使用CTE
With CTE as
(
Select [GROUP], COUNT(*) as cnt
from tbl
group by [Group]
)
Select t1.*
from (Select *, ROW_NUMBER() OVER (Partition By [Group] order by Date) RN
from tbl) t1
inner join CTE a on a.[Group] = t1.[Group]
where t1.RN <= 200 and
a.Cnt >= 200https://stackoverflow.com/questions/25872968
复制相似问题
腾讯云开发者
