DELETE FROM TB FROM TB T1 WHERE EXISTS(SELECT 1 FROM TB T2 WHERE T2.Department=T1.Department AND T2.[NAME]=T1.[NAME])
delete from tb t where not exists(select 1 from tb where t.Department=Department and t.Name=Name and t.id>id)
delete from tb where id in(5,6)
要留一条啊,那就加个NOT EXISTSDELETE FROM TB FROM TB T1 WHERE EXISTS(SELECT 1 FROM TB T2 WHERE T2.Department=T1.Department AND T2.[NAME]=T1.[NAME]) AND NOT EXISTS( SELECT 1 FROM TB T2 WHERE T2.Department=T1.Department AND T2.[NAME]=T1.[NAME] AND T2.ID<T1.ID )
delete from tb t where not exists(select 1 from tb where t.Department=Department and t.Name=Name and t.id<id)
delete from table.name where ID=5 and ID=6;
if object_id('tb') is not null drop table tb go create table tb(id int ,depart char(10)) insert into tb select 1,'部门1' union all select 2,'部门1' union all select 3,'部门1' select * from tbdelete t from tb t where exists(select 1 from tb where depart=t.depart and id<t.id)select * from tbid depart 1 部门1
delete from tb where id in(5,6)
我想有一部分人没有明白我的意思。 “假设其中有这样的3条记录: ID为4,5,6的Department和Name都相同 ” 这里面有一个“假设”这个词现在我把我想到的答案说一下:delete from table where id in (select distint b.id from table a, table b where a.id < b.id and a.department = b.department and a.name = b.name)假设id为4,5,6,7的department和name都相同那么select a.id, b.id from table a, table b where a.id < b.id and a.department = b.department and a.name = b.name搜索的结果应该如下: 4,5 4,6 4,7 5,6 5,7 6,7select distint b.id from table a, table b where a.id < b.id and a.department = b.department and a.name = b.name搜索的结果就是如下了: 5 6 7 最后就是删除这些数据了
FROM TB T1 WHERE EXISTS(SELECT 1 FROM TB T2
WHERE T2.Department=T1.Department AND T2.[NAME]=T1.[NAME])
where not exists(select 1 from tb where t.Department=Department and t.Name=Name and t.id>id)
FROM TB T1 WHERE EXISTS(SELECT 1 FROM TB T2
WHERE T2.Department=T1.Department AND T2.[NAME]=T1.[NAME])
AND NOT EXISTS(
SELECT 1 FROM TB T2
WHERE T2.Department=T1.Department AND T2.[NAME]=T1.[NAME] AND T2.ID<T1.ID
)
where not exists(select 1 from tb where t.Department=Department and t.Name=Name and t.id<id)
where ID=5 and ID=6;
drop table tb
go
create table tb(id int ,depart char(10))
insert into tb
select 1,'部门1' union all
select 2,'部门1' union all
select 3,'部门1' select * from tbdelete t from tb t
where exists(select 1 from tb where depart=t.depart and id<t.id)select * from tbid depart
1 部门1
“假设其中有这样的3条记录:
ID为4,5,6的Department和Name都相同 ”
这里面有一个“假设”这个词现在我把我想到的答案说一下:delete from table where id in
(select distint b.id from table a, table b where a.id < b.id
and a.department = b.department and a.name = b.name)假设id为4,5,6,7的department和name都相同那么select a.id, b.id from table a, table b where a.id < b.id
and a.department = b.department and a.name = b.name搜索的结果应该如下:
4,5
4,6
4,7
5,6
5,7
6,7select distint b.id from table a, table b where a.id < b.id
and a.department = b.department and a.name = b.name搜索的结果就是如下了:
5
6
7
最后就是删除这些数据了