表结构:
CREATE TABLE `aaa` (
`id` int(11) DEFAULT NULL,
`time` datetime DEFAULT NULL,
`path` varchar(100) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
数据:
insert into `aaa`(`id`,`time`,`path`) values (1,'2010-11-25 14:33:00','~/a/001.jpg');
insert into `aaa`(`id`,`time`,`path`) values (2,'2010-11-25 14:33:00','~/a/002.jpg');
insert into `aaa`(`id`,`time`,`path`) values (3,'2010-11-25 14:33:00','~/a/003.jpg');
insert into `aaa`(`id`,`time`,`path`) values (4,'2010-11-25 14:35:00','~/a/014.jpg');
insert into `aaa`(`id`,`time`,`path`) values (5,'2010-11-25 14:36:00','~/a/014.jpg');
insert into `aaa`(`id`,`time`,`path`) values (6,'2010-11-25 14:36:00','~/a/014.jpg');
insert into `aaa`(`id`,`time`,`path`) values (7,'2010-11-25 14:34:00','~/a/014.jpg');
id 4和5的数据是一样的,需要得到的结果:1 2010-11-25 14:33:00 ~/a/001.jpg
2 2010-11-25 14:33:00 ~/a/002.jpg
3 2010-11-25 14:33:00 ~/a/003.jpg
6 2010-11-25 14:36:00 ~/a/014.jpg
要求是:只要取出path不同的,相同的path取时间大的一行
CREATE TABLE `aaa` (
`id` int(11) DEFAULT NULL,
`time` datetime DEFAULT NULL,
`path` varchar(100) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
数据:
insert into `aaa`(`id`,`time`,`path`) values (1,'2010-11-25 14:33:00','~/a/001.jpg');
insert into `aaa`(`id`,`time`,`path`) values (2,'2010-11-25 14:33:00','~/a/002.jpg');
insert into `aaa`(`id`,`time`,`path`) values (3,'2010-11-25 14:33:00','~/a/003.jpg');
insert into `aaa`(`id`,`time`,`path`) values (4,'2010-11-25 14:35:00','~/a/014.jpg');
insert into `aaa`(`id`,`time`,`path`) values (5,'2010-11-25 14:36:00','~/a/014.jpg');
insert into `aaa`(`id`,`time`,`path`) values (6,'2010-11-25 14:36:00','~/a/014.jpg');
insert into `aaa`(`id`,`time`,`path`) values (7,'2010-11-25 14:34:00','~/a/014.jpg');
id 4和5的数据是一样的,需要得到的结果:1 2010-11-25 14:33:00 ~/a/001.jpg
2 2010-11-25 14:33:00 ~/a/002.jpg
3 2010-11-25 14:33:00 ~/a/003.jpg
6 2010-11-25 14:36:00 ~/a/014.jpg
要求是:只要取出path不同的,相同的path取时间大的一行
SELECT id,time,path FROM (SELECT id,time,path FROM aaa order by time desc) bbb GROUP BY path
5和6的时间是一样的呀!SELECT id,time,path FROM (SELECT id,time,path FROM aaa order by time desc,id desc) bbb GROUP BY path
and a.time > aaa.time ) group by time,path来接个分吧。
[征集]分组取最大N条记录方法征集,及散分....
+------+---------------------+-------------+
| id | time | path |
+------+---------------------+-------------+
| 1 | 2010-11-25 14:33:00 | ~/a/001.jpg |
| 2 | 2010-11-25 14:33:00 | ~/a/002.jpg |
| 3 | 2010-11-25 14:33:00 | ~/a/003.jpg |
| 4 | 2010-11-25 14:35:00 | ~/a/014.jpg |
| 5 | 2010-11-25 14:36:00 | ~/a/014.jpg |
| 6 | 2010-11-25 14:36:00 | ~/a/014.jpg |
| 7 | 2010-11-25 14:34:00 | ~/a/014.jpg |
+------+---------------------+-------------+
7 rows in set (0.06 sec)mysql> select *
-> from (select * from aaa order by path,time desc,id desc) t
-> group by path;
+------+---------------------+-------------+
| id | time | path |
+------+---------------------+-------------+
| 1 | 2010-11-25 14:33:00 | ~/a/001.jpg |
| 2 | 2010-11-25 14:33:00 | ~/a/002.jpg |
| 3 | 2010-11-25 14:33:00 | ~/a/003.jpg |
| 6 | 2010-11-25 14:36:00 | ~/a/014.jpg |
+------+---------------------+-------------+
4 rows in set (0.08 sec)mysql>