sql - #1093 表 'table' 被指定了两次，既作为 'DELETE' 的目标又作为数据的单独源

Question

在 mysql 查询中没有得到想要的结果

我已经搜索了很多但没有找到解决方案

DELETE FROM table1 WHERE username NOT IN (select t1.id from table1 as t1
inner join table1 as t2 on t1.username = t2.username and t1.id <= t2.id 
group by t1.username , t1.id having count(*) <= 5 order by t1.username , t1.id desc);

输出如下:-

Answer 1

应该适合你

delete from
    table1
where
    id in ( select
                id
            from
                (   select
                        *,
                        row_number() over(  partition by
                                                username
                                            order by
                                                id desc) as rn
                    from 
                        table1)
            where
                rn > 5)