使用3个表的SQL MAX查询

Question

我在查询SQL数据库时遇到困难。

| patient_id | episode_number | attend_practitioner | pract_assignment_date |
| ---------- | -------------- | ------------------- | --------------------- |
| 111        | 4              | 4444                | 01/05/2017            |
| 222        | 8              | 5555                | 03/17/2017            |
| 222        | 8              | 6666                | 03/20/2017            |
| 222        | 9              | 7777                | 04/10/2017            |
| 333        | 2              | 5555                | 10/08/2017            |
| 444        | 7              | 7777                | 08/09/2017            |

| patient_id | episode_number | backup_practitioner | date_of_assignment |
| ---------- | -------------- | ------------------- | ------------------ |
| 111        | 4              |                     |                    |
| 222        | 8              | 7777                | 03/17/2017         |
| 222        | 8              | 4444                | 05/18/2017         |
| 222        | 9              |                     |                    |
| 333        | 2              | 4444                | 10/08/2017         |
| 333        | 2              | 5555                | 10/19/2017         |

| patient_id | episode_number | admit_date | 
| ---------- | -------------- | ---------- | 
| 111        | 4              | 01/05/2017 |  
| 222        | 8              | 03/17/2017 |  
| 222        | 9              | 03/20/2017 |  
| 333        | 2              | 10/08/2017 | 

我正在寻找一个SQL查询，在这里我可以输入一个staff_id，然后让它返回当前分配给它的所有打开片段。结果：

| staff_id | patient_id | episode_number | admit_date | date_of_assignment | 
| -------- | ---------- | -------------- | ---------- | ------------------ | 
| 4444     | 111        | 4              | 01/05/2017 | 01/05/2017         | 
| 4444     | 222        | 8              | 03/17/2017 | 05/18/2017         | 

我不明白SQL如何处理查询中的别名。

SQL不知道如何处理OVER、LAG()、LEAD()等SQL窗口函数。因此，我在使用MAX()函数的同时使用self联接。也许这是一个较旧的SAP服务器。

我不知道SQL查询中的大写化是否不相关。

Answer 1

在调整查询项之后，这是返回正确结果的SQL查询版本：

SELECT
    t1.staff_id
  , t1.patient_id
  , t3.admit_date
  , t1.episode_number
  , t1.date_of_assignment

FROM
  /* select the most recent attending practitioner entry from table1 for the patient and episode */
  (SELECT attending_practitioner AS staff_id, patient_id, episode_number, pract_assignment_date AS date_of_assignment 
  FROM table1 AS t1a
  WHERE t1a.pract_assignment_date = 
    (SELECT MAX(pract_assignment_date) 
    FROM table1 AS t1b 
    WHERE t1b.patient_id = t1a.patient_id 
    AND t1b.episode_number = t1a.episode_number)
  UNION
  /* select the most recent practitioner entry from table2 for the patient and episode */
  SELECT backup_practitioner AS staff_id, patient_id, episode_number, date_of_assignment 
  FROM table2 AS t2a
  WHERE t2a.date_of_assignment = 
    (SELECT MAX(date_of_assignment) 
    FROM table2 AS t2b 
    WHERE t2b.patient_id = t2a.patient_id
    AND t2b.episode_number = t2a.episode_number)
  ) AS t1

INNER JOIN
  /* filter out closed episodes by using the table3 */
  (SELECT patient_id AS patient_id2, episode_number AS episode_number2, admit_date
  FROM table3) AS t3
  ON t3.patient_id = t1.patient_id
  AND t3.episode_number2 = t1.episode_number

WHERE t1.staff_id = '4444'

Answer 2

从你的问题中找出这一点有点困难。我想你需要为每一个病人/插曲做最近的作业。

您可以使用max进行第二个子查询，也可以使用下面所示的not。

正如您所提到的，使用row_number()和或CTE的版本要容易得多，但这是批量标准的sql版本。

select s.staff_id, e.patient_id, e.episode_number as episode_id, e.admit_date, s.date_of_assignment, c.last_date_of_service
from episode_history e
join (
    select patient_id, episode_number, attend_practitioner as staff_id, pract_assignment_date as date_of_assignment 
    from history_attending_practitioner
    union
    select patient_id, episode_number, backup_practitioner as staff_id, date_of_assignment as date_of_assignment 
    from user_practitioner_assignment
    where backup_practitioner is not null
    ) s on s.patient_id=e.patient_id and s.episode_number=e.episode_number
    and not exists(select * from history_attending_practitioner a2 where a2.patient_id=s.patient_id and a2.episode_number=s.episode_number and a2.pract_assignment_date>s.date_of_assignment)
    and not exists(select * from user_practitioner_assignment a3 where a3.patient_id=s.patient_id and a3.episode_number=s.episode_number and a3.date_of_assignment>s.date_of_assignment)
join view_episode_summary_current c on c.patient_id=e.patient_id and c.episode_number=e.episode_number
where e.discharge_date is null