delete from table where id not in (select max(id) from table group by name,age,sex)不知对否!
delete from table a where id not in (select max(id) from table b where name = a.name and b.sex= a.sex and b.age =a.age)
delete from t where t.rowid in (select rid from (select rowid rid , first_value () over (partition by 姓名 , 年龄 , 性别 order by id asc ) frid from t ) tmp where tmp.rid<>t.frid ) /* 当 order by asc 保留 id 最小的记录 当 order by desc 保留 id 最大的记录 */
楼上的应该是这样子,不过这样不能把相同行都删除,会保留一行 delete from t where t.id in (select rid from (select id rid , first_value (id) over (partition by 姓名 , 年龄 , 性别 order by id asc ) frid from t ) tmp where tmp.rid <>t.frid ) 要全部删除,可以用delete from t where (姓名 , 年龄 , 性别) in (select distinct 姓名 , 年龄 , 性别 from t group by 姓名 , 年龄 , 性别 having count(*)>=2) 最直接的方法,这方法如果数据量大,会比较慢
where t.rowid in
(select rid
from (select rowid rid ,
first_value () over (partition by 姓名 , 年龄 , 性别
order by id asc ) frid
from t
) tmp
where tmp.rid<>t.frid
)
/*
当 order by asc 保留 id 最小的记录
当 order by desc 保留 id 最大的记录
*/
delete from t
where t.id in
(select rid
from (select id rid ,
first_value (id) over (partition by 姓名 , 年龄 , 性别
order by id asc ) frid
from t
) tmp
where tmp.rid <>t.frid
) 要全部删除,可以用delete from t
where (姓名 , 年龄 , 性别) in
(select distinct 姓名 , 年龄 , 性别
from t group by 姓名 , 年龄 , 性别
having count(*)>=2) 最直接的方法,这方法如果数据量大,会比较慢