TGFC Lifestyle - Powered by Discuz! Board

标题: [其他] 求问编程DX们一句SQL语句 [打印本页]

作者: use2 时间: 2007-12-25 15:57 标题: 求问编程DX们一句SQL语句

要求是用SQL语句删除一张表中所有重复的行(无ID)

如:
字段:username
   11
   22
   33
   11
   22
   33
   44
   55

希望语句执行后的结果是
字段:username
   44
   55

能用一句话实现就最好了，不能的话问题也不大:D

[ 本帖最后由 use2 于 2007-12-25 15:58 编辑 ]

作者: nvidia007 时间: 2007-12-25 16:00

先count 出来distinct username的个数，然后删除个数 >1的就行了

作者: use2 时间: 2007-12-25 16:03

还是不太明白
LS能说具体点吗？谢谢~

作者: iorilu 时间: 2007-12-25 16:28

delete from username where id in(select count(*)>1 from username group by id)

作者: use2 时间: 2007-12-25 16:50

多谢~!!:D

作者: cc0128 时间: 2007-12-25 16:52

不知道

[ 本帖最后由 cc0128 于 2007-12-25 18:08 编辑 ]

作者: 沉默の狙击手 时间: 2007-12-25 17:34

引用:

原帖由 iorilu 于 2007-12-25 16:28 发表
delete from username where id in(select count(*)>1 from username group by id)

SQLSERVER里这样写不行的吧

作者: zhdongb 时间: 2007-12-25 18:03

delete from table as a where (select count(*) from table as b where b.username=a.username)>1

作者: use2 时间: 2007-12-25 18:58

LS这个好象也不行哦.........

作者: zhdongb 时间: 2007-12-25 20:34

delete from table where username in (select username from (select username,count(*) from table group by username having count(*)>1))

作者: 左右中 时间: 2007-12-25 23:10

delete from Table
where username in
(select distinct username from Table group by username having count(username)>1)

作者: use2 时间: 2007-12-26 09:28

呵呵,LS的可以了
谢谢大家~!!!

作者: 打击者 时间: 2007-12-26 09:36

DELETE FROM [table]
WHERE EXISTS
               (SELECT COUNT(username), username
               FROM [table] t
               WHERE t.username = username
               GROUP BY username
               HAVING (COUNT(username) > 1))

欢迎光临 TGFC Lifestyle (http://bbs.tgfcer.com/)