Mysql实现ROW_NUMBER() over(PARTITION BY column1 order by column2)

需要实现的需求为：按照ID分组，starttime排列，取出最新的starttime。以下没有考虑sql优化，仅仅只是实现功能。以下测试有误，数据量太小。

1.创建测试表并添加测试数据

Oracle：

create table test(id number,starttime date);

insert into test values(1,to_date('2016-01-12','YYYY-MM-DD'));
insert into test values(2,to_date('2016-05-12','YYYY-MM-DD'));
insert into test values(3,to_date('2016-05-12','YYYY-MM-DD'));

insert into test values(1,to_date('2016-02-12','YYYY-MM-DD'));
insert into test values(2,to_date('2016-06-12','YYYY-MM-DD'));
insert into test values(3,to_date('2016-06-12','YYYY-MM-DD'));

insert into test values(1,to_date('2016-03-12','YYYY-MM-DD'));
insert into test values(2,to_date('2016-01-12','YYYY-MM-DD'));
insert into test values(3,to_date('2016-01-12','YYYY-MM-DD'));

insert into test values(1,to_date('2016-04-12','YYYY-MM-DD'));
insert into test values(2,to_date('2016-02-12','YYYY-MM-DD'));
insert into test values(3,to_date('2016-02-12','YYYY-MM-DD'));

Mysql：

CREATE TABLE test (
  id int(10),
  starttime date
) ENGINE=InnoDB

insert into test values(1,'2016-01-12');
insert into test values(2,'2016-05-12');
insert into test values(3,'2016-05-12');

insert into test values(1,'2016-02-12');
insert into test values(2,'2016-06-12');
insert into test values(3,'2016-06-12');

insert into test values(1,'2016-03-12');
insert into test values(2,'2016-01-12');
insert into test values(3,'2016-01-12');

insert into test values(1,'2016-04-12');
insert into test values(2,'2016-02-12');
insert into test values(3,'2016-02-12');

排序之前的情况：

Oracle使用ROW_NUMBER() over(PARTITION BY column1 order by column2)分析函数

select *
  from (select t.id,
               t.starttime,
               ROW_NUMBER() over(PARTITION BY t.id order by t.starttime desc) as orderId
          from test t)
 where orderId = 1

执行结果：

Mysql：

select * from (
select t.id,t.starttime,count(*) as rank from test t
LEFT OUTER JOIN test r
on t.id = r.id
and t.starttime<=r.starttime
group by t.id,t.starttime
order by t.id, t.starttime desc
) s where s.rank = 1 order by s.id asc

执行结果

原文链接：https://blog.csdn.net/Howie_zhw/article/details/51706282