如何删除所有商品,但在价格历史记录表中保留每个产品的最新2行

我有一张桌子,我在其中存储产品历史记录

+------------+---------------------+
| product_id | date_in             |
+------------+---------------------+
|     500 | 2020-04-24 07:10:52 |
|     500 | 2020-04-24 07:12:11 |
|     500 | 2020-04-24 07:16:25 |
|     500 | 2020-04-24 07:18:32 |
|     500 | 2020-04-24 07:18:42 |
|     500 | 2020-04-24 13:51:30 |
|     500 | 2020-04-24 14:01:22 |

|     501 | 2020-04-24 07:10:52 |
|     501 | 2020-04-24 07:12:11 |
|     501 | 2020-04-24 07:16:25 |
|     501 | 2020-04-24 07:18:32 |
|     501 | 2020-04-24 07:18:42 |
|     501 | 2020-04-24 13:51:30 |
|     501 | 2020-04-24 14:01:22 |
+------------+---------------------+

我只想保留每个产品的最后2个历史记录行,并将其全部删除,因此它看起来像这样

+------------+---------------------+
| product_id | date_in             |
+------------+---------------------+

|     500 | 2020-04-24 13:51:30 |
|     500 | 2020-04-24 14:01:22 |


|     501 | 2020-04-24 13:51:30 |
|     501 | 2020-04-24 14:01:22 |
+------------+---------------------+
评论
  • Sim
    Sim 回复

    You can use the delete/join syntax, along with row_number() (the latter requires MySQL 8.0)`:

    delete t
    from mytable t
    inner join (
        select 
            product_id, 
            date_in, 
            row_number() over(partition by product_id order by date_in desc) rn
        from mytable t
    ) t1 
        on  t1.product_id = t.product_id 
        and t1.date_in = t.date_in 
        and t1.rn > 2