sql之with as和case when用法2

with t as(
  select t1.project_type as projectType,
         t1.improve_method as improveMehtod,
         t1.project_codenumber as projectCodenumber,
         t1.project_manager as projectManager,
         t2.task_names as taskNames,
         t2.end_time as endTime
         from t_qm_work_project_info t1,t_qm_work_task_assign_manage t2
         where t2.assigned_by = 'xxx'
         and t2.project_id in('xxx','xxx','xxx')
         and t1.project_id = t2.project_id
)
   select projectManager,projectType,improveMethod,projectCodenumber,taskNames from t
   where concat(projectType,projectCodenumber,taskNames,projectManager) like '%xxx%'



<sql id="searchTable">
    with t as(
  t1.task_names as taskNames,
  t1.end_time as scheduledEndTime,
  t1.assigned_by as assignedBy,
                      t.project_id as projectId;
  case
    when t.project_status = '99' then '已结项'
    when t.project_status = 'D0' then '审批中'
    when t.project_status = 'D1' then '审批中'
            else '执行中' end as projectStatus,
            case
    when t.project_status in ('D2','D3','D4') then 'D阶段'
    when t.project_status like 'M%' then 'M阶段'
    when t.project_status like 'A%' then 'A阶段'
    when t.project_status like 'I%' then 'I阶段'
    when t.project_status like 'C%' then 'C阶段'
    when t.project_status ='99' then '已立项'
            else '立项' end as projectProgress,
    t.project_type as projectType,
    t.project_manager as projectManager,
    to_char(t.start_time,'yyyy-mm-dd') as startTime,
    to_char(t.end_time,'yyyy-mm-dd') as endTime,
    t.project_name as 'projectName',
    t.improve_method as 'improveMethod',
    t.level_two_department as 'I2Name',
    t.level_three_department as 'I3Name',
    t.level_one_department as 'I1Name',
    t.level_four_department as 'I4Name',
    t.creation_date as 'createionDate',
    t.project_status as 'projectStatus',
    t.label_year as 'labelYear',
    t.last_updated_by as 'lastUpdatedBy',
    t.last_updated_by_name as 'lastUpdatedByName',
    t.last_updated_date as 'lastUpdateDate',
    t.site_id as 'siteId',
    t.project_codenumber as 'projectCodeNumber',
            case
    when t.archiving = 'approving' then '审批中'
    when t.archiving = 'execution' then '执行中'
    when t.archiving = 'closed' then '已结项'
    when t.archiving = 'archived' then '已归档'
            else '已终止' end as archiving
    from t_qm_work_project_info t,t_qm_work_task_assign_manage t1
  <trim prefix="where"  prefixOverrides="and|or">
    <if test='assignedBy != null and assignedBy != ""'>
      and t1.assigned_by = #{assignedBy,jdbc=varchar}
    </if>
      and t1.project_id in
      <foreach collection="list" item = "item" open="(" separator="," close=")">
        #{item.projectId,jdbc=varchar}
      </foreach>
      and t.project_id = t1.project_id
      and t.use_flag = 'Y'
  </trim>
    )
</sql>
  <select id = "findDeptDetailsByPage" resultType= "com.huawei.it.mes.mesplus.inproment.vo.SearchVo">
   <include refid = "searchTable"/>
select taskNames,
       scheduleEndTime,
  projectId,
       projectStatus,
       projectProgress,
       projectType,
       projectManager,
       startTime,
       endTime,
       projectName,
       improveMethod,
       I1Name,
       I2Name,
       I3Name,
       I4Name,
       creationDate,
       status,
       labelYear,
       lastUpdatedBy,
       lastUpdatedByName,
       lastUpdatedDate,
       siteId,
       projectCodeNumber,
       archiving
from t
  <include refid="searchDetailFields"/>
order by lastUpdatedDate desc
  </select>
  <sql id="searchDeatilsFields">
  <trim prefix="where" prefixOverrides="and|or">
  <if test = 'keyWord!=null and keyWord !=""'>
  and lower(concat(projectId,projectType,projectName,improveMethod,I1Name,I2Name,
  projectManager,I3Name,I4Name,endTime,startTime,projectStatus,archiving,projectProgress,
  projectCodenumber)) like '%' || lower(#{keyWord,jdbcType=varchar}) || '%'
  </if>
  </trim>
  </sql>

Mysql内外连接：