数据库表详情看附图,表中存储多个用户的记录,search_time表示该条记录插入数据库的时间
你说的是一个用户的情况,这个表是保存了多个用户的记录,我不知道怎么实现对应的每个用户删除较早的记录,只保留最新10条
追答试试
delete from tbname where id not in (select id运行不了
delete from tbname where id not in (select id
from tbname as a1
inner join (select a.u_id,a.search_time
from article as a
left join article as b
on a.u_id = b.u_id
and a.search_time <= b.search_time
group by a.u_id,a.search_time
having count(b.search_time) <= 10
) as b1 on a1.u_id = b1.u_id and a1.search_time = b1.search_time
order by a1.u_id,a1.search_time desc
)
参考你的答案我做出来了,谢谢你