with xmmxz (year,month,prjname,account,acctname,debit,credit,Row_Num)
as
(
select top 1565 year(t0.refdate) as 'year',month(t0.refdate) as 'Month',
t3.prjname,t1.account,t2.acctname,t1.debit,t1.credit,
row_number()over(partition by prjname,account,year(t0.refdate),month(t0.refdate) order by year(t0.refdate),month(t0.refdate)) as Row_Num
from ojdt t0 inner join jdt1 t1 on t1.transid=t0.transid
inner join oact t2 on t2.acctcode=t1.account
left join oprj t3 on t3.prjcode=t1.project
where t3.prjname is not null and t3.prjname<>'other' --测试使用,实际应用该条件不加
)
select *,(case
when debitsum-creditsum > 0 then '借'
when debitsum-creditsum = 0 then '平'
else '贷'
end) as '方向'
from
(select t2.year,t2.month,prjname,account,acctname,cast(debit as numeric(10,3)) as debit,
cast(credit as numeric(10,3)) as credit,Row_Num
,(select cast(sum(debit) as numeric(10,3))
from xmmxz t1
where t1.year=t2.year and t1.month=t2.month and t1.prjname=t2.prjname
and t1.account=t2.account and t1.Row_Num<=t2.Row_Num ) as debitsum
,(select cast(sum(credit) as numeric(10,3))
from xmmxz t1
where t1.year=t2.year and t1.month=t2.month and t1.prjname=t2.prjname
and t1.account=t2.account and t1.Row_Num<=t2.Row_Num ) as creditsum
from xmmxz as t2) as ttt
order by prjname,account,year,month我用SQL SERVER优化引擎结果性能提升为0
as
(
select top 1565 year(t0.refdate) as 'year',month(t0.refdate) as 'Month',
t3.prjname,t1.account,t2.acctname,t1.debit,t1.credit,
row_number()over(partition by prjname,account,year(t0.refdate),month(t0.refdate) order by year(t0.refdate),month(t0.refdate)) as Row_Num
from ojdt t0 inner join jdt1 t1 on t1.transid=t0.transid
inner join oact t2 on t2.acctcode=t1.account
left join oprj t3 on t3.prjcode=t1.project
where t3.prjname is not null and t3.prjname<>'other' --测试使用,实际应用该条件不加
)
select *,(case
when debitsum-creditsum > 0 then '借'
when debitsum-creditsum = 0 then '平'
else '贷'
end) as '方向'
from
(select t2.year,t2.month,prjname,account,acctname,cast(debit as numeric(10,3)) as debit,
cast(credit as numeric(10,3)) as credit,Row_Num
,(select cast(sum(debit) as numeric(10,3))
from xmmxz t1
where t1.year=t2.year and t1.month=t2.month and t1.prjname=t2.prjname
and t1.account=t2.account and t1.Row_Num<=t2.Row_Num ) as debitsum
,(select cast(sum(credit) as numeric(10,3))
from xmmxz t1
where t1.year=t2.year and t1.month=t2.month and t1.prjname=t2.prjname
and t1.account=t2.account and t1.Row_Num<=t2.Row_Num ) as creditsum
from xmmxz as t2) as ttt
order by prjname,account,year,month我用SQL SERVER优化引擎结果性能提升为0
其它你是想
row_number()over(partition by prjname,account,year(t0.refdate),month(t0.refdate) order by year(t0.refdate),month(t0.refdate)) as Row_Num
from ojdt t0 inner join jdt1 t1 on t1.transid=t0.transid
inner join oact t2 on t2.acctcode=t1.account
left join oprj t3 on t3.prjcode=t1.project
按prjname,account,year(t0.refdate),month(t0.refdate)分组后,按year(t0.refdate),month(t0.refdate)组内排序。判断大小 !
,(select cast(sum(debit) as numeric(10,3))
from xmmxz t1
where t1.year=t2.year and t1.month=t2.month and t1.prjname=t2.prjname
and t1.account=t2.account and t1.Row_Num<=t2.Row_Num ) as debitsum
,(select cast(sum(credit) as numeric(10,3))
from xmmxz t1
where t1.year=t2.year and t1.month=t2.month and t1.prjname=t2.prjname
and t1.account=t2.account and t1.Row_Num<=t2.Row_Num ) as creditsum
这两个可以全并成一表。(select cast(sum(credit) as numeric(10,3)), cast(sum(debit) as numeric(10,3)),t1.year,t1.month,t1.prjname,t1.account,t2.Row_Num
from xmmxz t1,xmmxz t2
where t1.year=t2.year and t1.month=t2.month and t1.prjname=t2.prjname
and t1.account=t2.account and t1.Row_Num<=t2.Row_Num ) as t3再与前面的xmmxz t2 串联 on t3.year=t2.year and t3.month=t2.month and t3.prjname=t2.prjname
and t3.account=t2.account and t3.row_num=t2.t2.Row_Num