我需要从到达的最长时间得到病人的id

Question

我需要从Time_arrived的最大日期中获取patient_id

Time_Arrived大于'01-01-2016'

示例

    Patien_ID  TIME_ARRIVED
    1234       01-01-2013
    1234       01-02-2014
    1235       01-02-2014
    1235       01-02-2015 
    1236       01-02-2015
    1236       01-03-2016
    1237       01-03-2015
    1237       01-04-2016

RESULT必须是

Patient_id
1236
1237

Answer 1

看起来time_arrived是一个日期列(而不是日期时间)，那么你可以使用这个查询:

select distinct patient_id
from patients
where time_arrived>='2016-01-01'

Answer 2

请以MySQl日期格式更新您的time_arrived。'yyyy-mm-dd'使用以下函数:

DELIMITER $$
USE `db_name`$$
DROP FUNCTION IF EXISTS `strtotime`$$
CREATE FUNCTION `strtotime`(input_string VARCHAR(50)) RETURNS VARCHAR(50)
BEGIN
DECLARE var_upd_str VARCHAR(50);

IF(input_string LIKE '%-%-%' AND input_string NOT LIKE '%-%-%-%') THEN
SET var_upd_str= CONCAT(SUBSTRING_INDEX(input_string,'-',-1),'-',SUBSTRING_INDEX(SUBSTRING_INDEX(input_string,'-',-2),'-',1),'-',SUBSTRING_INDEX(input_string,'-',1));
END IF;
RETURN var_upd_str;
END$$
DELIMITER ;

update your table:-

UPDATE patient SET time_arrived=strtotime(time_arrived);
ALTER TABLE patient CHANGE time_arrived time_arrived DATE NOT NULL;

如果您希望患者id从最大到达时间开始，并且到达时间应该大于或等于'2016-01-01':

SELECT patient_id FROM patient WHERE time_arrived=(SELECT MAX(time_arrived) AS date_arr  FROM patient) AND time_arrived>='2016-01-01';

如果您想要到达时间大于或等于'2016-01-01'的患者id:

SELECT patient_id FROM patient WHERE  time_arrived>='2016-01-01';

Answer 3

试试这个简单的

select patient_id from patients order by time_arrived desc limit 2

这里limit将显示2个结果，desc将按到达列数据的时间降序显示日期。