delete Table_y anot exists(select 1 from Table_y where a.id<id and name=name )
CREATE TABLE TB(ID INT IDENTITY ,COL1 VARCHAR(10))INSERT INTO TB (COL1) SELECT 'AA' UNION ALL SELECT 'AA' UNION ALL SELECT 'BB' UNION ALL SELECT 'C'SELECT * FROM TBSELECT [NO]=MIN(ID),COL1 FROM TB GROUP BY COL1 DROP TABLE TB
if object_id('test') is not null drop table test create table test ( id int identity(1,1), name nvarchar(10) ) insert into test select 'aaa' union all select 'aaa' union all select 'aaa' union all select 'bbb' select * from testselect * from test a where not exists (select * from test b where a.name=b.name and a.id>b.id )
select * from test a where not exists (select * from test b where a.name=b.name and a.id>b.id )
delete test where id not in (select id from (select * from test a where not exists (select * from test b where a.name=b.name and a.id>b.id)) c)
delete table_name from table_name a where exists(select 1 from a where table_name.name=name and table_name.id > id)
delete table_name from table_name a where exists(select 1 from a where table_name.name=name and table_name.id > id)
if object_id ('a') is not null drop table a create table a ( id int identity(1,1), name varchar(5) )insert a select 'a' union all select 'a' union all select 'b' union all select 'b' union all select 'b' select * from a delete from a where id in ( select id from a aa where exists (select top 1 id from a bb where bb.name = aa.name and aa.id < bb.id) )select * from a/** id name ----------- ----- 2 a 5 b(所影响的行数为 2 行) **/
SELECT 'AA' UNION ALL
SELECT 'AA' UNION ALL
SELECT 'BB' UNION ALL
SELECT 'C'SELECT * FROM TBSELECT [NO]=MIN(ID),COL1 FROM TB GROUP BY COL1
DROP TABLE TB
if object_id('test') is not null
drop table test
create table test
(
id int identity(1,1),
name nvarchar(10)
)
insert into test
select 'aaa' union all
select 'aaa' union all
select 'aaa' union all
select 'bbb'
select * from testselect * from test a
where not exists
(select * from test b
where a.name=b.name and a.id>b.id
)
(select * from test b
where a.name=b.name and a.id>b.id
)
delete test where id not in (select id from (select * from test a where not exists
(select * from test b where a.name=b.name and a.id>b.id)) c)
from table_name a
where exists(select 1 from a where table_name.name=name and table_name.id > id)
delete table_name
from table_name a
where exists(select 1 from a where table_name.name=name and table_name.id > id)
if object_id ('a') is not null
drop table a
create table a
(
id int identity(1,1),
name varchar(5)
)insert a
select 'a' union all
select 'a' union all
select 'b' union all
select 'b' union all
select 'b' select * from a
delete from a where id in
(
select id from a aa where exists
(select top 1 id from a bb where
bb.name = aa.name and aa.id < bb.id)
)select * from a/**
id name
----------- -----
2 a
5 b(所影响的行数为 2 行)
**/