MySQL数据联查

select  t1.id ,
        t1.number ,
        t1.siteId ,
        t1.creator AS 上报人 ,
        t1.create_time AS 上报时间 ,
        CASE WHEN t1.status = 'Waiting' THEN N'待处理'
             WHEN t1.status = 'Handling' THEN N'处理中'
             WHEN t1.status = 'visitting' THEN N'已回访'
             WHEN t1.status = 'Finished' THEN N'已关闭'
             WHEN t1.status = 'Retention' THEN N'滞留'
             ELSE ''
        END AS 工单状态 ,telNum1.TEXT_ as 来电号码,telQueue1.TEXT_ as 来电队列
 from ACT_C_TASKS as t1,
(
      (select telNum.PROC_INST_ID_,MAX(telNum.TEXT_) AS TEXT_ FROM(((SELECT  PROC_INST_ID_,CAST(TEXT_ AS CHAR(50)) as TEXT_ FROM  ACT_HI_VARINST  WHERE  NAME_ = 'telNum') union ALL (SELECT  PROC_INST_ID_,  CAST(TEXT_ AS CHAR(50)) as TEXT_ FROM  ACT_RU_VARIABLE  WHERE  NAME_ = 'telNum')) ) as telNum group by telNum.PROC_INST_ID_)as telNum1,
      (select telQueue.PROC_INST_ID_,MAX(telQueue.TEXT_) AS TEXT_ FROM(((SELECT  PROC_INST_ID_,CAST(TEXT_ AS CHAR(50)) as TEXT_ FROM  ACT_HI_VARINST  WHERE  NAME_ = 'telQueue') union ALL (SELECT  PROC_INST_ID_,  CAST(TEXT_ AS CHAR(50)) as TEXT_ FROM  ACT_RU_VARIABLE  WHERE  NAME_ = 'telQueue'))) as telQueue group by telQueue.PROC_INST_ID_)as telQueue1


) where
  t1.c_process_id =telNum1.PROC_INST_ID_ 
and
  t1.c_process_id=telQueue1.PROC_INST_ID_ 
and  t1.dispatched = 1
        AND t1.isDelete = 0