如何优化的难题

[wzsjzjjxy]

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

[wzsjzjjxy]

一共八千多条财务记录，用了17分钟。没办法测试时候只能在前面加 top 限定结果集了。

[duanzhi1984]

子查询太多，先将子查询的数据做个结果集再链接起来。。

[飘零一叶]

可以把后面的那俩个子查询做成一个CTE.

[wzsjzjjxy]

你拷贝到SSMS里看一下，主要是多次使用那个CTE表[Quote=引用 2 楼 duanzhi1984 的回复:]
子查询太多，先将子查询的数据做个结果集再链接起来。。
[/Quote]

[ttjacky]

表上面有没有加索引先？

[304的的哥]

使用视图！

[abuying]

select中的子查询太多了。
其它你是想
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