MySQL创建给出语句

Question

现有一组流程跟踪数据，请用SQL取出每一个流程中的最新创建的记录。数据结构如下，假设只有四个字段，依次为ID(主键)，PROCESS_INS_ID(流程实例ID)，CREATE_TIME（创建时间）,DEAL_UID（处理人）

[{'1','P_1','2019-07-03 18:00:00','ZHANG'},{'2','P_1','2019-07-03 18:01:00','ZHANG'},

{'3','P_1','2019-07-03 18:02:00','ZHANG'},{'4','P_2','2019-07-03 18:00:00','ZHANG'},

{'5','P_2','2019-07-03 18:01:00','ZHANG'},{'6','P_3','2019-07-03 18:00:00','ZHANG'},

{'7','P_3','2019-07-03 18:01:00','ZHANG'},{'8','P_3','2019-07-03 18:02:00','ZHANG'}]

要求查询出的结果为

{'3','P_1','2019-07-03 18:02:00','ZHANG'} p_1中 创建时间最新的

{'5','P_2','2019-07-03 18:01:00','ZHANG'} p_2中 创建时间最新的

{'8','P_3','2019-07-03 18:02:00','ZHANG'} p_3中 创建时间最新的

请选择基于Oracle或者mysql数据库给出sql语句。

Answer 1

MYSQL 实现方式

SELECT t.*
FROM (
      SELECT PROCESS_INS_ID, MAX(CREATE_TIME) as CREATE_TIME
      FROM test
      GROUP BY PROCESS_INS_ID
) r
INNER JOIN test t
ON t.PROCESS_INS_ID = r.PROCESS_INS_ID AND t.CREATE_TIME = r.CREATE_TIME
GROUP BY t.PROCESS_INS_ID;