MySQL：删除组中第n行以外的行

问题描述：

I insert rows into a table with time-stamps (time). I could have multiple rows that are identical except id and time. I want to delete all rows that are beyond 5 "iterations". I only want to keep 5 iterations of an instance's "history".

Instance = new entry for name

History = the entries for a particular name

Example:

id    name    value    time
1     blue    15       12/1/2016
2     blue    16       12/2/2016
3     blue    12       12/3/2016
4     blue    43       12/4/2016
5     blue    12       12/5/2016
6     blue    9        12/6/2016
7     blue    33       12/7/2016
8     red     15       12/5/2016
9     red     15       12/8/2016

After Delete:

id    name    value    time
3     blue    12       12/3/2016
4     blue    43       12/4/2016
5     blue    12       12/5/2016
6     blue    9        12/6/2016
7     blue    33       12/7/2016
8     red     15       12/5/2016
9     red     15       12/8/2016

我将行插入带有时间戳（time code>）的表中。除了 id code>和 time code>之外，我可以有多个相同的行。我想删除超过5“迭代”的所有行。我只想保留一个实例的“历史”的5次迭代。 p>

Instance = name code>的新条目 p>

History =特定名称的条目 code> p>

示例： strong> p>

  id name value time 
1 blue 15 12/1/2016 
2 blue  16 12/2/2016 
3 blue 12 12/3/2016 
4 blue 43 12/4/2016 
5 blue 12 12/5/2016 
6 blue 9 12/6/2016 
7 blue 33 12/7 /  2016 
8 red 15 12/5/2016 
9 red 15 12/8/2016 
  code>  pre> 
 
  删除后： strong>  p>  
 
 
  id name value time 
3 blue 12 12/3/2016 
4 blue 43 12/4/2016 
5 blue 12 12/5/2016 
6 blue 9 12/6/2016  
7 blue 33 12/7/2016 
8 red 15 12/5/2016 
9 red 15 12/8/2016 
  code>  pre> 
  div>

答

Here was the adopted solution from the suggested almost-duplicate question:

delete l.* from table_name L inner join (
    select name, group_concat(id order by time desc) grouped_value from table_name 
) R on l.name=r.name and find_in_set(id,grouped_value) > 5

MySQL：删除组中第n行以外的行

相关推荐