在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');删除其中的冗余数据?
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');删除其中的冗余数据?
name、value重复?
from tt where name=a.name and a.id>b.id)
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)
from tt where name=a.name and a.id>b.id)
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语句的问题?
你的SQL语句
delete t1 from testtable t1,testtable t2
where t1.id>t2.id and t1.name=t2.name and t1.value=t2.value;
select t1.* from testtable t1,( select id from testtable t group by name
,value) t2 where t1.id = t2.id;
至少我执行了只有那个可以
DELETE a FROM njzg1 a INNER JOIN njzg1 b ON name=a.name and a.id>b.id and a.value=b.value;