7,713
社区成员
发帖
与我相关
我的任务
分享sql问题高分求解
现有一个表，表中有两个字段：ID(主键，自动加一)和UserName两个字段，其中有一些UserName字段相同的记录，请教如何这些UserName字段相同的记录而只保留同名记录中ID号最小的那条记录？望不吝赐教。
...全文
请发表友善的回复…
发表回复
amtyuranus 2003-05-27
- 打赏
- 举报
select * from tablename where username not in (select username from tablename where count(username)>=2)
union select d.* from tablename as d,(select username,min(id) as id1 from tablename as a,(select username from tablename where count(username)>=2) as b where a.username=b.username group username) as c where c.username=d.username and d.id=c.id1
得到值
直接删掉可以这样做
delect d.* from tablename as d,(select username,min(id) as id1 from tablename as a,(select username from tablename where count(username)>=2) as b where a.username=b.username group username) as c where c.username=d.username and d.id!=c.id1
union select d.* from tablename as d,(select username,min(id) as id1 from tablename as a,(select username from tablename where count(username)>=2) as b where a.username=b.username group username) as c where c.username=d.username and d.id=c.id1
得到值
直接删掉可以这样做
delect d.* from tablename as d,(select username,min(id) as id1 from tablename as a,(select username from tablename where count(username)>=2) as b where a.username=b.username group username) as c where c.username=d.username and d.id!=c.id1
zhengjialon 2003-05-27
- 打赏
- 举报
SELECT Min(tablename.id) AS id之Min, tablename.UserName
FROM tablename
GROUP BY tablename.UserName;
FROM tablename
GROUP BY tablename.UserName;
Happiness 2003-05-27
- 打赏
- 举报
delete from tablename
where exists (
select * from tablename x
where x.UserName=tablename.UserName
and x.id<tablename.id
)
where exists (
select * from tablename x
where x.UserName=tablename.UserName
and x.id<tablename.id
)
