sql IN语句如何优化？

2025-05-09 20:56:37

推荐回答（5个）

回答1：

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

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) 实收

FROM V_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

回答2：

delete from acct_item a where not exists (select 1 from subs b where a.subs_id=b.subs_id )

回答3：

一般in用 exist可以优化，也可以转换成多个or条件

回答4：

改成not exists，关联字段分别加索引

回答5：

delete from acct_item a where not exists (select 1 from subs where subs_id=a.subs_id )