如果计数> 5，如何从表中删除记录

I have following table:

我有以下表格：

id  systemid    value
1      1           0
2      1           1
3      1           3
4      1           4
6      1           9
8      1           10
9      1           11
10     1           12

Now here i have 8 records of systemid = 1 so now i want to keep only latest 3 records (desc order) and delete older records whose systemid=1

现在我有8条systemid = 1的记录，所以现在我只想保留最新的3条记录（desc order）并删除systemid = 1的旧记录

I want output like :

我想输出像：

id    systemid    value
 8     1            10
 9     1            11
 10    1            12

I just want to delete old records of systemid=1 only if its count > 5 and keep its latest 3 records.

我只想删除systemid = 1的旧记录，只要它的计数> 5并保留最新的3条记录。

How can i do this in query ?

我怎么能在查询中这样做？

4 个解决方案

#1

If you do not always have 8 records and want to select the last 3 records from the table where systemid=1 however many records there are, then a good way to do this is to use the IN selector in your SQL statement.

如果你不总是有8条记录，并且想要从systemid = 1的表中选择最后3条记录，但是有很多记录，那么一个好方法是在SQL语句中使用IN选择器。

It would be good is you could do this simply using the statement

如果您只是使用该语句就可以做到这一点很好

SELECT * FROM mytable WHERE id IN (SELECT id FROM `mytable` WHERE systemid=1 ORDER BY id DESC LIMIT 3)

However this is not yet supported in MySQL and if you try this then you will get an error like

但是，MySQL尚不支持这一点，如果您尝试这样做，那么您将收到类似的错误

...doesn't yet support 'LIMIT & IN/ALL/SOME subquery'

So you need a workaround as follows (using SELECT to test):

所以你需要一个解决方法如下（使用SELECT来测试）：

SET @myvar := (SELECT GROUP_CONCAT(id SEPARATOR ',') AS myval FROM (SELECT * FROM `mytable` WHERE systemid=1 ORDER BY id DESC LIMIT 3 ) A GROUP BY A.systemid);
SELECT * FROM mytable WHERE FIND_IN_SET(id,@myvar);

The way that this works (first line) is to set a variable called @myvar which will hold the last 3 values as a comma separated string if id values. In your case

它的工作方式（第一行）是设置一个名为@myvar的变量，它将最后3个值保存为逗号分隔的字符串（如果是id值）。在你的情况下

9,8,10

Then select the rows where the 'id' is in this string.

然后选择'id'在此字符串中的行。

Replace the 'SELECT *' with 'DELETE FROM' to finalize the result so your query will be

将'SELECT *'替换为'DELETE FROM'以最终确定结果，以便查询

SET @myvar := (SELECT GROUP_CONCAT(id SEPARATOR ',') AS myval FROM (SELECT * FROM `mytable` WHERE systemid=1 ORDER BY id DESC LIMIT 3 ) A GROUP BY A.systemid);
DELETE FROM mytable WHERE NOT FIND_IN_SET(id,@myvar);

I hope that this helps.

我希望这个对你有用。

#2

This is going to be a very long query. What you need is for every system_id that have more than 5 records you want to delete the record that are less than ranking 3.

这将是一个非常长的查询。您需要的是每个具有5个以上记录的system_id要删除小于3的记录。

I'm going to seperate the queries and use names for them at the end.

我将分离查询并在最后使用它们的名称。

1_ ranking_query: abriviation is RQ

1_ ranking_query：abriviation是RQ

In mysql there is no rownum or something like that hope this query return the records ranked from ghe first to the last.

在mysql中没有rownum或类似的东西希望这个查询返回从第一个到最后一个排序的记录。

       SELECT 
               @rank := @rank + 1 as rank, 
               id, 
               system_id,
               value
       FROM table_name, (select @rank:=0) as rt
       order by system_id, value desc

this will rank every record in your table and the main benefit is the record with the same system_id will be after each other in desc order

这将对您表中的每条记录进行排名，主要好处是具有相同system_id的记录将以desc顺序相互跟随

            system_id       value      rank
               1.                     10.         1

                1.                     9.           2

                1.                     7.           3

                1.                     5.           4

                1.                     3.            5

                1.                      2.           6

                2.                    12.          7

                2.                    10.          8

                3.                     11.         9
                ........
                ......
                 3.                     1.          15

In this example for system_id 1 we need to keep only the three first (1,2,3) record same thing for system_id 3 (9,10,11)

在system_id 1的这个例子中，我们需要为system_id 3（9,10,11）只保留三个第一个（1,2,3）记录相同的东西

2_ filter_query. Abriviation is: FQ Because you want to delete based on count 5 we need this extra query

2_ filter_query。 Abriviation是：FQ因为你想根据计数5删除我们需要这个额外的查询

        SELECT
                 system_id
        FROM table_name
        GROUP BY system_id 
        having count(*) > 5

result:

结果：

                     system_id
                        1
                        3

4_ third_query abriviation: RQD

4_ third_query abriviation：RQD

a query to know which rank should we start do delete from for every system_id in mysql we need to rewrite the first query again but here i'm going to use Abriviation to keep the answer short.

我们需要在mysql中为每个system_id删除一个查询，知道我们应该从哪个等级中删除。我们需要再次重写第一个查询，但在这里我将使用Abriviation来保持答案简短。

         SELECT 
               system_id,
               min_rank + 3  from_rank
        FROM (
               SELECT
                      RQ2.system_id,
                      min(RQ2.rank) as  min_rank
              FROM (rank_query) RQ2
              GROUP BY system_id) RS

so for the same example the we going to have this result

所以对于同一个例子，我们将得到这个结果

         system_id.    from_rank 
            1.                     4
            2.                     9    -- this record will be removed by the filter_query
            3.                     12

FINAL QUERY:

最终查询：

so we need to delete record that exist in filter query and the ranking is bigger than from_rank.

所以我们需要删除过滤器查询中存在的记录，并且排名大于from_rank。

        DELETE FROM table_name WHERE 
              id in (
                 SELECT
                     RQ.id
                 FROM
                            rank_query RQ INNER JOIN filter_query FQ ON rq.system_id = FQ.system_id
                                                       INNER JOIN third_query RQD ON RQ.rank >= RQD.from_rank)

I hope this idea work sorry for any syntax error i used my phone to answer i like this kind of query

我希望这个想法能为我使用手机回答的任何语法错误感到抱歉我喜欢这种查询

#3

Try this to keep the latest three records that have system_id equal to 1 and count is greater than 5:

尝试此操作以保留最新的三条记录，其中system_id等于1且count大于5：

DELETE FROM <table_name> WHERE system_id = 1 AND value > 5 ORDER BY id DESC LIMIT 3

DELETE FROM WHERE system_id = 1 AND value> 5 ORDER BY id DESC LIMIT 3

#4

You can specify an offset with the LIMIT keyword in your query so the newest 5 rows are kept. According to MySQL's documentation, however, there's no easy way to limit from an offset all the way to the last; instead, they suggest this:

您可以在查询中使用LIMIT关键字指定偏移量，以保留最新的5行。然而，根据MySQL的文档，没有简单的方法可以将偏移限制为最后一个;相反，他们建议：

To retrieve all rows from a certain offset up to the end of the result set, you can use some large number for the second parameter.

要从特定偏移量检索所有行直到结果集的末尾，可以使用一些大数字作为第二个参数。

So this SQL should do the trick:

所以这个SQL应该可以解决这个问题：

DELETE FROM table where systemid = 1 ORDER BY value DESC LIMIT 5,45484848

DELETE FROM table其中systemid = 1 ORDER BY值DESC LIMIT 5,45484848