dwid data money
1 2013/5/26 23000.00
2 2013/5/3 23000.00
2 2013/5/1 22.00
3 2013/5/14 23000.00
3 2013/5/1 22.00
4 2013/5/1 30000.00 -----------------------------
要求出来的结果为dwid data money
1 2013/5/26 23000.00
2 2013/5/3 23000.00
3 2013/5/14 23000.00
4 2013/5/1 30000.00
1 2013/5/26 23000.00
2 2013/5/3 23000.00
2 2013/5/1 22.00
3 2013/5/14 23000.00
3 2013/5/1 22.00
4 2013/5/1 30000.00 -----------------------------
要求出来的结果为dwid data money
1 2013/5/26 23000.00
2 2013/5/3 23000.00
3 2013/5/14 23000.00
4 2013/5/1 30000.00
from (select row_number() over(partition by dd order by money desc) as rn,
dwid,
dd,
money
from test)
where rn = 1
with t as
(
select 1 dwid,to_date('2013/5/26','YYYY/MM/DD') date1,23000 money from dual
union all
select 2 dwid,to_date('2013/5/3','YYYY/MM/DD') m,23000 s from dual
union all
select 2 dwid,to_date('2013/5/3','YYYY/MM/DD') m,230000 s from dual
union all
select 2 dwid,to_date('2013/5/1','YYYY/MM/DD') m,22 s from dual
union all
select 3 dwid,to_date('2013/5/14','YYYY/MM/DD') m,23000 s from dual
union all
select 3 dwid,to_date('2013/5/1','YYYY/MM/DD') m,22 s from dual
union all
select 4 dwid,to_date('2013/5/1','YYYY/MM/DD') m,30000 s from dual
)
select dwid,date1,money from (
select dwid,date1,money,row_number()over(partition by dwid order by date1 desc,money desc) rn from t
) where rn=1如果存在相同日期取money 最大的记录
二 依据子查询的结果查出来所要的数据
(select dwid,max(data) from tab_name group by dwid);