多表關聯想查a表中除去b表的可用not exists 效率比not in 更高

優化后的語句用時0.421秒

select john.*, (case when round((case john.sumcount when 0 then 0 else mycount * 100 / sumcount end), 2) > 100 then 100 else round((case john.sumcount when 0 then 0 else mycount * 100 / sumcount end), 2) end) lv from (select '路徑外' kind, cp_id, (select cp_node_name from lcp_master_node n where n.cp_id = o.cp_id and n.cp_node_id = o.cp_node_id) node_name, cp_node_id, LOCAL_ORDER_TEXT, LOCAL_ORDER_NO, MEASURE, IS_ANTIBIOTIC, MEASURE_UNITS, count(*) mycount, (select count(*) from (select count(*)from LCP_PATIENT_LOG_ORDER rwhere r.cp_id = '10896'group by patient_no)) sumcount from LCP_PATIENT_LOG_ORDER o where cp_id = '10896' group by cp_id, cp_node_id, LOCAL_ORDER_TEXT, LOCAL_ORDER_NO, MEASURE, IS_ANTIBIOTIC, MEASURE_UNITS) john where NOT EXISTS (select * from lcp_node_order_item i where i.cp_id = john.cp_id and i.cp_node_id = john.cp_node_id and i.CP_NODE_ORDER_TEXT = john.local_order_text) order by cp_node_id, mycount desc;

　　

轉載于:https://www.cnblogs.com/wuhailong/p/5089199.html

總結

以上是生活随笔為你收集整理的sql语句优化之not in的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。