mysql分组取最大N条记录

最新推荐文章于 2024-06-11 10:15:00 发布

weixin_34409357

最新推荐文章于 2024-06-11 10:15:00 发布

阅读量350

点赞数

文章标签：数据库 python

原文链接：https://my.oschina.net/u/242853/blog/284169

版权

2019独角兽企业重金招聘Python工程师标准>>>

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);

+----+---------------------+--------+ | 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 | +----+---------------------+--------+

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.解决本质：

转载于:https://my.oschina.net/u/242853/blog/284169