主页 / coding / 问题 / 78479846
Accepted
Ziad Adnan
Asked: 2024-05-15 01:45:55 +0800 CST

如果报告表中存在报告，如何选择批准日期？

  • 772

我需要从 3 个表中选择订单日期、扫描日期和报告日期，这是查询：

SELECT   H.PATIENT_NO,
         TO_CHAR(TO_DATE(H.SERV_REQUEST_DATE,'yyyymmdd'),'YYYY-MM-DD') AS "Order Date",
         (TO_CHAR(TO_DATE(LPAD(TO_CHAR(H.SERV_REQUEST_TIME),4,'0'),'HH24MI'),'HH24:MI')) AS "Order Time" , 
         TO_CHAR(TO_DATE(G.DATE_IMAGE_TAKEN,'yyyymmdd'),'YYYY-MM-DD') AS "Scan Date",
         (TO_CHAR(TO_DATE(LPAD(TO_CHAR(G.TIME_IMAGE_TAKEN),4,'0'),'HH24MI'),'HH24:MI')) AS "Scan Time" , 
         TO_CHAR(TO_DATE(R.APPROVING_DATE,'yyyymmdd'),'YYYY-MM-DD') AS "Release Date",
         (TO_CHAR(TO_DATE(LPAD(TO_CHAR(r.approving_time),4,'0'),'HH24MI'),'HH24:MI')) AS "Release Time" , 
          H.PATIENT_SOURCE_IND,
          H.PROVIDING_RESOURCE                 
     FROM   RAD_RESULT_VIEW H,RAD_IMAGE_HEADER G, RAD_REPORT_RESULT R , MR_FILE_DETAILS A
     WHERE  H.HOSPITAL_NO = G.HOSPITAL_NO
            AND H.HOSPITAL_NO = M.HOSPITAL_NO
            AND H.PATIENT_NO = A.PATIENT_NO
            AND H.RAD_ORDER_NO = G.RAD_ORDER_NO
            AND (G.IMAGE_NO = R.IMAGE_NO)

并非所有订单都在 RAD_REPORT_RESULT 表中提供报告

那么我需要如何选择 R.APPROVING_DATE 和 R.approving_time 仅当存在带有此选项的报告时

 AND (G.IMAGE_NO = R.IMAGE_NO)

仅当表 RAD_REPORT_RESULT 中存在时，它才会选择行
如果有报告或没有报告，我需要选择所有行 仅从其他表中选择数据

例子 ：

PATIENT_NO      Order Date          Scan Date        Release Date 
  1               15-05-2024        15-05-2024
  2               15-05-2024        15-05-2024       15-05-2024
  3               15-05-2024        15-05-2024       
  4               14-05-2024        15-05-2024
  5               14-05-2024        15-05-2024       15-05-2024
oracle

1 个回答

  1. Best Answer

    这不就是左连接吗？我错过了什么吗？

     WITH t AS (
        SELECT   
            H.PATIENT_NO,
            TO_CHAR(TO_DATE(H.SERV_REQUEST_DATE, 'yyyymmdd'), 'YYYY-MM-DD') AS "Order Date",
            TO_CHAR(TO_DATE(LPAD(H.SERV_REQUEST_TIME, 4, '0'), 'HH24MI'), 'HH24:MI') AS "Order Time", 
            TO_CHAR(TO_DATE(G.DATE_IMAGE_TAKEN, 'yyyymmdd'), 'YYYY-MM-DD') AS "Scan Date",
            TO_CHAR(TO_DATE(LPAD(G.TIME_IMAGE_TAKEN, 4, '0'), 'HH24MI'), 'HH24:MI') AS "Scan Time", 
            H.PATIENT_SOURCE_IND,
            H.PROVIDING_RESOURCE,
            G.IMAGE_NO 
        FROM   
            RAD_RESULT_VIEW H
            JOIN RAD_IMAGE_HEADER G ON H.HOSPITAL_NO = G.HOSPITAL_NO AND H.RAD_ORDER_NO = G.RAD_ORDER_NO
            JOIN MR_FILE_DETAILS A ON H.PATIENT_NO = A.PATIENT_NO
    )
    SELECT 
        t.*,  
        TO_CHAR(TO_DATE(R.APPROVING_DATE, 'yyyymmdd'), 'YYYY-MM-DD') AS "Release Date"
    FROM 
        t
        LEFT JOIN RAD_REPORT_RESULT R ON t.IMAGE_NO = R.IMAGE_NO;
    • 1

相关问题