mysql获取按日期排序获取最新的记录

最新推荐文章于 2024-09-18 14:11:14 发布

yygg329405

最新推荐文章于 2024-09-18 14:11:14 发布

阅读量2.4w

点赞数 1

分类专栏： mysql 文章标签： mysql 最新最新数据

mysql 专栏收录该内容

6 篇文章 0 订阅

订阅专栏

主要按照日期获得最新的数据；

今天记录两种方式，并不涉及效率等其他方面问题：

第一种，利用GROUP BY原理：

select * from (
	select * from `authenticationrecord` order by `authenticationtime` desc
) `temp`  group by merchantrid order by `authenticationtime` desc

第二种，利用mysql内置函数

select * from `test` where id in(select SUBSTRING_INDEX(group_concat(id order by `date` desc),',',1) from `test` group by category_id ) order by `date` desc

<<<<<<<内容完<><><>

试验脚本（自己玩）：

create table STAFF(
	id  int,
	sname  varchar(50),
dept  varchar(50),
salary  int,
edlevel  int,
hiredate datetime
)

insert into STAFF values('1','张三','开发部','2000','3','2009-10-11');
insert into STAFF values('2','李四','开发部','2500','3','2009-10-01');
insert into STAFF values('3','王五','设计部','2600','5','2010-10-02');
insert into STAFF values('4','王六','设计部','2300','4','2010-10-03');
insert into STAFF values('5','马七','设计部','2100','4','2010-10-06');
insert into STAFF values('6','赵八','销售部','3000','5','2010-10-05');
insert into STAFF values('7','钱九','销售部','3100','7','2010-10-07');
insert into STAFF values('8','孙十','销售部','3500','7','2010-10-06');


select group_concat(id order by hiredate desc) as test from staff
group by dept;

select * from STAFF;


select * from STAFF
group by dept

参考网站：http://blog.csdn.net/agileclipse/article/details/12184635