SQL

SELECT v.bed_name            AS "BedName",
       p.id                  AS "PatientCaseId",
       v.inpat_status        AS "InpatStatus",
       v.source_ward_id      AS "SourceWardId",
       v.patient_name        AS "PatientName",
       t.code                AS "DiseaseCode",
       t.title               AS "DiseaseName",
       v.gender              AS "Gender",
       v.source_visit_id     AS "SourceVisitId",
       v.source_patient_id   AS "SourcePatientId",
       p.report_status       AS "ReportStatus",
       v.case_id             AS "CaseID",
       v.total_cost          AS "TotalCost",
       p.creation_by         AS "CreationBy",
       v.inpat_time          AS "InpatTime",
       v.out_time            AS "OutTime",
       v.source_doctor_id    AS "BedDoctorWorkNo",
       v.bed_doc_name        AS "BedDoctorName",
       v.dept_chief_doc_name AS "DepartmentChiefName",
       v.source_inpat_no     AS "RegistrationNo",
       d0.source_dept_id     AS "SourceDepartmentId",
       d0.name               AS "DepartmentName",
       w.name                AS "WardName",
       t0.name               AS "JoinGroupDoctor",
       v.type                AS "Type",
       v.reg_time            AS "RegTime"
FROM mqm.patient_case AS p
         LEFT JOIN mqm.visit AS v ON p.visit_id = v.id
         
LEFT JOIN (
          SELECT d.id,
           d.code,
           d.is_valid,
           d.name,
           d.order_no,
           d.disease_organ_id,
           d.disease_pic,
           d.disease_system_id,
           d.title
    FROM mqm.disease_form AS d
    WHERE d.is_valid = TRUE  ) AS t ON p.disease_code = t.code
         LEFT JOIN mqm.department AS d0 ON v.source_dept_id = d0.source_dept_id
         LEFT JOIN mqm.ward AS w ON v.source_ward_id = w.source_ward_id
         
LEFT JOIN (
           SELECT d1.id,
           d1.accessible_ward_ids,
           d1.dept_id,
           d1.in_use,
           d1.is_valid,
           d1.name,
           d1.role_id,
           d1.role_ids,
           d1.sso_id,
           d1.source_doctor_id,
           d1.ward_id,
           d1.work_no
    FROM mqm.doctor AS d1
    WHERE d1.is_valid ) AS t0 ON p.creation_by = t0.work_no
WHERE (v.type = 0)
  AND p.report_status IN (0, 5, 10)
ORDER BY (((p.report_status = 90) AND (p.report_status IS NOT NULL)) OR
          ((p.report_status = 80) AND (p.report_status IS NOT NULL))) OR
         ((p.report_status = 12) AND (p.report_status IS NOT NULL)) DESC, (p.last_update_time IS NULL),
         p.last_update_time DESC