sql 语句 in的优化。

假设原来的句子是

select * from t1 where t1.f1 in (select t2.f2 from t2 where t2.f2=xxx)

和你的很类似

你用子查询 很慢

我们现在修改为：

select t1.* from t1 ,t2 where t1.f1 = t2.f2 and t2.f2=xxxx

这里使用了关联查询代替了子查询大大提高效率。

其次你可以考虑在表t1.f1上加索引，提高查询速度。

楼主测一下结果集是否正确

declare @lFAD01 int

set @lFAD01 = 605

SELECT

case a.VBL04 when 2 then '门诊挂号' else '门诊收费' end 业务类别

, b.AAO02 支付类型

, a.VBL14 支付方式

, SUM(case a.VBL05 when 4 then a.VBL13 else 0 END) 退费

, SUM(case a.VBL05 when 4 then 0 else a.VBL13 END) 收费

, SUM(a.VBL13) 实收

FROMV_VBL_Full a

JOIN AAO1 b ON a.VBL15 = b.AAO01

JOIN vaa1 c ON c.vaa01 = a.vaa01

WHERE VBL17 = @lFAD01 and a.VBL04 >= 2 and a.VBL04 <= 3

and not exists(select 1 from IAS1 where IAS1.VAK01=a.VAK01)

AND NOT EXISTS(SELECT 1 FROM V_VBL_Full AS a1 WHERE (a1.VBL01=a.VBL01 OR a.VBL01A=a1.VBL01) AND EXISTS(SELECT 1 FROM IAS1 WHERE VAK01=a1.VAK01))

GROUP BY case a.VBL04 when 2 then '门诊挂号' else '门诊收费' END,b.AAO02,a.VBL14

---