表名:bugs_activity
主键:无
要求:
在表里,bugs_when column字段(日期时间类型)有较多重复值(即两条记录该值相等),如果有两条该字段重复的记录,就把后面的加1秒。如:bugs_when column
2005-11-25 13:51:17
2005-11-25 13:51:17 --> 2005-11-25 13:51:18
2005-11-25 13:51:20
2005-11-25 13:51:58
2005-11-25 13:51:58 --> 2005-11-25 13:51:59谢谢了
主键:无
要求:
在表里,bugs_when column字段(日期时间类型)有较多重复值(即两条记录该值相等),如果有两条该字段重复的记录,就把后面的加1秒。如:bugs_when column
2005-11-25 13:51:17
2005-11-25 13:51:17 --> 2005-11-25 13:51:18
2005-11-25 13:51:20
2005-11-25 13:51:58
2005-11-25 13:51:58 --> 2005-11-25 13:51:59谢谢了
或者分两步
select max(id) AS id from news group by bugs_when having (count(0) > 1)
找出id
第二步
update news set bugs_when= bugs_when+0 + 1 where id in()
bugs_when timestamp );
insert into lk5 values
('2005-11-25 13:51:17'),
('2005-11-25 13:51:17'),
('2005-11-25 13:51:20'),
('2005-11-25 13:51:58'),
('2005-11-25 13:51:58');create temporary table tmp select * from lk5 group by bugs_when having count(1) > 1 union all
select * from lk5 group by bugs_when having count(1) = 1 ;
insert into tmp select date_add(bugs_when,interval 1 second) from lk5 group by bugs_when having count(1)>1;
truncate table lk5;
insert into lk5 select * from tmp order by bugs_when asc;
select * from lk5;
query result(5 records)
bugs_when
2005-11-25 13:51:17
2005-11-25 13:51:18
2005-11-25 13:51:20
2005-11-25 13:51:58
2005-11-25 13:51:59