今天看到一个删除重复数据的sql面试题,用原生sql想了很久也没有想出解决方案。最后还是利用mysql8的窗体函数来解决这个问题。
代码如下:
DELETE FROM tab1 WHERE id IN ( SELECT id FROM ( SELECT row_number ( ) over ( PARTITION BY a, b ORDER BY id ) AS row_num, id, a, b, c, d, e, f, g FROM tab1 ) AS t WHERE row_num != 1 );
如果大家可以使用原生sql解决这个问题欢迎大家留言啊!