Question

sql查询数据，查询全部速度很快，改成not in查询未销售就很慢，怎样优化谢谢！

查询全部的sql：SELECT SUM(ps.projectcount) as count, m.mediatypename, s.name FROM t_project p, t_projectsize ps, shannxi.dbo.t_surround_value_report ss, shannxi.dbo.t_surround_ad_data sv, shannxi.dbo.t_surround_district s, t_mediatype_infor m WHERE p.mediatypeinfoid in ( select id from fn_get_mediatype_infor(4) ) AND ss.dataid = s.id AND sv.id = ss.projectid AND p.id = ps.projectid AND p.dataid = sv.id AND p.isdelete=0 AND p.companyid = 2248 AND p.regionid=s.regionid AND s.regionid = 354 AND ss.datatype=2 AND ss.distance=-1 AND p.mediatypeinfoid=m.id GROUP BY m.mediatypename, s.name 改成not in查询未销售的sql:SELECT SUM(ps.projectcount) as count, m.mediatypename, s.name FROM t_project p, t_projectsize ps, shannxi.dbo.t_surround_value_report ss, shannxi.dbo.t_surround_ad_data sv, shannxi.dbo.t_surround_district s, t_mediatype_infor m WHERE p.mediatypeinfoid in ( select id from fn_get_mediatype_infor(4) ) AND ss.dataid = s.id AND sv.id = ss.projectid AND p.id = ps.projectid AND p.dataid = sv.id AND p.isdelete=0 AND p.companyid = 2248 AND p.regionid=s.regionid AND s.regionid = 354 AND ss.datatype=2 AND ss.distance=-1 AND p.mediatypeinfoid=m.id AND p.id NOT IN ( SELECT mm.mediaid FROM t_sale_agreement_media mm ) GROUP BY m.mediatypename, s.name





Answer 1

in 和not in 速度都会慢。改成not exists（）去写 会快一些。 上面有一个in 可以改成用exists 去写。