MySQL:根据两列之间的条件合并表中行的数据

Question

我在MySql中有一张像这样的医院床头柜

patient_id      date_started     date_stopped

1               2018-01-10       2018-01-15
1               2018-01-15       2018-01-28
1               2018-01-28       2018-02-05
2               2018-01-15       2018-02-10
2               2018-03-05       2018-04-12
3               2018-01-10       2018-01-20
3               2018-01-20       null
4               2018-02-10       2018-02-10

现在我不想要患者的 date_stopped 与不同行的 date_started 相同的那些行。我希望输出像

patient_id     date_started     date_stopped

1              2018-01-10       2018-02-05
2              2018-01-15       2018-02-10
2              2018-03-05       2018-04-12
4              2018-02-10       2018-02-10

如果我进行 INNER JOIN，我会得到多行

SELECT
  *
FROM bed AS bed_1
INNER JOIN bed_2
  ON bed_2.patient_id = bed_1.patient_id
  AND bed_2.date_started != bed_2.date_stopped

解决这个问题的最佳方法是什么？

Answer 1

这可以通过编写接受程序来完成patient_id 和 date_stopped 并递归直到 date_stopped 不等于同一患者的下一个 date_started

CREATE PROCEDURE FINAL_STOP_DATE(IN patientId INT,IN dateStopped DATETIME, OUT finalStopDate DATETIME)
 +  BEGIN
 +    DECLARE current_patient INT;
 +    DECLARE curr_start_date DATETIME;
 +    DECLARE curr_stop_date DATETIME;
 +    SET max_sp_recursion_depth = 250;
 +    SELECT patient_id,
 +      date_started,date_stopped
 +    INTO current_patient,curr_start_date,curr_stop_date
 +    FROM bed
 +          WHERE patient_id = patientId AND date_started = dateStopped
 +    LIMIT 1;
 +    if current_patient = patientId  AND curr_start_date = dateStopped THEN
 +      CALL FINAL_STOP_DATE(current_patient, curr_stop_date, finalStopDate);
 +    ELSE
 +      SET finalStopDate = dateStopped;
 +    END IF ;
 +  END; 

因此此过程的输出是给定患者的最终停止日期，不包括中途的所有连续开始和停止日期

我们可以为 bed 表中的每条记录调用此过程，并将此过程的输出选择为 date_ended 列。为了实现这一点，我们需要编写一个中介函数，因为我们不能从 SQL 中的 select 子句调用过程

CREATE FUNCTION GET_FINAL_STOP_DATE(patientId INT, date_stopped DATETIME)
 +  RETURNS DATETIME
 +  BEGIN
 +    DECLARE final_stop_date DATETIME;
 +    CALL FINAL_STOP_DATE(patientId, date_stopped, final_stop_date);
 +    RETURN final_stop_date;
 +  END; 

现在从 select 子句调用此函数，它将选择返回值作为 date_ended 列现在对结果集执行 GROUP BY patient_id, date_ended 并选择 MIN date_started

SELECT patient_id, 
       MIN(date_started),
       GET_FINAL_STOP_DATE(patient_id,date_stopped) AS date_ended
FROM bed
GROUP BY patient_id, date_ended

最后的结果会是这样

patient_id     date_started     date_ended

1              2018-01-10       2018-02-05
2              2018-01-15       2018-02-10
2              2018-03-05       2018-04-12
3              2018-01-10       null
4              2018-02-10       2018-02-10

最后，添加一个 where 条件说 date_ended 不应该为 null 以获得预期的结果