SELECT * FROM(SELECT DISTINCT NVL(T.ID,A.ID) ID, A.TESTMETHODID, EMPNAME, STARTTIME, T.ENDTIME, T.EQUIPTID, T.EQUIPTNAME, T.ASSETSNO,
T.CREATEDTIME,NVL(T.TYPE,A.TYPE) TYPE, NVL(T.SCHEDULESTATUS,A.SCHEDULESTATUS) SCHEDULESTATUS, ISEMPSCHEDULE,
NVL(DBMS_LOB.SUBSTR(T.ORDERNO,3000,1),DBMS_LOB.SUBSTR(A.ORDERNO,3000,1)) ORDERNO, T.TEMPERATURE,
CONDITION, SAMPLELENGTH, SAMPLEWIDTH, SAMPLEHEIGHT, TESTNO, ORDERNUM,
CONSUMABLEINFOS, FAILREASON, A.ID PROJID, ISINSERTPROJ,
EMPBEFORESTARTTIME, EMPBEFOREENDTIME, EMPAFTERSTARTTIME, EMPAFTERENDTIME, A.PROJNO,
A.TESTMETHODNAME, EQUIPTCATEGORYID, ORIJSON,
B.NUM parkNum,C.TESTUSERNUM testUserNum,
CASE WHEN T1.ENDTIME IS NOT NULL THEN 2
WHEN T1.ACTIVATETIME IS NULL AND TO_CHAR(SYSDATE,'yyyy-MM-dd')> TO_CHAR(T.STARTTIME,'yyyy-MM-dd') THEN 1
ELSE 0 END AS taskStatus,A.LOCATIONID1 locationId
FROM (SELECT ID,TESTMETHODID,PROJNO,SCHEDULESTATUS,TESTMETHODNAME,TYPE,WM_CONCAT(ORDERNO) ORDERNO
FROM (SELECT
DISTINCT WP.ID,M.ID TESTMETHODID,WP.PROJNO,'-1' SCHEDULESTATUS,M.TESTMETHODNAME,
CASE WHEN M.TESTCATEGORY IN ('normal','instruction') THEN '1' ELSE '2' END TYPE,WO.ORDERNO
FROM T_HKWS_WL_PROJECT WP
LEFT JOIN T_HKWS_WL_ORDER WO ON WP.ID = WO.PROJID
LEFT JOIN T_HKWS_WL_ORDER_TASK WOT ON WOT.ORDERID = WO.ID
LEFT JOIN T_HKWS_WL_TASK WT ON WOT.TASKID = WT.ID
LEFT JOIN T_HKWS_WL_TEST_METHOD M ON M.ID = WT.TESTMETHODID
LEFT JOIN (SELECT PROJID,sum(COUNT) count FROM T_HKWS_WL_MTL_ARRIVE_RECORD GROUP BY PROJID) T1 ON T1.PROJID = WP.ID
WHERE t1.COUNT>= SAMPLECOUNT AND (wp.NODESTATUS IN ('待领样','试验中') OR wp.NODESTATUS LIKE '%报告%'))
GROUP BY ID,TESTMETHODID,PROJNO,SCHEDULESTATUS,TESTMETHODNAME,TYPE
UNION ALL
SELECT ID,TESTMETHODID,PROJNO,SCHEDULESTATUS,TESTMETHODNAME,TYPE,WM_CONCAT(ORDERNO) ORDERNO FROM (
SELECT
a.ID,M.ID TESTMETHODID,a.PROJNO,'-1' SCHEDULESTATUS,M.TESTMETHODNAME,
CASE WHEN M.TESTCATEGORY IN ('normal','instruction') THEN '1' ELSE '2' END TYPE,O.ORDERNO
FROM T_HKWS_YJ_PROJECT A
LEFT JOIN T_HKWS_YJ_PRODUCT B ON A.ID = B.PROJID
LEFT JOIN T_HKWS_YJ_ORDER O ON A.ID = O.PROJID
LEFT JOIN T_HKWS_YJ_ORDER_TASK OT ON OT.ORDERID = O.ID
LEFT JOIN T_HKWS_YJ_TASK T ON OT.TASKID = T.ID
LEFT JOIN T_HKWS_WL_TEST_METHOD M ON M.ID = T.TESTMETHODID
LEFT JOIN T_HKWS_YJ_ARRIVE_RECORD R ON R.PRODUCTID = B.ID
WHERE (B.NODESTATUS IN ('待领样','试验中') OR B.NODESTATUS LIKE '%报告%')
AND R.COUNT>=B.ORDERCOUNT)GROUP BY ID,TESTMETHODID,PROJNO,SCHEDULESTATUS,TESTMETHODNAME,TYPE ) A
LEFT JOIN T_HKWS_SCHEDULE_RESULT T ON A.ID = T.PROJID AND T.TESTMETHODID = A.TESTMETHODID
LEFT JOIN T_HKWS_EQUIPT A ON T.EQUIPTID = A.ID
LEFT JOIN T_HKWS_EQUIPT_LOCATION B ON A.LOCATIONID1 = B.ID
LEFT JOIN T_HKWS_WL_TEST_METHOD C ON T.TESTMETHODID = C.ID
LEFT JOIN (SELECT MIN(ACTIVATETIME) ACTIVATETIME,MAX(ENDTIME) ENDTIME,
EQUIPTID,PROJID,TESTMETHODID
FROM (SELECT A.ACTIVATETIME,A.ENDTIME,A.EQUIPTID,B.PROJID,B.TESTMETHODID FROM T_HKWS_WL_RUN_EQUIPT
A
LEFT JOIN T_HKWS_WL_RUN B ON A.RUNID = B.ID
UNION ALL
SELECT A.ACTIVATETIME,A.ENDTIME,A.EQUIPTID,B.PROJID,B.TESTMETHODID FROM T_HKWS_YJ_RUN_EQUIPT A
LEFT JOIN T_HKWS_YJ_RUN B ON A.RUNID = B.ID) GROUP BY EQUIPTID,PROJID,TESTMETHODID)T1 ON T1.PROJID = T.PROJID AND T1.TESTMETHODID = T.TESTMETHODID AND T.EQUIPTID = T1.EQUIPTID)K 帮我优化下这个sql
最新发布
博客内容包含一条SQL查询语句，使用select关键字从proj_info表中查询projid列，并通过wm_concat函数拼接b表的custname列。体现了SQL在数据查询和处理方面的应用。
扫一扫
4206
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
