现有表table1,结构如下,字段stationName有很多重复的内容,求一SQL语句,重复的项目只保留一条,其余的都删除。 stationID stationName
86028 九眼桥
86028 青龙场中心站
86028 火车北站
86028 火车北站
86028 红星路广场
86028 青龙场中心站
86028 青龙场中心站
86028 青龙场中心站
86028 五桂桥
86028 游乐园
86028 九眼桥
86028 青龙场中心站
86028 火车北站
86028 火车北站
86028 红星路广场
86028 青龙场中心站
86028 青龙场中心站
86028 青龙场中心站
86028 五桂桥
86028 游乐园
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(*)>1)
select distinct * into # from 表名delete 表名insert 表名
select * from #
create table tab(stationID varchar(10),stationName varchar(30))
insert tab
select '86028','九眼桥'
union all select '86028','青龙场中心站'
union all select '86028','火车北站'
union all select '86028','火车北站'
union all select '86028','红星路广场'
union all select '86028','青龙场中心站'
union all select '86028','青龙场中心站'
union all select '86028','青龙场中心站'
union all select '86028','五桂桥'
union all select '86028','游乐园'select distinct * into # from tabdelete from tabinsert tab
select * from #select * from tabdrop table tab,#/* 结果stationID stationName
---------- ------------------------------
86028 红星路广场
86028 火车北站
86028 九眼桥
86028 青龙场中心站
86028 五桂桥
86028 游乐园(6 row(s) affected)*/
SELECT TOP 0 stationID, stationName INTO table2 FROM table1
-- 复制数据唯一值
SELECT DISTINCT stationID, stationName INTO table2 FROM table1
-- 删除表table1
DROP TABLE table1
-- 重命名表
EXEC sp_rename 'table2', 'table1'