Question

sql IN语句如何优化？

delete from acct_item where subs_id not in(select subs_id from subs);
我要处理一百万条数据，这条语句删了我近三个小时时间，求优化，积分不是问题，答出来可以加分。万分感谢！

Answer 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

Answer 2

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

追问

这个select 1不明白什么意思啊

追答

简单理解就是不查询具体列，只要有值就显示1
这个 语句 你 能用 否 ？

Answer 3

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

Answer 4

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

Answer 5

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

追问

这个select 1不明白什么意思啊