//主查询语句

select isnull(SUM(ZJSL),0) as zjsl,

b.b_code,b.b_name,b.b_munit,b.b_remark,b.b_cprice,

sum(sib_cnum) as sib_cnum,

sum(sib_afterrevnum) as sib_afterrevnum,

sum(sib_afterchgnum) as sib_afterchgnum,

round(b_cpricesum(sib_afterchgnum),0) as amount

from Meas_SubItemBill sib

join Meas_Bill b on sib.sib_bid = b.row_id

left join

(

select v.sibSiid ,v.b_code, v.b_name, v.b_munit, v.b_price, v.b_remark, SUM(v.ZJSL) as ZJSL

from view_chgBills v

group by v.sibSiid, v.b_code, v.b_name, v.b_munit, v.b_price, v.b_remark

) r on r.sibSiid = sib.sib_siid and isnull(r.b_code,'') = isnull(b.b_code,'') and isnull(r.b_name,'') = isnull(b.b_name,'')

and isnull(r.b_munit,'') = isnull(b.b_munit,'')

and isnull(r.b_price,0) = isnull(b.b_cprice,0) and isnull(r.b_remark,'') = isnull(b.b_remark,'')

where sib_siid in (select id from c_tree('62b7d3e5-1f37-4fa4-a390-2d746419d58b'))

group by b.b_code, b.b_name, b.b_munit, b.b_cprice, b.b_remark

//其中的用到的表值函数

ALTER function [dbo].c_tree/

定义函数c_tree，输入参数为初始节点id 创建函数 根据节点id 找出所有子节点/

returns @t table(id varchar(50),parentid varchar(50),lev INT,byid varchar(50))/

定义表t用来存放取出的数据/

begin

declare @i int/

标志递归级别*/

set @i=1

insert @t select row_id,ssi_pid,@i ,byid=@initid from Base_SectionSubItem where row_id=@initid

while @@rowcount<>0

begin

set @i=@i+1

insert @t select row_id,ssi_pid,@i,@initid from Base_SectionSubItem as a,@t as b

where b.id=ssi_pid and b.lev=@i-1

end

return

END

如果将where sib_siid in (select id from c_tree('62b7d3e5-1f37-4fa4-a390-2d746419d58b')) 这个where语句去掉，查询速度就会提升或者将left join去掉也可以，是什么原因造成的查询速度降了下来，有什么好的方法在保证查询结果的情况下，提升查询速度？