我插入了一个表的数据,可是由于执行了多次现在出现了很多重复的行,其中 dno=5的这行和dno = 2这一行 出现了多行请问该怎么删除重复的行 我用的是SQL
数据如下:
--表二department的数据
insert into department(dno,dname,drpperson,ddate)
values(1,'人事处','1002','2004-8-2');
insert into department(dno,dname,drpperson,ddate)
values(2,'机关','2001','2003-8-3');
insert into department(dno,dname,drpperson,ddate)
values(3,'销售科','',null);
insert into department(dno,dname,drpperson,ddate)
values(4,'生产科','4002','2003-6-1');
insert into department(dno,dname,drpperson,ddate)
values(5,'车间','',null);
DELETE FROM DEPARTMENT WHERE values=(5,'车间','',null);
数据如下:
--表二department的数据
insert into department(dno,dname,drpperson,ddate)
values(1,'人事处','1002','2004-8-2');
insert into department(dno,dname,drpperson,ddate)
values(2,'机关','2001','2003-8-3');
insert into department(dno,dname,drpperson,ddate)
values(3,'销售科','',null);
insert into department(dno,dname,drpperson,ddate)
values(4,'生产科','4002','2003-6-1');
insert into department(dno,dname,drpperson,ddate)
values(5,'车间','',null);
DELETE FROM DEPARTMENT WHERE values=(5,'车间','',null);
delect table where id not in ( select max(id) from table group by col1,col2,col3...
)
网上很多的
然后删除表中数据
然后在把刚才插入到临时表的数据
插入到department
delect table where id not in ( select max(id) from table group by col1,col2,col3...
)
go
truncate table tablename
go
insert tablename
select * from temptable
go
drop table temptable
go
2楼的以下也是正解:delect table where id not in (select max(id) from table group by col1,col2,col3... )