[MySql]Óï¾äÓÅ»¯ÎÊÌ⣬ÇóÓÅ»¯

´úÂë
SQL code:

select i.fk_in_no,i.fk_vender_abbrev,i.fk_factory_abbrev,i.fk_part_no,i.fk_spec,i.fk_commodity,i.qty,
i.qty-
ifnull((select sum(o.qty)
from ve_report_outbound_detail o
where i.fk_in_no=o.fk_in_no
and i.fk_part_no=o.fk_vender_part_no
and i.fk_spec=o.fk_vender_spec
and i.fk_commodity=o.fk_vender_commodity
and i.fk_qty_unit_abbrev=o.fk_qty_unit_abbrev),0) as stock_qty,i.fk_qty_unit_abbrev,i.inbound_time
from ve_report_inbound_detail i
having stock_qty>0



ÊÓͼ£ºve_report_inbound_detail ºÍ ve_report_outbound_detail ¶¼Ö»Óв»µ½Ò»ÍòµÄÊý¾Ý
ÔõôÕâÌõÓï¾äÒªÖ´ÐÐ40¶àÃë

ÇóÓÅ»¯ лл´ó´ó

---

ÕâÊÇexplain ,ÄÇÁ½¸öshow indexΪ¿Õ
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY <derived3> ALL 7868
3 DERIVED m ALL Index_InMain_InNo 3253 Using where; Using temporary; Using filesort
3 DERIVED d ref Index_InDetail_InNo Index_InDetail_InNo 92 wms.m.in_no 1 Using where
2 DEPENDENT SUBQUERY <derived5> ALL 11180 Using where
5 DERIVED m ALL Index_InMain_OutNo 3154 Using where; Using temporary; Using filesort
5 DERIVED d ref Index_OutDetail_OutNo Index_OutDetail_OutNo 92 wms.m.out_no 1 Using where


ÁíÍâÓÐûÓÐÆäËü·½·¨¸ÄдÕâ¸öÓï