需要实现的需求为:按照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
执行结果