首页 > 数据库技术 > 详细

sql查询 行转列

时间:2021-09-02 21:17:38      阅读:38      评论:0      收藏:0      [点我收藏+]
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进行拼接

sql查询 行转列

原文:https://www.cnblogs.com/zeevy/p/15219699.html

(0)
(0)
   
举报
评论 一句话评论(0
关于我们 - 联系我们 - 留言反馈 - 联系我们:wmxa8@hotmail.com
© 2014 bubuko.com 版权所有
打开技术之扣,分享程序人生!