删除冗余数据

[WHW1984]

在mysql中一个表如下
create table testtable(
id int(4) not null primary key,
name varchar(12),
value varchar(12)
)

插入数据如下：
insert into testtable values(1, 'ww', 'ee');
insert into testtable values(2, 'ww', 'ee');
insert into testtable values(4, 'ww', 'ee');
insert into testtable values(5, 'ww', 'ee');
insert into testtable values(7, 'ww', 'ee');
insert into testtable values(8, 'qq', 'aa');
insert into testtable values(9, 'qq', 'aa');
insert into testtable values(10, 'qq', 'aa');
insert into testtable values(11, 'qq', 'aa');
insert into testtable values(12, 'rr', 'yy');
insert into testtable values(13, 'rr', 'yy');
insert into testtable values(14, 'rr', 'yy');
insert into testtable values(15, 'rr', 'yy');

删除其中的冗余数据？

[WWWWA]

冗余标准？
name、value重复？

[WHW1984]

对啊 把整条记录都删除，保留id最小的那个数据

[WWWWA]

delete from tt a where not exists(select 1
from tt where name=a.name and a.id>b.id)

[WHW1984]

Error Code : 1064
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'where not exists(select 1 from testtable where name=a.name and a.id>b.id)' at line 1
(0 ms taken)

[WWWWA]

delete from tt a where exists(select 1
from tt where name=a.name and a.id>b.id)

[WHW1984]

Error Code : 1064
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'where exists(select 1 from testtable where name=a.name and a.id>b.id)' at line 1
(0 ms taken)
是我的SQLyog的问题还是mysql版本的问题 还是你的sql语句的问题？

[WWWWA]

不会吧，在MYSQL5.1下没有问题
你的SQL语句

[WWWWA]

select * from from tt a where exists(select 1 from tt where name=a.name and a.id>b.id)

这样试试，我在SQLYOG　8.12+mysql5.32 测试没有问题

[berryluo]

LZ的mysql表类型是InnoDB还是MyISAM呢？建议看看这两种类型的差别。。。。。

[WHW1984]

这个我也试过 没问题的
但是我要的是删除啊 大哥

[WHW1984]

是InnoDB

[阿_布]

delete t1 from testtable t1,testtable t2
where t1.id>t2.id and t1.name=t2.name and t1.value=t2.value;

[huangdh12]

delete table a where a.id>(select min(id) from table b where a.name=b.name and a.value=b.value);

这个应该是可以

[storyxsj]

试试吧
select t1.* from testtable t1,( select id from testtable t group by name
,value) t2 where t1.id = t2.id;

[WHW1984]

zhoupuye正解
至少我执行了只有那个可以

[WWWWA]

测试了一下，
DELETE a FROM njzg1 a INNER JOIN njzg1 b ON name=a.name and a.id>b.id and a.value=b.value;