对下表tb进行统计
SUBJECTID ACCTID DEBIT CREDIT STATUSDATE
1 1 800 800 2009-2-09 0:03:28
2 1 800 800 2009-2-10 0:03:28
3 1 800 800 2009-2-10 0:03:28
4 2 700 800 2009-2-11 0:03:28
5 2 700 800 2009-2-12 0:03:28
6 2 700 800 2009-2-12 0:03:28
7 3 900 800 2009-2-13 0:03:28
8 3 900 800 2009-2-13 0:03:28
9 3 900 800 2009-2-13 0:03:28要求:统计出每个ACCTID对应离当前日期最近第一条OVERAGE记录(其中OVERAGE是DEBIT - CREDIT计算出来的)结果:有2列 ACCTID,OVERAGE
ACCTID这一列每一项都是唯一的(统计时根据ACCTID分组)即可以做主键。
统计出的结果是
ACCTID OVERAGE
2 0
5 -100
7 100这个用PL/SQL语言怎么实现,高手来指教,十万火急啊!!
(最好执行效率能够比较高的语句,不要存储过程)
SUBJECTID ACCTID DEBIT CREDIT STATUSDATE
1 1 800 800 2009-2-09 0:03:28
2 1 800 800 2009-2-10 0:03:28
3 1 800 800 2009-2-10 0:03:28
4 2 700 800 2009-2-11 0:03:28
5 2 700 800 2009-2-12 0:03:28
6 2 700 800 2009-2-12 0:03:28
7 3 900 800 2009-2-13 0:03:28
8 3 900 800 2009-2-13 0:03:28
9 3 900 800 2009-2-13 0:03:28要求:统计出每个ACCTID对应离当前日期最近第一条OVERAGE记录(其中OVERAGE是DEBIT - CREDIT计算出来的)结果:有2列 ACCTID,OVERAGE
ACCTID这一列每一项都是唯一的(统计时根据ACCTID分组)即可以做主键。
统计出的结果是
ACCTID OVERAGE
2 0
5 -100
7 100这个用PL/SQL语言怎么实现,高手来指教,十万火急啊!!
(最好执行效率能够比较高的语句,不要存储过程)
from
(
select a.*,row_number() over(partition by ACCTID order by STATUSDATE desc) rn
from
table a)
where rn=a
from
(
select a.*,row_number() over(partition by ACCTID order by STATUSDATE desc) rn
from
table a )
where rn=1
union all select 1,800,800,'2009-2-10 0:03:28' from dual
union all select 1,800,800,'2009-2-10 0:03:28' from dual
union all select 2,700,800,'2009-2-11 0:03:28' from dual
union all select 2,700,800,'2009-2-12 0:03:28' from dual
union all select 2,700,800,'2009-2-12 0:03:28' from dual
union all select 3,900,800,'2009-2-13 0:03:28' from dual
union all select 3,900,800,'2009-2-13 0:03:28' from dual
union all select 3,900,800,'2009-2-13 0:03:28' from dual
)
select ACCTID,(DEBIT - CREDIT) OVERAGE
from
(
select tt.*,row_number() over(partition by ACCTID order by STATUSDATE desc) rn
from
tt
) where rn=1
(select SUBJECTID,DEBIT - CREDIT OVERAGE,row_number() over(partition by ACCTID order by to_date(STATUSDATE,'yyyy-mm-dd hh24:mi:ss') desc, SUBJECTID) rn from tb)
where rn=1;
ACCTID哪里有5、7的记录啊
from
(
select a.*,row_number() over(partition by ACCTID order by STATUSDATE desc) rn
from
table a )
where rn=1