请教一下大家,我现在是想在一个表中查找重复的记录,以两个字段为标准查找,例如以姓名和编号为标准,我看网上的解决方法基本都是这样的
select * from vitae a
where (a.peopleId,a.seq) in (select peopleId,seq from vitae group by peopleId,seq having count(*) > 1) 为什么我用这个方法总说a.peopleId这句"在应使用条件的上下文(在 ',' 附近)中指定了非布尔类型的表达式",请问大家是什么意思?这确的应该怎么写?请高手指教,谢谢大家了。
select * from vitae a
where (a.peopleId,a.seq) in (select peopleId,seq from vitae group by peopleId,seq having count(*) > 1) 为什么我用这个方法总说a.peopleId这句"在应使用条件的上下文(在 ',' 附近)中指定了非布尔类型的表达式",请问大家是什么意思?这确的应该怎么写?请高手指教,谢谢大家了。
where exists(select 1 from vitae where a.peopleId=peopleId and a.seq=seq)
INSERT #tab
SELECT 'a','001',2 UNION ALL
SELECT 'a','002',4 UNION ALL
SELECT 'b','003',4 UNION ALL
SELECT 'a','001',9 UNION ALL
SELECT 'b','003',8SELECT a.* from #tab a,(SELECT NAME,no FROM #tab t
GROUP BY NAME,no HAVING COUNT(*)>1)b WHERE a.[NAME]=b.[NAME] AND a.no=b.no/*
NAME no item
---------- ---------- -----------
a 001 2
a 001 9
b 003 4
b 003 8(4 行受影响)
*/
那么我现在要删除其他的记录,只留下一条记录,该怎么写?
select name,no,item,row_number() over(partition by name,no order by item ) as cnt from #tab
)
delete from #tab where exists(select 'x' from cte where cte.name=#tab.name and cte.no=#tab.no and cte.item=#tab.item and cte.cnt>=2)
where (id+name) in (select id+name from test group by id+name having count(id+name) > 1)
select * from vitae a
where (select Count(1) from vitae where a.peopleId=peopleId and a.seq=seq)>1如果是要删除重复的记录,如果该表没有可区分不同记录的其它主键的话,可以使用临时表,用IDENTITY函数全表插入到临时表中,在临时表中删除重复记录,删除原表记录,再将临时表中记录全部插入到原表中.