需求:
删除tb1数据但保留每1个tid的最近1条记录:版本:
mysql 5.0.83表tb1:
create table tb1
(
intime datetime,
tid varchar(10),
txt varchar(10)
);============================
假设插入数据:
insert into tb1 values('2011-04-11 12:00:00','1','test1');
insert into tb1 values('2011-04-11 13:00:00','1','test');
insert into tb1 values('2011-04-11 14:00:00','1','test');
insert into tb1 values('2011-04-11 15:00:00','1','test');insert into tb1 values('2011-04-11 12:00:00','2','test2');
insert into tb1 values('2011-04-11 13:00:00','2','test');
insert into tb1 values('2011-04-11 14:00:00','2','test');
insert into tb1 values('2011-04-11 15:00:00','2','test');insert into tb1 values('2011-04-11 12:00:00','3','test3');
insert into tb1 values('2011-04-11 13:00:00','3','test');
insert into tb1 values('2011-04-11 14:00:00','3','test');
insert into tb1 values('2011-04-11 15:00:00','3','test');==============================================
希望只保留tb1中的
('2011-04-11 15:00:00','1','test1');
('2011-04-11 15:00:00','2','test2');
('2011-04-11 15:00:00','3','test3');
这样的数据;注:
实际表中tid值可能不只3种。
删除tb1数据但保留每1个tid的最近1条记录:版本:
mysql 5.0.83表tb1:
create table tb1
(
intime datetime,
tid varchar(10),
txt varchar(10)
);============================
假设插入数据:
insert into tb1 values('2011-04-11 12:00:00','1','test1');
insert into tb1 values('2011-04-11 13:00:00','1','test');
insert into tb1 values('2011-04-11 14:00:00','1','test');
insert into tb1 values('2011-04-11 15:00:00','1','test');insert into tb1 values('2011-04-11 12:00:00','2','test2');
insert into tb1 values('2011-04-11 13:00:00','2','test');
insert into tb1 values('2011-04-11 14:00:00','2','test');
insert into tb1 values('2011-04-11 15:00:00','2','test');insert into tb1 values('2011-04-11 12:00:00','3','test3');
insert into tb1 values('2011-04-11 13:00:00','3','test');
insert into tb1 values('2011-04-11 14:00:00','3','test');
insert into tb1 values('2011-04-11 15:00:00','3','test');==============================================
希望只保留tb1中的
('2011-04-11 15:00:00','1','test1');
('2011-04-11 15:00:00','2','test2');
('2011-04-11 15:00:00','3','test3');
这样的数据;注:
实际表中tid值可能不只3种。
删除tb1数据但保留每1个tid的最近1条记录:。。
WHERE a.tid=b.tid AND a.intime<b.intime
mysql> delete a from tb1 a left join (select max(intime) as intime,tid from tb1
group by tid) b on a.tid=b.tid and a.intime=b.intime
-> where b.tid is null;
Query OK, 9 rows affected (0.03 sec)mysql> select * from tb1;
+---------------------+------+------+
| intime | tid | txt |
+---------------------+------+------+
| 2011-04-11 15:00:00 | 1 | test |
| 2011-04-11 15:00:00 | 2 | test |
| 2011-04-11 15:00:00 | 3 | test |
+---------------------+------+------+
3 rows in set (0.00 sec)mysql>
where exits (select 1 from tb where A.tid=tid and A.tid>tid)
where exists (select 1 from tb where A.tid=tid and A.tid>tid)
rucypli 的语句执行出错:
“SQL ERROR(1093):you can't specify target table 'a' for update in FROM clause”
mysql> delete A from tb1 A,( select max(intime) intime,tid from tb1 group by tid
) B where A.tid=B.tid AND A.intime<B.intime;
Query OK, 9 rows affected (0.09 sec)mysql> select * from tb1;
+---------------------+------+------+
| intime | tid | txt |
+---------------------+------+------+
| 2011-04-11 15:00:00 | 1 | test |
| 2011-04-11 15:00:00 | 2 | test |
| 2011-04-11 15:00:00 | 3 | test |
+---------------------+------+------+
3 rows in set (0.00 sec)