删除表上的所有记录，每个用户的最新 10 条记录除外

这适用于 mysql 5.x 及更高版本

mysql 自版本以来具有窗口函数 ROW_NUMBER 以便可以在没有用户定义变量的情况下编写子查询

我添加了一些数据以表明它有效

思路很简单，按user_id对所有日期进行排序，保持10个最新，rownumber定义了按日期降序排序的记录的位置

CREATE TABLE user_date
    (`id` int, `user_id` int, `activity` varchar(3), `date` varchar(19))
;
    
INSERT INTO user_date
    (`id`, `user_id`, `activity`, `date`)
VALUES
    (1, 1, 'foo', '2020-08-07 00:00:00'),
    (2, 1, 'bar', '2020-08-08 00:00:00'),
    (3, 1, 'baz', '2020-08-09 00:00:00'),
    (4, 2, 'foo', '2020-08-07 00:00:00'),
    (5, 2, 'bar', '2020-08-08 00:00:00'),
    (6, 2, 'baz', '2020-08-09 00:00:00')
    ,
    (7, 2, 'baz', '2020-08-10 00:00:00'),
    (8, 2, 'baz', '2020-08-10 00:00:00'),
    (9, 2, 'baz', '2020-08-10 00:00:00'),
    (10, 2, 'baz', '2020-08-10 00:00:00'),
    (11, 2, 'baz', '2020-08-10 00:00:00'),
    (12, 2, 'baz', '2020-08-10 00:00:00'),
    (13, 2, 'baz', '2020-08-10 00:00:00'),
    (14, 2, 'baz', '2020-08-10 00:00:00'),
    (15, 2, 'baz', '2020-08-10 00:00:00'),
    (16, 2, 'baz', '2020-08-10 00:00:00')
;

DELETE u1
FROM
       user_date AS u1
   JOIN
(
SELECT  id
  FROM
     (
      SELECT  @row_num := IF(@prev_value=o.user_id,@row_num+1,1) AS 'RowNumber'
             ,o.id
             ,@prev_value := o.user_id
        FROM user_date o,
             (SELECT @row_num := 1) x,
             (SELECT @prev_value := '') y
       ORDER BY o.user_id, o.`date` DESC
     ) subquery
 WHERE `RowNumber` > 10 ) u2 ON u1.id = u2.id  
 

SELECT * FROM user_date

编号 | 用户 ID | 活动 | 日期               
-: | ------: | :------- | :-----------------
 1 | 1 | 富 | 2020-08-07 00:00:00
 2 | 1 | 酒吧 | 2020-08-08 00:00:00
 3 | 1 | 巴兹 | 2020-08-09 00:00:00
 7 | 2 | 巴兹 | 2020-08-10 00:00:00
 8 | 2 | 巴兹 | 2020-08-10 00:00:00
 9 | 2 | 巴兹 | 2020-08-10 00:00:00
10 | 2 | 巴兹 | 2020-08-10 00:00:00
11 | 2 | 巴兹 | 2020-08-10 00:00:00
12 | 2 | 巴兹 | 2020-08-10 00:00:00
13 | 2 | 巴兹 | 2020-08-10 00:00:00
14 | 2 | 巴兹 | 2020-08-10 00:00:00
15 | 2 | 巴兹 | 2020-08-10 00:00:00
16 | 2 | 巴兹 | 2020-08-10 00:00:00

db<>在这里摆弄

对于Sql 服务器

CREATE TABLE user_date
    (id int, user_id int, activity varchar(3), date varchar(19))
;
    
INSERT INTO user_date
    (id, user_id, activity, date)
VALUES
    (1, 1, 'foo', '2020-08-07 00:00:00'),
    (2, 1, 'bar', '2020-08-08 00:00:00'),
    (3, 1, 'baz', '2020-08-09 00:00:00'),
    (4, 2, 'foo', '2020-08-07 00:00:00'),
    (5, 2, 'bar', '2020-08-08 00:00:00'),
    (6, 2, 'baz', '2020-08-09 00:00:00')
    ,
    (7, 2, 'baz', '2020-08-10 00:00:00'),
    (8, 2, 'baz', '2020-08-10 00:00:00'),
    (9, 2, 'baz', '2020-08-10 00:00:00'),
    (10, 2, 'baz', '2020-08-10 00:00:00'),
    (11, 2, 'baz', '2020-08-10 00:00:00'),
    (12, 2, 'baz', '2020-08-10 00:00:00'),
    (13, 2, 'baz', '2020-08-10 00:00:00'),
    (14, 2, 'baz', '2020-08-10 00:00:00'),
    (15, 2, 'baz', '2020-08-10 00:00:00'),
    (16, 2, 'baz', '2020-08-10 00:00:00')
;
GO

DELETE u1
FROM
       user_date AS u1
   JOIN
(
SELECT  id
  FROM
     (
      SELECT    ROW_NUMBER() OVER(PARTITION BY o.user_id ORDER BY o.[date] DESC) 
                    AS RowNumber
             ,o.id
             ,o.user_id
        FROM user_date o
     ) subquery
 WHERE RowNumber > 10 ) u2 ON u1.id = u2.id  
 
GO

3 行受影响

SELECT * FROM user_date
GO

编号 | 用户 ID | 活动 | 日期               
-: | ------: | :------- | :-----------------
 1 | 1 | 富 | 2020-08-07 00:00:00
 2 | 1 | 酒吧 | 2020-08-08 00:00:00
 3 | 1 | 巴兹 | 2020-08-09 00:00:00
 7 | 2 | 巴兹 | 2020-08-10 00:00:00
 8 | 2 | 巴兹 | 2020-08-10 00:00:00
 9 | 2 | 巴兹 | 2020-08-10 00:00:00
10 | 2 | 巴兹 | 2020-08-10 00:00:00
11 | 2 | 巴兹 | 2020-08-10 00:00:00
12 | 2 | 巴兹 | 2020-08-10 00:00:00
13 | 2 | 巴兹 | 2020-08-10 00:00:00
14 | 2 | 巴兹 | 2020-08-10 00:00:00
15 | 2 | 巴兹 | 2020-08-10 00:00:00
16 | 2 | 巴兹 | 2020-08-10 00:00:00

db<>在这里摆弄