select t.checkRequestId,t.customerName,t.identifyType,t.identifyNo,t.mobilePhone1,b.orgName,b.orgNo,c.enterDate,d.createDate,c.checkTimes,d.operateType
from (select a.checkRequestId,a.enterDate,a.createDate,a.checkTimes from dvcicheckflow a where a.operateType = '04') c
left join DvciCheckInfo t on c.checkRequestId = t.checkRequestId
inner join DvciOrganization b on b.orgId = t.orgId
left join (select createDate,operateType,checkTimes,checkRequestId from DvciCheckFlow where (operatetype = '03' or operatetype = '05')) d on d.checkRequestId = t.checkRequestId
where d.checkTimes = c.checkTimes
order by t.orgId,t.checkRequestId,t.customerName,d.createDate;
執行計劃
1 PRIMARY <derived2> ALL 666762 Using temporary; Using filesort
1 PRIMARY t eq_ref PRIMARY,idx_dvcicheckinfo_orgid PRIMARY 8 c.checkRequestId 1 Using where
1 PRIMARY b eq_ref PRIMARY PRIMARY 4 datavessel.t.orgid 1
1 PRIMARY <derived3> ref <auto_key0> <auto_key0> 13 c.checkTimes,c.checkRequestId 10
3 DERIVED e ALL 666762 Using where
2 DERIVED a ALL 666762 Using where
uj5u.com熱心網友回復：
樓主，你至少也要格式化一下你的sql啊，方便大家幫你看
SELECT T.CHECKREQUESTID,
T.CUSTOMERNAME,
T.IDENTIFYTYPE,
T.IDENTIFYNO,
T.MOBILEPHONE1,
B.ORGNAME,
B.ORGNO,
C.ENTERDATE,
D.CREATEDATE,
C.CHECKTIMES,
D.OPERATETYPE
FROM (SELECT A.CHECKREQUESTID, A.ENTERDATE, A.CREATEDATE, A.CHECKTIMES
FROM DVCICHECKFLOW A
WHERE A.OPERATETYPE = '04') C
LEFT JOIN DVCICHECKINFO T
ON C.CHECKREQUESTID = T.CHECKREQUESTID
INNER JOIN DVCIORGANIZATION B
ON B.ORGID = T.ORGID
LEFT JOIN (SELECT CREATEDATE, OPERATETYPE, CHECKTIMES, CHECKREQUESTID
FROM DVCICHECKFLOW
WHERE (OPERATETYPE = '03' OR OPERATETYPE = '05')) D
ON D.CHECKREQUESTID = T.CHECKREQUESTID
WHERE D.CHECKTIMES = C.CHECKTIMES
ORDER BY T.ORGID, T.CHECKREQUESTID, T.CUSTOMERNAME, D.CREATEDATE;
uj5u.com熱心網友回復：
實在抱歉，以后一定注意，謝了樓上的兄弟。。大神速速出來。。不知道這個可不可以優化。。uj5u.com熱心網友回復：
(OPERATETYPE = '03' OR OPERATETYPE = '05')這句能否改成 （OPERATETYPE in ('03','05') )
sql貌似如果用or 會慢。 同時看下你sql在資料庫的執行計劃 對應表是否有索引。
uj5u.com熱心網友回復：
一般 OR 可以改寫成 UNION ALL轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/104834.html
標籤：開發
上一篇：浮動車大資料挖掘
下一篇：ORACLE 11GR2 ，建庫創建EM ， 在85%的時候提示Error securing Database Control, Database Contro