SQL 查询语句语法检查与优化

这个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
该 SQL 语句的结构比较复杂，可以考虑进行以下优化：
将子查询简化为 JOIN 操作: 可以使用 JOIN 连接 sysmn.mav_emr_core_dict 表，而不是在每个字段中使用子查询获取数据。
使用 WITH 语句: 可以使用 WITH 语句将子查询提取出来，使 SQL 代码更易于理解和维护。
优化 WHERE 条件: 可以对 WHERE 条件进行简化，避免冗余的逻辑判断，提高查询效率。
使用索引: 对经常使用的字段创建索引，可以有效提高查询速度。
需要注意的是，具体的优化方案需要根据数据库的具体情况进行调整。