34,597
社区成员
发帖
与我相关
我的任务
分享一条语句如何删除重复值
id name
1 aa
1 aa
2 bb
3 bb
4 cc
1 aa
4 cc
id和nam都是普通的字段而已
请问大虾们：如何用一条SQL语句来删除重复值，比如不用临时表什么的等
结果
id name
1 aa
2 bb
3 bb
4 cc
谢谢
1 aa
1 aa
2 bb
3 bb
4 cc
1 aa
4 cc
id和nam都是普通的字段而已
请问大虾们：如何用一条SQL语句来删除重复值，比如不用临时表什么的等
结果
id name
1 aa
2 bb
3 bb
4 cc
谢谢
...全文
请发表友善的回复…
发表回复
b3727180 2010-04-06
- 打赏
- 举报
10 up
QQHRSWB 2010-04-03
- 打赏
- 举报
在表中增加一列，做为唯一值就可以 ,比如 UID IDENTITY ,到时就可以把多余的数据删除
Maryneet 2010-03-13
- 打赏
- 举报
不知道在那里面能不能用if条件判断 slelect count(*) from 表a where id=**and name=**
if ((int)cmd.Excuesclar()>0)
delete from 表a where id=**
if ((int)cmd.Excuesclar()>0)
delete from 表a where id=**
aspnetfeizi 2009-12-27
- 打赏
- 举报
[Quote=引用 13 楼 yangsnow_rain_wind 的回复:]
SQL code
经典尝试declare@tabletable (idint,namenvarchar(10))insertinto@tableselect1,'aa'unionallselect1,'aa'unionallselect2,'bb'unionallselect3,'bb'unionallselect4,'cc'unionallselect1,'aa'unionallselect4,?-
[/Quote]
请问为什么出现'row_number' 不是可以识别的函数名，SQL2000和05一样
SQL code
经典尝试declare@tabletable (idint,namenvarchar(10))insertinto@tableselect1,'aa'unionallselect1,'aa'unionallselect2,'bb'unionallselect3,'bb'unionallselect4,'cc'unionallselect1,'aa'unionallselect4,?-
[/Quote]
请问为什么出现'row_number' 不是可以识别的函数名，SQL2000和05一样
lsh2216024 2009-12-16
- 打赏
- 举报
[Quote=引用 14 楼 addua 的回复:]
没这么复杂吧，先distinct然后插入表.
[/Quote]
支持
没这么复杂吧，先distinct然后插入表.
[/Quote]
支持
r_yuan 2009-12-13
- 打赏
- 举报
来学习了
hzvcan 2009-11-24
- 打赏
- 举报
[Quote=引用 13 楼 yangsnow_rain_wind 的回复:]
SQL code
经典尝试declare@tabletable (idint,namenvarchar(10))insertinto@tableselect1,'aa'unionallselect1,'aa'unionallselect2,'bb'unionallselect3,'bb'unionallselect4,'cc'unionallselect1,'aa'unionallselect4,?-
[/Quote]
没有见过这种用法。学习。
SQL code
经典尝试declare@tabletable (idint,namenvarchar(10))insertinto@tableselect1,'aa'unionallselect1,'aa'unionallselect2,'bb'unionallselect3,'bb'unionallselect4,'cc'unionallselect1,'aa'unionallselect4,?-
[/Quote]
没有见过这种用法。学习。
lovezx1028 2009-11-23
- 打赏
- 举报
up
sucong 2009-05-05
- 打赏
- 举报
我也在找这样的语句.我知道用临时表可以，但是,在sybase里的话，要每次都建个新表麻烦.
还是自己多动下脑子,自己写个吧.
还是自己多动下脑子,自己写个吧.
lzfrab 2009-02-13
- 打赏
- 举报
13楼最先正解，也是我想要的答案
delete a
from (
select id,name,rn = row_number() over (partition by id,name order by id) from @table
) a where rn > 1
谢谢各位
delete a
from (
select id,name,rn = row_number() over (partition by id,name order by id) from @table
) a where rn > 1
谢谢各位
lingfa0511 2009-02-12
- 打赏
- 举报
谢谢各位！学习了！
renzaijiang 2009-02-12
- 打赏
- 举报
up
lzfrab 2009-02-12
- 打赏
- 举报
谢谢各位，学习了...
wangxiong26261 2009-02-11
- 打赏
- 举报
select *
from
(select id,Row_number()over(partition by id,name order by id) as cnt
from table) As A
where cnt =1
from
(select id,Row_number()over(partition by id,name order by id) as cnt
from table) As A
where cnt =1
huang123307 2009-02-11
- 打赏
- 举报
[Quote=引用 8 楼 tippointGmail 的回复:]
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(…
[/Quote]
这种看似可以.
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(…
[/Quote]
这种看似可以.
pengpzy 2009-02-11
- 打赏
- 举报
关注。。。
酒剑仙 2009-02-11
- 打赏
- 举报
是ORCALE的话 其实是很简单的
是SQLSERVER的话 我不知道
ORCALE 查询数据是把数据行的ROWID查出来，你把ROWID比较大的删除掉，就可以了。
比如：
delete test where rowid not in(select max(a.rowid)from
test a,test b where a.id=b.id and a.name=b.name and a.rowid=b.rowid group by a.name,a.id)
是SQLSERVER的话 我不知道
ORCALE 查询数据是把数据行的ROWID查出来，你把ROWID比较大的删除掉，就可以了。
比如：
delete test where rowid not in(select max(a.rowid)from
test a,test b where a.id=b.id and a.name=b.name and a.rowid=b.rowid group by a.name,a.id)
bushy 2009-02-11
- 打赏
- 举报
[Quote=引用 19 楼 move_stepbystep 的回复:]
引用 17 楼 move_stepbystep 的回复:
delete from tablename where id not in (select max(id) from tablename group by id,name)
不对,要把这个tablename加一个自增长字段TID
delete from tablename where TID not in (select max(TID) from tablename group by id,name)
[/Quote]
是的，19楼的方法最普遍，简单
引用 17 楼 move_stepbystep 的回复:
delete from tablename where id not in (select max(id) from tablename group by id,name)
不对,要把这个tablename加一个自增长字段TID
delete from tablename where TID not in (select max(TID) from tablename group by id,name)
[/Quote]
是的，19楼的方法最普遍，简单
qq3068703874 2009-02-10
- 打赏
- 举报
郁闷 什么啊 气死了
zhangzhao123 2009-02-10
- 打赏
- 举报
来学习
加载更多回复（91）
