求sql句子清理表内重复数据

2004-07-28 05:46:38 272 阅读 AI 写同款 · GEO 优化AI 选题数据监测一键分发 ›

【测试必备技能】如何用SQL精准揪出表中的重复数据？多种方法详解摘要：本文介绍了软件测试中查询重复数据的三种SQL方法：1. GROUP BY和HAVING子句（最常用），适用于统计字段重复次数；2. ROW_NUMBER()窗口函数（现代方法），能直接标记重复记录；3. SELF JOIN（自连接），适合理解SQL关系。测试建议包括明确业务规则中的重复定义、将查询集成到测试流程中，以及考虑大数据量时的性能优化。这些方法能有效验证数据唯一性约束，排查业务逻辑错误。阅读详情

有一表 a 字段如下

id username age
1 tom 25
2 jim 34
3 tim 65
4 jacky 33
5 jacky 33
6 tim 65

id 字段唯一 username里有重复信息如何删除

...全文

【Ragflow】9.问答为什么比搜索响应慢？从源码角度深入分析前几天看到有群友提到一个现象：Ragflow的搜索界面的问答速度很快，但聊天界面的问答速度很慢，究竟是什么原因？在深入挖掘源码之后，逐渐发现了问题的答案。阅读详情

fansun

MS-SQL Server 34,876 成员 254,635 内容

+ 关注 + 加入社区

切换为时间正序

sankis 2004-07-29

打赏
举报

Select Max(id), username Into #Table From table Group by username
Delete From table Where id not In (Select id #table)
Drop table #Table

xikboy 2004-07-29

打赏
举报

delete from a where id not in (select max(id) as id from a group by username)

exingsa 2004-07-28

打赏
举报

!!!up!!上面重复记录好像有只要删两条就可以了呀!!

egxsun 2004-07-28

打赏
举报

mark

sunrisehy2003 2004-07-28

打赏
举报

delete from a
where id not in (select min(id)as id ,username,age from a group by username,age)

chenqinzeng 2004-07-28

打赏
举报

删除重复的记录，只剩下一条
delete from table1
from (select max(c)as cc,a,b from table1 group by a,b having count(*) > 1) as table_1
where (table1.c <> table_1.cc) and (table1.a = table_1.a) and (table1.b = table_1.b)

yesterday2000 2004-07-28

打赏
举报

select distinct *
into #a
from a
where username in
(select max(username)
from a
group by username
having (*)>1)

delete a from a a,#a b where a.username=b.username and a.age=b.age

insert into
a (usernaem,age)
select username,age from #a

chinaandys 2004-07-28

打赏
举报

select * from taa
where id not in(select min(id) from taa group by username,age)

futulove 2004-07-28

打赏
举报

delete from a where id not in (select id from (select min(id)as id,username from a group by username ) b)
这个好使测试过

futulove 2004-07-28

打赏
举报

TO:三楼
你这样好像把所有重复的都给删除了，一个也没留吧

futulove 2004-07-28

打赏
举报

TO:一楼
group by username,age 好像不行，你看给的数据他俩的都不一样

shanjimail888 2004-07-28

打赏
举报

delete from a where username in (select username from a group by username having count(username )>=2)

futulove 2004-07-28

打赏
举报

select id,username into #t from a group by username having count(*)>1
delete from (select * from a where username in(select username from #t)) a where a.id notin (select id from #t)
试试应该好使

skyboy0720 2004-07-28

打赏
举报

select min(id),username,age from 表 group by username,age

求sql句子 清理表内重复数据

相关推荐

求sql句子清理表内重复数据