查找和删除记录中有多个字段重复的记录的sql语句怎么写?网上搜了,都是同一个文章,那个语句报错。这是网上找的:3、查找表中多余的重复记录(多个字段)
select * from vitae a
where (a.peopleId,a.seq) in (select peopleId,seq from vitae group by peopleId,seq having count(*) > 1)
报错:消息 102,级别 15,状态 1,第 2 行
',' 附近有语法错误。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)报错:消息 102,级别 15,状态 1,第 1 行
'a' 附近有语法错误。
消息 156,级别 15,状态 1,第 3 行
关键字 'and' 附近有语法错误。SQL重复记录
select * from vitae a
where (a.peopleId,a.seq) in (select peopleId,seq from vitae group by peopleId,seq having count(*) > 1)
报错:消息 102,级别 15,状态 1,第 2 行
',' 附近有语法错误。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)报错:消息 102,级别 15,状态 1,第 1 行
'a' 附近有语法错误。
消息 156,级别 15,状态 1,第 3 行
关键字 'and' 附近有语法错误。SQL重复记录
解决方案 »
- sql server不使用聚合函数进行行列转换
- 谁能帮我解释一下这条SQL语句!
- 问个数据库查询语句的问题
- 做一个去掉约束和外键的存储过程,发现结果不对,object_name()是否取当前数据库的值?
- 请教:SQL2005中的包含性列[索引复盖]如何用才能看出效果?
- 求sql语句或其他数据统计方法
- 如何把用group by 分组后每组数据>1条的列出来?
- 紧急问题!!!
- 如何将Sql server 2005 数据库 导入到 Sql server 2000 中去
- 如何写下面的存储过程?
- sql server 2005删除行时出现错误,不能删除
- 200-500万的数据量查询好慢。怎样才能快点??
select * from vitae a
where exists(select 1
from (select peopleId,seq from vitae group by peopleId,seq having count(*) > 1)t
where a.peopleId=t.peopleId and a.seq=t.seq)
--删除
delete vitae
where cast(peopleId as varchar)+'^'+cast(seq as varchar) in
(select cast(peopleId as varchar)+'^'+cast(seq as varchar)
from (
select *,ROW_NUMBER() over(order by peopleId,seq) rn from vitae a
where exists(select 1
from (select peopleId,seq from vitae group by peopleId,seq having count(*) > 1)t
where a.peopleId=t.peopleId and a.seq=t.seq)
)m
where rn>1
)
;with sel as(
select row_number()over(partition by peopleid,seq order by 1) as rownum,* from vitae
)
select * from sel where rownum>1 --查詢
;with sel as(
select row_number()over(partition by peopleid,seq order by 1) as rownum,* from vitae
)
delete from sel where rownum>1 --刪除
select a.* from vitae as a join
(select peopleId,seq from vitae group by peopleId,seq having count(*) > 1)as t
on a.peopleid=t.peopleid and a.seq=t.seq