id gbid title addtime
1 1 test1 2006-8-3 00:12:00
2 2 test2 2006-8-3 01:12:00
3 3 test3 2006-8-3 02:12:00
4 1 test4 2006-8-3 03:12:00
5 2 test5 2006-8-3 04:12:00
6 3 test6 2006-8-3 05:12:00
7 1 test7 2006-8-3 06:12:00
8 2 test8 2006-8-3 07:12:00
9 3 test9 2006-8-3 08:12:00
运行
select * from tb group by gbid
后,返回的结果:
1 1 test1 2006-8-3 00:12:00
2 2 test2 2006-8-3 01:12:00
3 3 test3 2006-8-3 02:12:00
但我现在要返回的结果时:
7 1 test7 2006-8-3 06:12:00
8 2 test8 2006-8-3 07:12:00
9 3 test9 2006-8-3 08:12:00SELECT MAX(addtime) FROM `tb` GROUP BY gbid; #取的只是 addtime 所以这个不行 SELECT MAX(addtime),title,id.............. FROM `tb` GROUP BY gbid;
只是取到了最新的addtime ,但没有取到任何新的其它信息 select * from tb where (gbid, addtime) in
( select gbid, max(addtime) from tb group by gbid )
只能用于mysql5,而我的服务器上装的是4
1 1 test1 2006-8-3 00:12:00
2 2 test2 2006-8-3 01:12:00
3 3 test3 2006-8-3 02:12:00
4 1 test4 2006-8-3 03:12:00
5 2 test5 2006-8-3 04:12:00
6 3 test6 2006-8-3 05:12:00
7 1 test7 2006-8-3 06:12:00
8 2 test8 2006-8-3 07:12:00
9 3 test9 2006-8-3 08:12:00
运行
select * from tb group by gbid
后,返回的结果:
1 1 test1 2006-8-3 00:12:00
2 2 test2 2006-8-3 01:12:00
3 3 test3 2006-8-3 02:12:00
但我现在要返回的结果时:
7 1 test7 2006-8-3 06:12:00
8 2 test8 2006-8-3 07:12:00
9 3 test9 2006-8-3 08:12:00SELECT MAX(addtime) FROM `tb` GROUP BY gbid; #取的只是 addtime 所以这个不行 SELECT MAX(addtime),title,id.............. FROM `tb` GROUP BY gbid;
只是取到了最新的addtime ,但没有取到任何新的其它信息 select * from tb where (gbid, addtime) in
( select gbid, max(addtime) from tb group by gbid )
只能用于mysql5,而我的服务器上装的是4
create table test1 (
id int not null AUTO_INCREMENT PRIMARY KEY,
gbid int not null,
title char(20) not null,
`addtime` timestamp not null default current_timestamp
) engine =myisam;insert into test1(gbid,title,`addtime`) values
(1,'test1','2006-8-3 00:12:00'),
(2,'test2','2006-8-3 01:12:00'),
(3,'test3','2006-8-3 02:12:00'),
(1,'test4','2006-8-3 03:12:00'),
(2,'test5','2006-8-3 04:12:00'),
(3,'test6','2006-8-3 05:12:00'),
(1,'test7','2006-8-3 06:12:00'),
(2,'test8','2006-8-3 07:12:00'),
(3,'test9','2006-8-3 08:12:00');select * from (select * from test1 order by id desc) T group by gbid;query result(3 records)
id gbid title addtime
7 1 test7 2006-08-03 06:12:00
8 2 test8 2006-08-03 07:12:00
9 3 test9 2006-08-03 08:12:00