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

{ "title": "查询检验检查结果数量 - SQL语句优化", "description": "该SQL语句查询了患者检验和检查结果的数量，并根据医生、科室、时间等条件进行了筛选。", "keywords": "SQL, 查询, 检验, 检查, 结果, 数量, 医生, 科室, 时间, 筛选", "content": "select count(0) from (select CR.OUTPAT_NUM as "outpatNum",PIH.PAT_NAME as "patName",'检验' as "recordType",\n to_char(PLR.CRITICAL_DATE,'yyyy-mm-dd hh24:mi:ss') as "reportDate",\n 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",\n plr.report_result ||plr.unit as "result",\n (select esp.person_name from sysmn.ehr_service_personal esp where cr.DISPOSE_USER_ID= esp.person_id) as "disposeDoctor",\n to_char(CR.LAST_TIME,'yyyy-mm-dd hh24:mi:ss') as "disposeDate",\n cr.CRITICAL_RECORD_ID as "recordId",\n cr.FINISHED as "finished",\n cr.INPAT_NUM as "inpatNum",\n PIH.DEPT_ID as "deptId",\n nvl((select esp.person_code from sysmn.ehr_service_personal esp where PLR.CREATE_USER_ID= esp.person_id),\n (select esp.person_code from sysmn.ehr_service_personal esp where esp.person_name = PLR.CREATE_USER_ID and rownum=1)) as "reportDoctorNum",\n 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",\n (select esp.person_code from sysmn.ehr_service_personal esp where cr.DISPOSE_USER_ID= esp.person_id) as "disposeDoctorNum",\n (select esp.person_code from sysmn.ehr_service_personal esp where PL.REQ_DOCTOR_ID = esp.person_id) as "reqDoctorNum",\n (select esp.person_name from sysmn.ehr_service_personal esp where PL.REQ_DOCTOR_ID = esp.person_id) as "reqDoctorName",\n (select sdd.dept_name from sysmn.sys_dept_dict sdd where PIH.DEPT_ID = sdd.DEPT_ID) as "deptName",\n -- (select esp.PERSON_NAME from sysmn.EHR_SERVICE_PERSONAL esp where PL2.REQ_DOCTOR_ID = esp.PERSON_ID)as "reqDoctorIdName",\n PIH.RESPONSIBLE_DOCTOR_PERSON_ID as "responsibleDoctorPersonId",\n (select esp.PERSON_NAME from sysmn.EHR_SERVICE_PERSONAL esp where PIH.RESPONSIBLE_DOCTOR_PERSON_ID = esp.PERSON_ID)as "responsibleDoctorPersonName",\n -- PL2.REQ_DOCTOR_ID as "reqDoctorId",\n to_char(pa.CREATE_DATETIME,'yyyy-mm-dd hh24:mi:ss') as "reqDate",\n to_char(nvl(cr.NURSE_NOTICE_TIME,CR.NURSE_LAST_TIME),'yyyy-mm-dd hh24:mi:ss') as "nurseLastTime",\n CR.NURSE_FINISHED AS "nurseFinished",\n nvl((select user_name from sysmn.SYS_USER su where su.USER_ID = cr.NURSE_NOTICE_USERID),SU4.USER_NAME)AS "disposeNurse",\n ward.DEPT_ALIAS as "wardDeptName",\n pih.WARD_DEPT_ID as "wardDeptId",\n PIH.BED_NO AS "bedNo",\n bd.BED_LABEL as "bedLabel",\n (case when ward.HOSPITAL_AREA_NO = '01' then '城中' when ward.HOSPITAL_AREA_NO = '02' then '阳湖'else '' end) as "hospitalAreaNo"\n from emr.CRITICAL_RECORD CR\n left join emr.LAB_CRITICAL_RECORD PLR on PLR.LAB_CRITICAL_ID = cr.ITEM_ID\n left join emr.PATIENT_LAB PL on PL.REQ_NUM = plr.REQ_NUM\n left join emr.PATIENT_IN_HOSPITAL PIH on cr.INPAT_NUM = PIH.INPAT_NUM\n left join emr.patient_orders pa on plr.REQ_NUM = pa.REQ_NUM and plr.serial_num = pa.serial_number\n left join SYSMN.SYS_USER SU4 on SU4.USER_ID = CR.NURSE_USER_ID\n left join sysmn.sys_dept_dict ward on ward.dept_id = pih.WARD_DEPT_ID\n left join sysmn.SYS_BED_DICT bd on bd.WARD_DEPT_ID = pih.WARD_DEPT_ID and bd.BED_NO = pih.BED_NO\n\n WHERE (pih.RESPONSIBLE_DOCTOR_PERSON_ID = ?\n or (PL.REQ_DOCTOR_ID = ? or cr.DISPOSE_USER_ID = ?)\n \n or pih.dept_id in\n \n )\n \n \n \n \n \n \n and PLR.CRITICAL_DATE >= to_date(?,'yyyy-mm-dd hh24:mi:ss')\n \n \n and to_date(?,'yyyy-mm-dd hh24:mi:ss') >= PLR.CRITICAL_DATE\n \n \n \n and PIH.DEPT_ID not in\n (\n ?\n )\n \n\n and cr.INPAT_NUM is not null\n and cr.OUTPAT_NUM is null\n and CR.RECOED_TYPE = '1'\n and (PLR.notice_status = '0' or PLR.notice_status is null) \n\n union\n\n select cr2.OUTPAT_NUM as "outpatNum" , PIH.PAT_NAME as "patName", '检查' as "recordType",\n to_char(per2.DT_ALERT,'yyyy-mm-dd hh24:mi:ss') as "reportDate",\n 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",\n PER2.VAL_RSTRPTLAB as "result",\n (select esp.person_name from sysmn.ehr_service_personal esp where cr2.DISPOSE_USER_ID= esp.person_id) as "disposeDoctor",\n to_char(CR2.LAST_TIME,'yyyy-mm-dd hh24:mi:ss') as "disposeDate",\n cr2.CRITICAL_RECORD_ID as "recordId",\n cr2.FINISHED as "finished",\n cr2.INPAT_NUM as "inpatNum",\n PER2.CODE_EMP_ALERT as "reportDoctorNum",\n PER2.NAME_EMP_ALERT as "reportDoctoerName",\n PIH.DEPT_ID as "deptId",\n (select esp.person_code from sysmn.ehr_service_personal esp where cr2.DISPOSE_USER_ID= esp.person_id) as "disposeDoctorNum",\n 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",\n 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",\n (select sdd.dept_name from sysmn.sys_dept_dict sdd where PIH.DEPT_ID = sdd.DEPT_ID) as "deptName",\n --(select esp.PERSON_NAME from sysmn.EHR_SERVICE_PERSONAL esp where PE2.REQ_DOCTOR_ID = esp.PERSON_ID)as "reqDoctorIdName",\n PIH.RESPONSIBLE_DOCTOR_PERSON_ID as "responsibleDoctorPersonId",\n (select esp.PERSON_NAME from sysmn.EHR_SERVICE_PERSONAL esp where PIH.RESPONSIBLE_DOCTOR_PERSON_ID = esp.PERSON_ID)as "responsibleDoctorPersonName",\n -- PE2.REQ_DOCTOR_ID as "reqDoctorId",\n to_char(po.CREATE_DATETIME,'yyyy-mm-dd hh24:mi:ss') as "reqDate",\n to_char(CR2.NURSE_NOTICE_TIME,'yyyy-mm-dd hh24:mi:ss') as "nurseLastTime",\n CR2.NURSE_FINISHED AS "nurseFinished",\n nvl((select user_name from sysmn.SYS_USER su where su.USER_ID = CR2.NURSE_NOTICE_USERID),SU4.USER_NAME)AS "disposeNurse",\n ward.DEPT_ALIAS as "wardDeptName",\n pih.WARD_DEPT_ID as "wardDeptId",\n PIH.BED_NO AS "bedNo",\n bd.BED_LABEL as "bedLabel",\n (case when ward.HOSPITAL_AREA_NO = '01' then '城中' when ward.HOSPITAL_AREA_NO = '02' then '阳湖' else '' end) as "hospitalAreaNo"\n from emr.CRITICAL_RECORD CR2\n left join EMR.WJZ_MAG PER2 on PER2.ID_ALERT = cr2.ITEM_ID\n left join emr.PATIENT_EXAM_ITEM PLI on PLI.REQ_NUM = per2.NO_APPLYFORM and PLI.SERIAL_NUMBER = PER2.SUBNO_APPLYFORM\n left join emr.PATIENT_EXAM pe on pe.REQ_NUM = per2.NO_APPLYFORM\n left join emr.PATIENT_IN_HOSPITAL PIH on cr2.INPAT_NUM = PIH.INPAT_NUM\n left join emr.patient_orders po on per2.NO_APPLYFORM = po.REQ_NUM and po.serial_number = per2.subno_applyform\n left join SYSMN.SYS_USER SU4 on SU4.USER_ID = CR2.NURSE_USER_ID\n left join sysmn.sys_dept_dict ward on ward.dept_id = pih.WARD_DEPT_ID\n left join sysmn.SYS_BED_DICT bd on bd.WARD_DEPT_ID = pih.WARD_DEPT_ID and bd.BED_NO = pih.BED_NO\n WHERE (pih.RESPONSIBLE_DOCTOR_PERSON_ID = ?\n \n or pih.dept_id in\n \n )\n \n \n \n \n \n \n and per2.DT_ALERT >= to_date(?,'yyyy-mm-dd hh24:mi:ss')\n \n \n and to_date(?,'yyyy-mm-dd hh24:mi:ss') >= per2.DT_ALERT\n \n \n \n and PIH.DEPT_ID not in\n (\n ?\n )\n \n and CR2.OUTPAT_NUM is null\n and CR2.RECOED_TYPE = '4') tmp_count 缺失表达式中文回答内容：这个SQL查询语句包含了两个子查询，并且在最外层使用了UNION运算符将两个子查询的结果合并在一起。最终查询的结果是两个子查询结果行数的总和。

在子查询中，使用了多个表的连接和条件筛选，以及一些计算和转换函数来生成查询结果。具体的查询逻辑和条件如下：

第一个子查询：

选择了一系列列，并为每一列定义了别名。
从多个表中进行了左连接操作，并根据条件进行了筛选。
筛选条件包括：RESPONSIBLE_DOCTOR_PERSON_ID等于给定的参数值，或者REQ_DOCTOR_ID等于给定的参数值，或者DISPOSE_USER_ID等于给定的参数值。另外，还对DEPT_ID进行了条件筛选。
对CRITICAL_DATE进行了时间范围的筛选。
排除了DEPT_ID等于给定的参数值的行。
对INPAT_NUM进行了非空判断。
对RECOED_TYPE进行了值等于'1'的筛选。
最后，对notice_status进行了值等于'0'或者为空的筛选。

第二个子查询：

选择了一系列列，并为每一列定义了别名。
从多个表中进行了左连接操作，并根据条件进行了筛选。
筛选条件包括：RESPONSIBLE_DOCTOR_PERSON_ID等于给定的参数值。另外，还对DEPT_ID进行了条件筛选。
对DT_ALERT进行了时间范围的筛选。
排除了DEPT_ID等于给定的参数值的行。
对OUTPAT_NUM进行了非空判断。
对RECOED_TYPE进行了值等于'4'的筛选。

最后，将两个子查询的结果合并在一起，并使用COUNT函数计算结果行数。