delete from 表 a where number not in(select min(number) from 表 where username=a.username and password=a.password ) create temporary table tmp1(username varchar(10),password varchar(10),number smallint,PRIMARY KEY (username,password));insert into tmp1 select * from 表 union select * from 表delete from 表insert into 表 select * from tmp1drop table tmp1
lsxaa(小李铅笔刀) : 1, select * from 表 union select * from 表什么意思?自己和自己并多余? 2, create temporary table tmp1(username varchar(10),password varchar(10),number smallint,PRIMARY KEY (username,password));insert into tmp1 select * from 表 union select * from 表 这样不是会发生异常么?不能插入相同主键。。 3, delete from 表 a where number not in(select min(number) from 表 where username=a.username and password=a.password ) (提示a附近有语法错误?sql server2000,有关系么?没装mysql所以我没法调试它了) 这个删除的是number不等于0的记录啊。那怎么行??
delete from login a where number=0 and (select count(1) from login where username=a.username and password=a.password and number=a.number)>1
create temporary table tmp1(username varchar(10),password varchar(10),number smallint,PRIMARY KEY (username,password));insert into tmp1 select * from 表 union select * from 表delete from 表insert into 表 select * from tmp1drop table tmp1
如果可以保留两个以上,那自然是用join了
不知道mysql4.1对sql语句支持到哪个程度,不然还可以考虑用exists了条条大路通罗马啊
1, select * from 表 union select * from 表什么意思?自己和自己并多余?
2, create temporary table tmp1(username varchar(10),password varchar(10),number smallint,PRIMARY KEY (username,password));insert into tmp1 select * from 表 union select * from 表
这样不是会发生异常么?不能插入相同主键。。
3, delete from 表 a where number not in(select min(number) from 表 where username=a.username and password=a.password )
(提示a附近有语法错误?sql server2000,有关系么?没装mysql所以我没法调试它了)
这个删除的是number不等于0的记录啊。那怎么行??
where number=0
and (select count(1)
from login
where username=a.username
and password=a.password
and number=a.number)>1
至于重复的记录,你自己想办法吧