查询检验检查记录数量 - Oracle 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", -- (SELECT esp.PERSON_NAME FROM sysmn.EHR_SERVICE_PERSONAL esp WHERE PL2.REQ_DOCTOR_ID = esp.PERSON_ID)as "reqDoctorIdName", 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", -- PL2.REQ_DOCTOR_ID as "reqDoctorId", 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", --(SELECT esp.PERSON_NAME FROM sysmn.EHR_SERVICE_PERSONAL esp WHERE PE2.REQ_DOCTOR_ID = esp.PERSON_ID)as "reqDoctorIdName", 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", -- PE2.REQ_DOCTOR_ID as "reqDoctorId", 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