select
--具体要查询的字段不展示，用*代表需要取出的字段 * from (select * from ts_drc_responsibility where CREATE_TIME > ‘2021-01-01 00:00:00‘) a left join catalog_res_associated b on a.res_id = b.resource_id left join ts_drc_catalog c on b.catalog_id = c.cata_id left join ts_drc_requirements d on a.req_id = d.req_id left join ts_drc_entity_processinst f on a.resp_id = f.ENTITY_ID left join sip_data_base.wfworkitem g on f.PROCESSINST_ID = g.PROCESSINSTIDwhere f.ENTITY_NAME=‘资源申请‘ and a.sub_req_code = ‘XQ0024781710560001‘
结果如下：
现在要将这三条数据组合成一条数据，状态为‘备案’，‘创建资源’，‘中心预审’
组合sql：
select * from (select * from ts_drc_responsibility WHERE create_time > ‘2021-01-01 00:00:00‘ ) a left join catalog_res_associated b on a.res_id = b.resource_id left join ts_drc_catalog c on b.catalog_id = c.cata_id left join ts_drc_requirements d on a.req_id = d.req_id left join ts_drc_entity_processinst f on a.resp_id = f.ENTITY_ID left join (SELECT PROCESSINSTID,PARTINAME,CREATETIME,ENDTIME FROM sip_data_base.wfworkitem WHERE WORKITEMNAME = ‘创建资源申请‘) g on f.PROCESSINST_ID = g.PROCESSINSTID left join (SELECT PROCESSINSTID,PARTINAME,CREATETIME,ENDTIME FROM sip_data_base.wfworkitem WHERE WORKITEMNAME = ‘中心预审‘) g1 on f.PROCESSINST_ID = g1.PROCESSINSTID
left join (SELECT PROCESSINSTID,PARTINAME,CREATETIME,ENDTIME FROM sip_data_base.wfworkitem WHERE WORKITEMNAME = ‘备案‘) g5 on f.PROCESSINST_ID = g5.PROCESSINSTID where f.ENTITY_NAME=‘资源申请‘ and a.sub_req_code = ‘XQ0024781710560001‘
结果如下：
使用left join 进行关联查询，然后把三种状态分别left join进行拼接
原文：https://www.cnblogs.com/zeevy/p/15219699.html