sql中如何删除一个表中重复的记录?
insert into a_dist values(1,'abc')
insert into a_dist values(1,'abc')
insert into a_dist values(1,'abc')
insert into a_dist values(1,'abc')
删除重复记录,sql语句该怎么写? 展开
sql中删除一个表中的重复记录可以采用如下步骤:
1、把a_dist表的记录用distinct去重,结果放到临时表中。
select distinct * into #temp from a_dist;
2、把a_dist表的记录全部删除。
delete from a_dist;
3、把临时表中的数据信息导进到a_dist表中,并删除临时表。
insert into a_dist select * from #temp;
drop table #temp;
扩展资料:
SQL (结构化查询语言)是用于执行查询的语法。在数据库上执行的大部分工作都由 SQL 语句完成。SQL 语言包含用于更新、插入和删除记录的语法。
增删改查指令构成了 SQL 的 DML 部分:
SELECT - 从数据库表中获取数据
UPDATE - 更新数据库表中的数据
DELETE - 从数据库表中删除数据
INSERT INTO - 向数据库表中插入数据
数据库去重复有以下三种方法:
1.两条记录或者多条记录的每一个字段值完全相同,这种情况去重复最简单,用关键字distinct就可以去掉。
2.两条记录之间之后只有部分字段的值是有重复的,但是表存在主键或者唯一性ID。如果是这种情况的话用DISTINCT是过滤不了的,这就要用到主键id的唯一性特点及group by分组。
3.两条记录之间之后只有部分字段的值是有重复的,但是表不存在主键或者唯一性ID。这种情况可以使用临时表,讲数据复制到临时表并添加一个自增长的ID,在删除重复数据之后再删除临时表。
扩展资料:
1、查找表中多余的重复记录,重复记录是根据单个字段(peopleId)来判断。
select * from people
where peopleId in (select peopleId from people group by peopleId having count(peopleId) > 1)
2、删除表中多余的重复记录,重复记录是根据单个字段(peopleId)来判断,只留有rowid最小的记录。
delete from people
where peopleId in (select peopleId from people group by peopleId having count(peopleId) > 1)
and rowid not in (select min(rowid) from people group by peopleId having count(peopleId )>1)
3、查找表中多余的重复记录(多个字段)。
select * from vitae a
where (a.peopleId,a.seq) in (select peopleId,seq from vitae group by peopleId,seq having count(*) > 1)
4、删除表中多余的重复记录(多个字段),只留有rowid最小的记录。
delete from vitae a
where (a.peopleId,a.seq) in (select peopleId,seq from vitae group by peopleId,seq having count(*) > 1)
and rowid not in (select min(rowid) from vitae group by peopleId,seq having count(*)>1)
5、查找表中多余的重复记录(多个字段),不包含rowid最小的记录。
select * from vitae a
where (a.peopleId,a.seq) in (select peopleId,seq from vitae group by peopleId,seq having count(*) > 1)
and rowid not in (select min(rowid) from vitae group by peopleId,seq having count(*)>。
delete from 表
where aa in (select aa from 表 group by aa having count(aa) > 1) and
bb not in (select max(bb) from 表 group by aa having count(aa) > 1);
2、有多种写法:
delete A from B where A.AA = B.AA
delete A from A,B where A.AA = B.AA
delete A where AA in (select AA from B)
3、使用into关键字:
select * into 新表名 from 原表
4、取数据前3位,字段必须是类似char类型,使用类似substring这样的函数(SYBASE是substring,ORACLE是substr):
select substring(字段,1,3) from 表名
2011-10-19 · 知道合伙人数码行家
select distinct * into #tmpp from tid
delete from tid
insert into tid select * from #tmpp
drop table #tmpp
如果有id主键(数字,自增1的那种),那么:(sql server2000下测试通过)
delete from tableA where id not in
(select id = min(id) from tableA group by name)