查询检验和检查结果数量 - SQL 语句优化

SELECT COUNT(0) FROM ( SELECT CR.OUTPAT_NUM AS "outpatNum", PIH.PAT_NAME AS "patName", '检验' AS "recordType", TO_CHAR(PLR.CRITICAL_DATE, 'yyyy-mm-dd hh24:mi:ss') AS "reportDate", NVL(PLR.LAB_REPORT_ITEM_NAME, ( SELECT M.DATA_VALUE_REMARK FROM SYSMN.MAV_EMR_CORE_DICT M WHERE M.BASICDATAVALUE_ID = PLR.LAB_REPORT_ITEM_CODE )) AS "reportItemName", PLR.REPORT_RESULT || PLR.UNIT AS "result", ( SELECT ESP.PERSON_NAME FROM SYSMN.EHR_SERVICE_PERSONAL ESP WHERE CR.DISPOSE_USER_ID = ESP.PERSON_ID ) AS "disposeDoctor", TO_CHAR(CR.LAST_TIME, 'yyyy-mm-dd hh24:mi:ss') AS "disposeDate", CR.CRITICAL_RECORD_ID AS "recordId", CR.FINISHED AS "finished", CR.INPAT_NUM AS "inpatNum", PIH.DEPT_ID AS "deptId", NVL(( SELECT ESP.PERSON_CODE FROM SYSMN.EHR_SERVICE_PERSONAL ESP WHERE PLR.CREATE_USER_ID = ESP.PERSON_ID ), ( SELECT ESP.PERSON_CODE FROM SYSMN.EHR_SERVICE_PERSONAL ESP WHERE ESP.PERSON_NAME = PLR.CREATE_USER_ID AND ROWNUM = 1 )) AS "reportDoctorNum", NVL(( SELECT ESP.PERSON_NAME FROM SYSMN.EHR_SERVICE_PERSONAL ESP WHERE PLR.CREATE_USER_ID = ESP.PERSON_ID ), PLR.CREATE_USER_ID) AS "reportDoctoerName", ( SELECT ESP.PERSON_CODE FROM SYSMN.EHR_SERVICE_PERSONAL ESP WHERE CR.DISPOSE_USER_ID = ESP.PERSON_ID ) AS "disposeDoctorNum", ( SELECT ESP.PERSON_CODE FROM SYSMN.EHR_SERVICE_PERSONAL ESP WHERE PL.REQ_DOCTOR_ID = ESP.PERSON_ID ) AS "reqDoctorNum", ( SELECT ESP.PERSON_NAME FROM SYSMN.EHR_SERVICE_PERSONAL ESP WHERE PL.REQ_DOCTOR_ID = ESP.PERSON_ID ) AS "reqDoctorName", ( SELECT SDD.DEPT_NAME FROM SYSMN.SYS_DEPT_DICT SDD WHERE PIH.DEPT_ID = SDD.DEPT_ID ) AS "deptName", PIH.RESPONSIBLE_DOCTOR_PERSON_ID AS "responsibleDoctorPersonId", ( SELECT ESP.PERSON_NAME FROM SYSMN.EHR_SERVICE_PERSONAL ESP WHERE PIH.RESPONSIBLE_DOCTOR_PERSON_ID = ESP.PERSON_ID ) AS "responsibleDoctorPersonName", TO_CHAR(PA.CREATE_DATETIME, 'yyyy-mm-dd hh24:mi:ss') AS "reqDate", TO_CHAR(NVL(CR.NURSE_NOTICE_TIME, CR.NURSE_LAST_TIME), 'yyyy-mm-dd hh24:mi:ss') AS "nurseLastTime", CR.NURSE_FINISHED AS "nurseFinished", NVL(( SELECT USER_NAME FROM SYSMN.SYS_USER SU WHERE SU.USER_ID = CR.NURSE_NOTICE_USERID ), SU4.USER_NAME) AS "disposeNurse", WARD.DEPT_ALIAS AS "wardDeptName", PIH.WARD_DEPT_ID AS "wardDeptId", PIH.BED_NO AS "bedNo", BD.BED_LABEL AS "bedLabel", (CASE WHEN WARD.HOSPITAL_AREA_NO = '01' THEN '城中' WHEN WARD.HOSPITAL_AREA_NO = '02' THEN '阳湖' ELSE '' END) AS "hospitalAreaNo" FROM EMR.CRITICAL_RECORD CR LEFT JOIN EMR.LAB_CRITICAL_RECORD PLR ON PLR.LAB_CRITICAL_ID = CR.ITEM_ID LEFT JOIN EMR.PATIENT_LAB PL ON PL.REQ_NUM = PLR.REQ_NUM LEFT JOIN EMR.PATIENT_IN_HOSPITAL PIH ON CR.INPAT_NUM = PIH.INPAT_NUM LEFT JOIN EMR.PATIENT_ORDERS PA ON PLR.REQ_NUM = PA.REQ_NUM AND PLR.SERIAL_NUM = PA.SERIAL_NUMBER LEFT JOIN SYSMN.SYS_USER SU4 ON SU4.USER_ID = CR.NURSE_USER_ID LEFT JOIN SYSMN.SYS_DEPT_DICT WARD ON WARD.DEPT_ID = PIH.WARD_DEPT_ID LEFT JOIN SYSMN.SYS_BED_DICT BD ON BD.WARD_DEPT_ID = PIH.WARD_DEPT_ID AND BD.BED_NO = PIH.BED_NO WHERE ( PIH.RESPONSIBLE_DOCTOR_PERSON_ID = ? OR ( PL.REQ_DOCTOR_ID = ? OR CR.DISPOSE_USER_ID = ? ) OR PIH.DEPT_ID IN ) AND PLR.CRITICAL_DATE >= TO_DATE(?, 'yyyy-mm-dd hh24:mi:ss') AND TO_DATE(?, 'yyyy-mm-dd hh24:mi:ss') >= PLR.CRITICAL_DATE AND PIH.DEPT_ID NOT IN ( ? ) AND CR.INPAT_NUM IS NOT NULL AND CR.OUTPAT_NUM IS NULL AND CR.RECOED_TYPE = '1' AND ( PLR.NOTICE_STATUS = '0' OR PLR.NOTICE_STATUS IS NULL ) UNION SELECT CR2.OUTPAT_NUM AS "outpatNum", PIH.PAT_NAME AS "patName", '检查' AS "recordType", TO_CHAR(PER2.DT_ALERT, 'yyyy-mm-dd hh24:mi:ss') AS "reportDate", NVL(PLI.EXAM_ITEM_NAME, ( SELECT M.DATA_VALUE_REMARK FROM SYSMN.MAV_EMR_CORE_DICT M WHERE M.BASICDATAVALUE_ID = PLI.EXAM_ITEM_ID )) AS "reportItemName", PER2.VAL_RSTRPTLAB AS "result", ( SELECT ESP.PERSON_NAME FROM SYSMN.EHR_SERVICE_PERSONAL ESP WHERE CR2.DISPOSE_USER_ID = ESP.PERSON_ID ) AS "disposeDoctor", TO_CHAR(CR2.LAST_TIME, 'yyyy-mm-dd hh24:mi:ss') AS "disposeDate", CR2.CRITICAL_RECORD_ID AS "recordId", CR2.FINISHED AS "finished", CR2.INPAT_NUM AS "inpatNum", PER2.CODE_EMP_ALERT AS "reportDoctorNum", PER2.NAME_EMP_ALERT AS "reportDoctoerName", PIH.DEPT_ID AS "deptId", ( SELECT ESP.PERSON_CODE FROM SYSMN.EHR_SERVICE_PERSONAL ESP WHERE CR2.DISPOSE_USER_ID = ESP.PERSON_ID ) AS "disposeDoctorNum", NVL(( SELECT ESP.PERSON_CODE FROM SYSMN.EHR_SERVICE_PERSONAL ESP WHERE PE.REQ_DOCTOR_ID = ESP.PERSON_ID ), ( SELECT ESP.PERSON_CODE FROM SYSMN.EHR_SERVICE_PERSONAL ESP WHERE PO.CREATE_PERSON_ID = ESP.PERSON_ID )) AS "reqDoctorNum", NVL(( SELECT ESP.PERSON_NAME FROM SYSMN.EHR_SERVICE_PERSONAL ESP WHERE PE.REQ_DOCTOR_ID = ESP.PERSON_ID ), ( SELECT ESP.PERSON_NAME FROM SYSMN.EHR_SERVICE_PERSONAL ESP WHERE PO.CREATE_PERSON_ID = ESP.PERSON_ID )) AS "reqDoctorName", ( SELECT SDD.DEPT_NAME FROM SYSMN.SYS_DEPT_DICT SDD WHERE PIH.DEPT_ID = SDD.DEPT_ID ) AS "deptName", PIH.RESPONSIBLE_DOCTOR_PERSON_ID AS "responsibleDoctorPersonId", ( SELECT ESP.PERSON_NAME FROM SYSMN.EHR_SERVICE_PERSONAL ESP WHERE PIH.RESPONSIBLE_DOCTOR_PERSON_ID = ESP.PERSON_ID ) AS "responsibleDoctorPersonName", TO_CHAR(PO.CREATE_DATETIME, 'yyyy-mm-dd hh24:mi:ss') AS "reqDate", TO_CHAR(CR2.NURSE_NOTICE_TIME, 'yyyy-mm-dd hh24:mi:ss') AS "nurseLastTime", CR2.NURSE_FINISHED AS "nurseFinished", NVL(( SELECT USER_NAME FROM SYSMN.SYS_USER SU WHERE SU.USER_ID = CR2.NURSE_NOTICE_USERID ), SU4.USER_NAME) AS "disposeNurse", WARD.DEPT_ALIAS AS "wardDeptName", PIH.WARD_DEPT_ID AS "wardDeptId", PIH.BED_NO AS "bedNo", BD.BED_LABEL AS "bedLabel", (CASE WHEN WARD.HOSPITAL_AREA_NO = '01' THEN '城中' WHEN WARD.HOSPITAL_AREA_NO = '02' THEN '阳湖' ELSE '' END) AS "hospitalAreaNo" FROM EMR.CRITICAL_RECORD CR2 LEFT JOIN EMR.WJZ_MAG PER2 ON PER2.ID_ALERT = CR2.ITEM_ID LEFT JOIN EMR.PATIENT_EXAM_ITEM PLI ON PLI.REQ_NUM = PER2.NO_APPLYFORM AND PLI.SERIAL_NUMBER = PER2.SUBNO_APPLYFORM LEFT JOIN EMR.PATIENT_EXAM PE ON PE.REQ_NUM = PER2.NO_APPLYFORM LEFT JOIN EMR.PATIENT_IN_HOSPITAL PIH ON CR2.INPAT_NUM = PIH.INPAT_NUM LEFT JOIN EMR.PATIENT_ORDERS PO ON PER2.NO_APPLYFORM = PO.REQ_NUM AND PO.SERIAL_NUMBER = PER2.SUBNO_APPLYFORM LEFT JOIN SYSMN.SYS_USER SU4 ON SU4.USER_ID = CR2.NURSE_USER_ID LEFT JOIN SYSMN.SYS_DEPT_DICT WARD ON WARD.DEPT_ID = PIH.WARD_DEPT_ID LEFT JOIN SYSMN.SYS_BED_DICT BD ON BD.WARD_DEPT_ID = PIH.WARD_DEPT_ID AND BD.BED_NO = PIH.BED_NO WHERE ( PIH.RESPONSIBLE_DOCTOR_PERSON_ID = ? OR PIH.DEPT_ID IN ) AND PER2.DT_ALERT >= TO_DATE(?, 'yyyy-mm-dd hh24:mi:ss') AND TO_DATE(?, 'yyyy-mm-dd hh24:mi:ss') >= PER2.DT_ALERT AND PIH.DEPT_ID NOT IN ( ? ) AND CR2.OUTPAT_NUM IS NULL AND CR2.RECOED_TYPE = '4') tmp_count