//主查询语句
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去掉也可以，是什么原因造成的查询速度降了下来，有什么好的方法在保证查询结果的情况下，提升查询速度？