一个查询有关问题.这次应该简单点了.

一个查询问题..这次应该简单点了..
create table reply(
id int not null primary key auto_increment
reply_id int not null
name varchar(10) not null default ''
content mediumtext not null
time int unsigned not null
);

insert into reply(id,reply_id,name,content,time) values('1','1','test1','test1','1');

insert into reply(id,reply_id,name,content,time) values('2','1','test2','test2','3');

insert into reply(id,reply_id,name,content,time) values('3','2','test3','test3','2');

insert into reply(id,reply_id,name,content,time) values('4','2','test4','test4','4');

insert into reply(id,reply_id,name,content,time) values('5','3','test5','test5','1');

insert into reply(id,reply_id,name,content,time) values('6','3','test6','test6','2');

insert into reply(id,reply_id,name,content,time) values('7','3','test7','test7','5');

要达到的目的:
按reply_id来进行划分,取相同reply_id值的所有记录中time值最大的一条记录,然后再把所有取出的值按time值降序排列

预期结果:
按此要求则首先取出了3条记录(因为只有3个不同的reply_id值):

id reply_id name content time
2 1 test2 test2 3
4 2 test4 test4 4
7 3 test7 test7 5

然后.再在此基础上.按time值大小进行排序.于是得到的最后的查询结果:

id reply_id name content time
7 3 test7 test7 5
4 2 test4 test4 4
2 1 test2 test2 3


这样的查询语句如何写?

------解决方案--------------------

SQL code

SELECT * FROM
(SELECT `id`, `reply_id`, `name`, `content`, `time` FROM `reply` ORDER BY `time` DESC) tbl
GROUP BY `reply_id`
ORDER BY `time` DESC

------解决方案--------------------

其中的一种解法如下。

SQL code

mysql> select * from reply;
+----+----------+-------+---------+------+
| id | reply_id | name  | content | time |
+----+----------+-------+---------+------+
|  1 |        1 | test1 | test1   |    1 |
|  2 |        1 | test2 | test2   |    3 |
|  3 |        2 | test3 | test3   |    2 |
|  4 |        2 | test4 | test4   |    4 |
|  5 |        3 | test5 | test5   |    1 |
|  6 |        3 | test6 | test6   |    2 |
|  7 |        3 | test7 | test7   |    5 |
+----+----------+-------+---------+------+
7 rows in set (0.00 sec)

mysql> select * from reply r
    -> where not exists (select 1 from reply where reply_id=r.reply_id and time>r.time)
    -> order by time desc;
+----+----------+-------+---------+------+
| id | reply_id | name  | content | time |
+----+----------+-------+---------+------+
|  7 |        3 | test7 | test7   |    5 |
|  4 |        2 | test4 | test4   |    4 |
|  2 |        1 | test2 | test2   |    3 |
+----+----------+-------+---------+------+
3 rows in set (0.00 sec)

mysql>