简单例子:
create table aaaa(
a varchar2(1),
b varchar2(1)
);
insert into aaaa
values('1','a');
insert into aaaa
values('1','b');
insert into aaaa
values('1','c');
insert into aaaa
values('1','d');
insert into aaaa
values('2','a');
insert into aaaa
values('2','b');
insert into aaaa
values('2','c');
结果:
SQL> select a,b,row_number() over(partition by a order by a) from aaaa;
A B ROW_NUMBER()OVER(PARTITIONBYAO
- - ------------------------------
1 a 1
1 b 2
1 c 3
1 d 4
2 a 1
2 b 2
2 c 3
如何查询出每个人每天中最晚一次记录时间,ORACLE MSSQL均可!
数据库结构为:
用户名 操作时间
MMM 2009-9-5 18:21:56
MMM 2009-9-6 8:35:20
MMM 2009-9-6 8:38:10
LL 2009-9-6 9:29:27
LL 2009-9-6 9:40:40
KKK 2009-9-6 10:09:50
KKK 2009-9-6 11:09:38
select * from
(SELECT USERID ,DATED ,row_number() over (partition by USERID order by USERID ,DATED desc) rn FROM aa
GROUP BY USERID ,DATED )
where rn=1