一个查询问题..这次应该简单点了..
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>
