3,491
社区成员
发帖
与我相关
我的任务
分享
select m.sn,m.cn,m.sm,m.cn1,m.am,m.mm
from
(select c.subjname sn,
count(a.debitamount)over(partition by a.pk_accsubj) cn,
sum(a.creditamount)over(partition by a.pk_accsubj) sm,
count(a.debitamount)over(partition by a.pk_accsubj) cn1,
avg(a.creditamount)over(partition by a.pk_accsubj) am,
max(to_number(a.creditamount))over(partition by a.pk_accsubj) mm,
row_number()over(partition by c.subjname order by c.subjname) rn
from detail a, bd_accsubj b,bd_accsubj c
where a.direction = 'C'
and a.pk_accsubj = b.pk_accsubj
and c.subjcode = substr(b.subjcode,1,4)
order by b.subjcode) m
where m.rn = 1;