1.代表Post CREATE TABLE Post ( ID int(11) NOT NULL AUTO_INCREMENT COMMENT '主键', PublishTime datetime NOT NULL COMMENT '发帖时间', UserID int(11) NOT NULL COMMENT '用户ID', PRIMARY KEY (ID), KEY UserID_IDX (UserID), KEY PublishTime_IDX (PublishTime) ); 2,问题:显示最新发表的5条帖子，可是每一个用户的帖子数不能超过2条 3.例子：insert Post(PublishTime,UserID) values('2014-02-15 00:00:00',1),('2014-02-13 00:00:00',1),('2014-02-14 00:00:00',2),('2014-02-14 00:00:00',1),('2014-02-15 00:00:00',2),('2014-02-17 00:00:00',2),('2014-02-19 00:00:00',3);code
+----+---------------------+--------+ | ID | PublishTime | UserID | +----+---------------------+--------+ | 1 | 2014-02-15 00:00:00 | 1 | | 2 | 2014-02-13 00:00:00 | 1 | | 3 | 2014-02-14 00:00:00 | 2 | | 4 | 2014-02-14 00:00:00 | 1 | | 5 | 2014-02-15 00:00:00 | 2 | | 6 | 2014-02-17 00:00:00 | 2 | | 7 | 2014-02-19 00:00:00 | 3 | +----+---------------------+--------+it
4 解决：select * from Post a where 2>(select count(*) from Post where UserID=a.UserID and PublishTime>a.PublishTime) order by PublishTime desc limit 5; +----+---------------------+--------+ | ID | PublishTime | UserID | +----+---------------------+--------+ | 7 | 2014-02-19 00:00:00 | 3 | | 6 | 2014-02-17 00:00:00 | 2 | | 1 | 2014-02-15 00:00:00 | 1 | | 5 | 2014-02-15 00:00:00 | 2 | | 4 | 2014-02-14 00:00:00 | 1 | +----+---------------------+--------+ 5.解决本质：date