MySQL实现把两行两列数据合并为一行一列

时间:2021-09-19 22:32:22

最近在oa项目中使用acitiviti中,遇到一个排他网关有多个判断条件(),并且可以多次执行,在显示已办任务的时候要归属为一条数据,利用GROUP_CONCAT和CONCAT加上group by 解决。

详细sql如下:

SELECT
aht.ID_ AS id,
ard.NAME_ AS processName,
aht.NAME_ AS name ,
art.NAME_ AS currentName,
csp.post_name AS postName,
GROUP_CONCAT(CONCAT(ahd.NAME_,':',ahd.TEXT_)) AS message,
ahp.END_TIME_ AS endTime
FROM
act_hi_taskinst aht LEFT JOIN act_re_procdef arp ON aht.PROC_DEF_ID_ = arp.ID_
INNER JOIN act_re_procdef_ext arpe ON arp.ID_ = arpe.act_re_procdef_id
LEFT JOIN act_re_deployment ard ON arp.DEPLOYMENT_ID_ = ard.ID_
LEFT JOIN act_ru_task art ON aht.EXECUTION_ID_ = art.EXECUTION_ID_
LEFT JOIN cf_sys_post csp ON art.ASSIGNEE_ = csp.id
LEFT JOIN act_hi_actinst aha ON  aha.TASK_ID_ = aht.ID_
LEFT JOIN act_hi_detail ahd ON aha.ID_ =  ahd.ACT_INST_ID_
LEFT JOIN act_hi_procinst ahp ON aht.PROC_INST_ID_ = ahp.PROC_INST_ID_
WHERE
aht.ID_ IN (
SELECT
ahte.act_hi_taskinst_id
FROM
act_hi_taskinst_ext ahte
WHERE
ahte.complete_status = '1'
AND ahte.proxy_id = #{userId}
OR ahte.handle_id = #{userId}
)
GROUP BY aht.ID_
ORDER BY aht.START_TIME_ DESC