我有点困惑如何为某些场景编写查询
桌子 : employee
create table employee
(
name varchar2(10),
sdate date,
subid number
);
insert into dummy values ('Arun',to_date('2016-03-01','YYYY-MM-DD'),123);
insert into dummy values ('Arun',to_date('2016-03-01','YYYY-MM-DD'),453);
insert into dummy values ('Raj',to_date('2016-03-01','YYYY-MM-DD'),12);
insert into dummy values ('Raj',to_date('2016-03-01','YYYY-MM-DD'),45);
insert into dummy values ('Raj',to_date('2016-03-01','YYYY-MM-DD'),16);
insert into dummy values ('Raj',to_date('2016-03-01','YYYY-MM-DD'),18);
insert into dummy values ('Darshan',to_date('2016-03-01','YYYY-MM-DD'),1600);
insert into dummy values ('Darshan',to_date('2016-03-01','YYYY-MM-DD'),1820);
下面是我在表中的数据
name,date,subid
Arun,2016-03-01,123
Arun,2016-03-01,453
Raj,2016-03-01,12
Raj,2016-03-01,45
Raj,2016-03-01,16
Raj,2016-03-01,18
Darshan,2016-03-01,1600
Darshan,2016-03-01,1820
maximum time
subid
现在我需要以参与的方式进行过滤name
each day
Arun -> count -> 2 -> 2016-03-01
Raj -> count -> 4 -> 2016-03-01
Darshan -> count -> 2 -> 2016-03-01
输出 :
name , subid , date
Raj , 12 , 2016-03-01
假设如果这种情况发生就像Raj
当时那样Darshan
并且对于那个名字same count
order name wise
pick lesser subid
name,date,subid
Arun,2016-03-01,123
Arun,2016-03-01,453
Raj,2016-03-01,12
Raj,2016-03-01,45
Raj,2016-03-01,16
Raj,2016-03-01,18
Darshan,2016-03-01,1600
Darshan,2016-03-01,1820
Darshan,2016-03-01,160
Darshan,2016-03-01,18
Arun -> count -> 2 -> 2016-03-01
Raj -> count -> 4 -> 2016-03-01
Darshan -> count -> 4 -> 2016-03-01
字母顺序
Arun -> count -> 2 -> 2016-03-01
Darshan -> count -> 4 -> 2016-03-01
Raj -> count -> 4 -> 2016-03-01
选择较小的补贴Darshan
输出 :
name , subid , date
Darshan , 18 , 2016-03-01
我无法确定如何实现它。
我想这样做partition
by
需要什么基础partition
?