not in 语句很慢，有什么好的解决方法？THK！

select FItemID from z_mrp_l where FItemID not in 
  (
select FItemID from z_mrp_l where (stk_qty=0 and plan_qty=0 and pur_qty=0 and chk_qty=0 and alt_qty=0 and test_qty=0)
  )

以上not in 语句很慢，有什么好的解决方法？THK！

SQL code

select FItemID 
from z_mrp_l t
where not exists(
    select 1 from z_mrp_l where stk_qty=0 and plan_qty=0 
                             and pur_qty=0 and chk_qty=0 
                             and alt_qty=0 and test_qty=0 
                             and FItemID = t.FItemID
)
--试试

如果只是求FItemID，有楼主子查询里面的就可以了，如果是其他的需求，试试以下语句，在FItemID上建索引
SQL code

select FItemID
from z_mrp_l t
where not exists(select 1 from z_mrp_l where FItemID=t.FItemID
and stk_qty=0 and plan_qty=0 and pur_qty=0 and chk_qty=0 and alt_qty=0 and test_qty=0
)

以上的语句还是慢 不单单只是求FItemID

在FItemID上建索引试试

SQL code

--除了上面的方法，下面也简化了下
select FItemID from z_mrp_l 
where not (stk_qty=0 and plan_qty=0 and pur_qty=0 
        and chk_qty=0 and alt_qty=0 and test_qty=0)

select FItemID from z_mrp_l where isNull(stk_qty+plan_qty+pur_qty+chk_qty+alt_qty+test_qty,0)<>0

看看你的执行计划看，哪边有问题

SQL code

select FItemID
from z_mrp_l
where stk_qty<>0 or plan_qty<>0 or pur_qty<>0 or chk_qty<>0 or alt_qty<>0 or test_qty<>0

与select FItemID from z_mrp_l where (stk_qty=0 and plan_qty=0 and pur_qty=0 and chk_qty=0 and alt_qty=0 and test_qty=0)
查询结果不一样的

SQL code

估计你这些字段有为null的,改为下面的试试
select FItemID
from z_mrp_l
where isnull(stk_qty,0)<>0 or isnull(plan_qty,0)<>0 
      or isnull(pur_qty,0)<>0 or isnull(chk_qty,0)<>0 
      or isnull(alt_qty,0)<>0 or isnull(test_qty,0)<>0