Activiti工作流流程步骤sql

-- Activiti工作流流程步骤sql
select 
	a.*,b.*
from 
(
	SELECT
		a.execution_id_,a.name_,a.assignee_,max(a.START_TIME_) START_TIME_,max(a.END_TIME_) END_TIME_,c.realname,CONCAT(c.realname,' ',DATE_FORMAT(max(a.END_TIME_),'%Y-%m-%d')) realName_endTime,a.DELETE_REASON_
	from act_hi_taskinst a 
	left join act_hi_procinst b on a.execution_id_ = b.id_  
	left join t_s_base_user c on a.assignee_=c.username 
	where b.BUSINESS_KEY_='${id}' and  a.DELETE_REASON_ not in('退回' )
	group by a.name_ 
	
) a
inner join 
(
	SELECT
		DISTINCT b.id_,d.*,concat(d.OP_NAME,': ',d.MEMO) op_name_memo,MAX(d.op_time)
	from 
			act_hi_procinst b,
			t_p_bpm_log d
	WHERE
			b.ID_ = d.BPM_ID and
			b.BUSINESS_KEY_='${id}'  
	group by d.task_node
	ORDER BY d.op_time asc
) b
on a.name_=b.task_node 
where a.realname=b.op_name and a.execution_id_=b.id_
ORDER BY a.START_TIME_ asc;