mysql查询最新的5条记录_mysql 查询表中不同状态的最新5条记录

创建表:status有4种状态,分别为1,2,3,4

CREATE TABLE `table_info` (

`id` int(11) NOT NULL AUTO_INCREMENT,

`createtime` datetime DEFAULT NULL,

`status` tinyint(4) DEFAULT '1',

PRIMARY KEY (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='表信息';

select @r1:=@r1+1 rn, t.* from (

select ti.id, ti.status, ti.createtime from table_info ti

where ti.status= 1 order by ti.createtime desc) t,(select @r1:=0) tn where @r1 < 5

union all

select @r2:=@r2+1 rn, t.* from (

select ti.id, ti.status, ti.createtime from table_info ti

where ti.status= 2 order by ti.createtime desc) t,(select @r2:=0) tn where @r2 < 5

union all

select @r3:=@r3+1 rn, t.* from (

select ti.id, ti.status, ti.createtime from table_info ti

where ti.status= 3 order by ti.createtime desc) t,(select @r3:=0) tn where @r3 < 5

union all

select @r4:=@r4+1 rn, t.* from (

select ti.id, ti.status, ti.createtime from table_info ti

where ti.status= 4 order by ti.createtime desc) t,(select @r4:=0) tn where @r4 < 5

查询结果