去除重复的 TypeName 值 并且查询的时候需要携带重复的 TypeName , Id 不同,那你打算保留那个 Id 呢?
delete from Type where Id is (select a.Id from Type a,Type b where a.TypeName = b.TypeName and a.Id != b.Id)
delete from Type where Id in (select a.Id from Type a,Type b where a.TypeName = b.TypeName and a.Id != b.Id) 上面那个有点小错误,这个正确
Id 是自增的、但是查询的时候必须要Id 这列、
select id,TypeName from (select id,TypeName,row_number() over(partition by TypeName order by id) rn from Type) a where rn=1select * from Type a where exists(select 1 from where a.TypeName=TypeName and a.id>id)select * from Type a where a.id in(select max(id) from Type where a.id=id group by TypeName)
select max(Id),TypeNamefrom Type group by TypeName
select max(Id),TypeName from Type group by TypeName
delete from Type where Id is
(select a.Id from Type a,Type b where a.TypeName = b.TypeName and a.Id != b.Id)
delete from Type where Id in
(select a.Id from Type a,Type b where a.TypeName = b.TypeName and a.Id != b.Id)
上面那个有点小错误,这个正确
Id 是自增的、但是查询的时候必须要Id 这列、
from (select id,TypeName,row_number() over(partition by TypeName order by id) rn
from Type) a
where rn=1select * from Type a
where exists(select 1 from where a.TypeName=TypeName and a.id>id)select * from Type a
where a.id in(select max(id) from Type where a.id=id group by TypeName)