表中数据如下:name idTom 1
Tom 2
Jerry 1
Jerry 2
Jerry 10
Jack 23
Jack 15。我要保留id最小的,Name唯一,如: Tom 1, Jerry 1, Jack 15
Tom 2
Jerry 1
Jerry 2
Jerry 10
Jack 23
Jack 15。我要保留id最小的,Name唯一,如: Tom 1, Jerry 1, Jack 15
from tb t
where exists(select 1 from tb where name=t.name and id<t.id)
go
create table [tb]([name] varchar(10),[id] int)
insert [tb] select 'Tom',1
union all select 'Tom',2
union all select 'Jerry',1
union all select 'Jerry',2
union all select 'Jerry',10
union all select 'Jack',23
union all select 'Jack',15delete t
from tb t
where exists(select 1 from tb where name=t.name and id<t.id)select * from tb
/*
name id
---------- -----------
Tom 1
Jerry 1
Jack 15(3 行受影响)*/
tb
where
exists(select 1 from tb t where t.name=tb.name and t.id>tb.id)
tb
where
exists(select 1 from tb t where t.name=tb.name and t.id<tb.id)select * from tb/**
name id
---------- -----------
Tom 1
Jerry 1
Jack 15(所影响的行数为 3 行)
**/
from 表名 as A,
(select name, min(id) as id
from 表名
group by name
) as v
where A.name = v.name and
A.id > v.id
go
create table [tb]([name] varchar(10),[id] int)
insert [tb] select 'Tom',1
union all select 'Tom',2
union all select 'Jerry',1
union all select 'Jerry',2
union all select 'Jerry',10
union all select 'Jack',23
union all select 'Jack',15delete t
from tb t
where exists(select 1 from tb where name=t.name and id<t.id)select * from tb这个正解
delete t where name in (select name from (select name,min(id) from t group by name ))
from tb t
where exists(select 1 from tb where name=t.name and id<t.id)
go
create table [tb]([name] varchar(10),[id] int)
insert [tb] select 'Tom',1
union all select 'Tom',2
union all select 'Jerry',1
union all select 'Jerry',2
union all select 'Jerry',10
union all select 'Jack',23
union all select 'Jack',15delete from [tb] where id not exists(select min(id) from [tb])delete
tb
where
exists(select 1 from tb t where t.name=tb.name and t.id<tb.id)