mysql - 删除以前的条目并使用当前条目更新表 - mySql

Question

下面是我的 table

entryID  ID    Number    locationId  timestamp  
331      136   7         1           5/30/2018 9:23     
332      136   7         1           5/30/2018 9:25  
333      136   9         1           5/30/2018 9:25         
334      136   11        1           5/30/2018 9:25    
335      136   11        1           5/30/2018 9:25    
336      136   9         1           5/30/2018 9:26    
337      137   1         2           6/4/2018 8:45    
338      137   2         2           6/4/2018 8:47  
339      137   7         2           6/4/2018 8:47  
340      137   7         2           6/4/2018 8:47

我想通过 ID 和 locationId 过滤它们，删除重复的行(“Number”是带有重复值的列)并在查询通过 locationId = 1 过滤的表后看到如下所示的结果。

entryID  ID    Number    locationId  timestamp    
332      136   7         1           5/30/2018 9:25   
335      136   11        1           5/30/2018 9:25    
336      136   9         1           5/30/2018 9:26    
337      137   1         2           6/4/2018 8:45    
338      137   2         2           6/4/2018 8:47  
339      137   7         2           6/4/2018 8:47  
340      137   7         2           6/4/2018 8:47

如果 locationId = 2，下面是我希望看到的结果。

  entryID    ID    Number    locationId  timestamp  
  331        136   7         1           5/30/2018 9:23     
  332        136   7         1           5/30/2018 9:25  
  333        136   9         1           5/30/2018 9:25         
  334        136   11        1           5/30/2018 9:25    
  335        136   11        1           5/30/2018 9:25    
  336        136   9         1           5/30/2018 9:26    
  337        137   1         2           6/4/2018 8:45    
  338        137   2         2           6/4/2018 8:47
  340        137   7         2           6/4/2018 8:47

Answer 1

要求没有得到很好的解释。不过，假设 timestamp 确实是一个时间戳，那么我怀疑您正在寻找这样的东西......

(记得在删除任何内容之前备份您的数据!!!)

DELETE x
  FROM my_table x 
  LEFT
  JOIN 
     ( SELECT locationid
            , number
            , MAX(entryid) entryid 
         FROM my_table 
        GROUP 
           BY locationid
            , number
     ) y 
    ON y.locationid = x.locationid 
   AND y.number = x.number 
   AND y.entryid = x.entryid
 WHERE y.entryid IS NULL;