手术订单信息查询 SQL 语句

SELECT *
FROM (
    SELECT oo.visit_no,
        oo.order_id,
        oo.oper_req_type,
        oo.patient_id,
        oo.ENTERED_BY,
        oo.ANESTHESIA_STATUS,
        oo.ack_indicator,
        oo.dept_stayed,
        oo.SCHEDULED_DATE_TIME,
        pmi.name,
        pmi.sex,
        pmi.DATE_OF_BIRTH,
        pmi.inp_no,
        (
            SELECT bc.bed_label
            FROM bed_rec bc
            WHERE bc.bed_no = pih.bed_no
        ) AS BED_NO,
        (
            SELECT LISTAGG(ood1.operation, ', ') WITHIN GROUP(
                    ORDER BY OPERATION_NO
                )
            FROM operation_order_detail ood1
            WHERE ood1.order_id = oo.order_id
        ) AS operation,
        oo.operator_doctor,
        oo.ANESTHESIA_DOCTOR,
        oo.FIRST_ASSISTANT,
        oo.FIRST_OPERATION_NURSE,
        oo.FIRST_SUPPLY_NURSE,
        CASE 
            WHEN pih.WARD_CODE IS NULL 
            THEN api.ward_code
            ELSE pih.ward_code
        END AS ward_code,
        (
            CASE 
                WHEN pih.ward_code IS NULL 
                THEN (
                        SELECT dd.dept_name
                        FROM dept_dict dd
                        WHERE dd.DEPT_CODE = api.ward_code
                    )
                ELSE (
                        SELECT dd.dept_name
                        FROM dept_dict dd
                        WHERE dd.DEPT_CODE = pih.WARD_CODE
                    )
            END
        ) AS dept_name,
        oo.OPERATION_ROOM_NO,
        oo.OPERATION_SEQUENCE,
        oo.notes_on_operation,
        oo.DIAG_BEFORE_OPERATION,
        ood.OPERATION_SCALE AS OPERATING_SCALE,
        bcd.code_name,
        oo.doctor_pre_platform_num,
        CASE 
            WHEN oo.baby_no != '0' 
            THEN 'B'
            WHEN EXISTS (
                    SELECT 1
                    FROM inp_visit iv
                    WHERE iv.visit_no = oo.visit_no
                ) 
            THEN 'I'
            WHEN EXISTS (
                    SELECT 1
                    FROM erp_visit ev
                    WHERE ev.visit_no = oo.visit_no
                ) 
            THEN 'E'
            ELSE ''
        END patient_class
    FROM operation_orders oo,
        PATS_IN_HOSPITAL pih,
        PAT_MASTER_INDEX pmi,
        Apply_Pats_Info api,
        apply_for_admission aoa,
        base_code_dict bcd
    LEFT JOIN operation_order_detail ood ON ood.order_id = oo.ORDER_ID
    WHERE bcd.codetype_name = 'ANAESTHESIA_DICT'
        AND bcd.code_id = oo.ANAESTHESIA_METHOD
        AND oo.ack_indicator IN (0, 1, 2, 3, 6)
        AND oo.SCHEDULED_DATE_TIME >= '2023-06-01 00:00:00'
        AND oo.SCHEDULED_DATE_TIME < '2023-09-28 00:00:00'
        AND pih.dept_code = 'A0229'
        AND pih.visit_no = oo.visit_no
        AND pmi.patient_id = oo.patient_id
        AND aoa.apply_no = api.apply_no
        AND aoa.visit_no = pih.visit_no
    
    UNION
    
    SELECT oo.visit_no,
        oo.order_id,
        oo.oper_req_type,
        oo.patient_id,
        oo.ENTERED_BY,
        oo.ANESTHESIA_STATUS,
        oo.ack_indicator,
        oo.dept_stayed,
        oo.SCHEDULED_DATE_TIME,
        pmi.name,
        pmi.sex,
        pmi.DATE_OF_BIRTH,
        pmi.inp_no,
        (
            SELECT bc.bed_label
            FROM bed_rec bc
            WHERE bc.bed_no = pih.bed_no
        ) AS BED_NO,
        (
            SELECT LISTAGG(ood1.operation, ', ') WITHIN GROUP(
                    ORDER BY OPERATION_NO
                )
            FROM operation_order_detail ood1
            WHERE ood1.order_id = oo.order_id
        ) AS operation,
        oo.operator_doctor,
        oo.ANESTHESIA_DOCTOR,
        oo.FIRST_ASSISTANT,
        oo.FIRST_OPERAT
)  -- 此处补充缺失的关键字和表名

该 SQL 语句查询了手术订单信息，包括患者信息、手术信息、麻醉信息等。

查询条件：

• 手术时间在 2023 年 6 月 1 日至 2023 年 9 月 27 日之间
• 科室代码为 'A0229'
• 麻醉方法类型为 'ANAESTHESIA_DICT'
• 确认指示器为 0、1、2、3 或 6

查询结果：

• 手术订单信息
• 患者基本信息
• 手术信息
• 麻醉信息

注意：

• 该 SQL 语句可能不完整，需要根据实际情况进行补充。
• 需要确保所有表名和字段名正确，并具有访问权限。
• 建议在使用该 SQL 语句前进行测试，以确保结果正确。