一个sql的问题,求指教.

[小小小小周]

表结构:
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取时间大的一行

[小小小小周]

更正 id 5和6的数据是一样.

[0轰隆隆0]

SELECT id,time,path FROM aaa GROUP BY path order by id asc

[0轰隆隆0]

刚才看错了

SELECT id,time,path FROM (SELECT id,time,path FROM aaa order by time desc) bbb  GROUP BY path

[wangqiang_lenovo]

[Quote=引用 3 楼 fulong258 的回复:]
刚才看错了

SQL code
SELECT id,time,path FROM (SELECT id,time,path FROM aaa order by time desc) bbb GROUP BY path
[/Quote]
3楼的正确。

[小小小小周]

还是不对哦,红色的6 大家注意下

[0轰隆隆0]

[Quote=引用 5 楼 mr_mablevi 的回复:]
还是不对哦,红色的6 大家注意下
[/Quote]

5和6的时间是一样的呀！

SELECT id,time,path FROM (SELECT id,time,path FROM aaa order by time desc,id desc) bbb  GROUP BY path

[zuoxingyu]

select max(id),time,path from aaa where not exists(select 1 from aaa a where a.path=aaa.path 
and a.time > aaa.time ) group by time,path

来接个分吧。

[ACMAIN_CHM]

参考下贴中的多种方法

http://topic.csdn.net/u/20091231/16/2f268740-391e-40f2-a15e-f243b2c925ab.html
[征集]分组取最大N条记录方法征集，及散分....

[ACMAIN_CHM]

其中一种MYSQL的解法如下。

mysql> select * from aaa;
+------+---------------------+-------------+
| 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>