select * from sales a
where not exists (
select '1' from sales_relation b where
(
(a.s_id =b.object_id and b.type='0' and a.base_id=b.base_id)
or
(a.b_id =b.object_id and b.type='1' and a.base_id=b.base_id)
or
(a.c_id =b.object_id and b.type='2' and a.base_id=b.base_id)
)
);
请问以上检验语句有没有优化的方案,数据量都在500万以上;
where not exists (
select '1' from sales_relation b where
(
(a.s_id =b.object_id and b.type='0' and a.base_id=b.base_id)
or
(a.b_id =b.object_id and b.type='1' and a.base_id=b.base_id)
or
(a.c_id =b.object_id and b.type='2' and a.base_id=b.base_id)
)
);
请问以上检验语句有没有优化的方案,数据量都在500万以上;
2 改成
select a.* from sales a, sales_relation b
where a.base_id=b.base_id and (not
(a.s_id =b.object_id and b.type= '0 ' or
a.b_id =b.object_id and b.type= '1 ' or
a.c_id =b.object_id and b.type= '2 '))
试试是否快一点。
会比其他做法慢一些。做的索引,不仅仅在base_id上面。将所有的用到的字段,都要加入。
然后用B表Minus掉上面的记录就是你所要的范围了
应该会比你那个快点吧,没试过,你可以试试
如果没有效,我期待楼下的更好答案了呵呵
貌似更烦的会更慢,不过结果就不好说吧
抱歉我没写代码......懒了.......楼主如果有兴趣可以试一下呵呵