sql之with as和case when用法

<sql id="searchTable">
  with t as(select
  project_id as projectId;
  case
  when project_status = '99' then '已结项'
  when project_status = 'D0' then '审批中'
  when project_status = 'D1' then '审批中'
  else '执行中' end as projectStatus,
  case
  when project_status in ('D2','D3','D4') then 'D阶段'
  when project_status like 'M%' then 'M阶段'
  when project_status like 'A%' then 'A阶段'
  when project_status like 'I%' then 'I阶段'
  when project_status like 'C%' then 'C阶段'
  when project_status ='99' then '已立项'
  else '立项' end as projectProgress,
  project_type as projectType,
  project_manager as projectManager,
  to_char(start_time,'yyyy-mm-dd') as startTime,
  to_char(end_time,'yyyy-mm-dd') as endTime,
  project_name as 'projectName',
  improve_method as 'improveMethod',
  level_two_department as 'I2Name',
  level_three_department as 'I3Name',
  level_one_department as 'I1Name',
  level_four_department as 'I4Name',
  creation_date as 'createionDate',
  project_status as 'projectStatus',
  label_year as 'labelYear',
  last_updated_by as 'lastUpdatedBy',
  last_updated_by_name as 'lastUpdatedByName',
  last_updated_date as 'lastUpdateDate',
  site_id as 'siteId',
  project_codenumber as 'projectCodeNumber',
  case
  when archiving = 'approving' then '审批中'
  when archiving = 'execution' then '执行中'
  when archiving = 'closed' then '已结项'
  when archiving = 'archived' then '已归档'
  else '已终止' end as archiving
  from t_qm_work_project_info
  <trim prefix="where"  prefixOverrides="and|or">
    <if test='_parameter.get("0").I1Name != null and _parameter.get("0").I1Name!=""'>
      and level_one_department = #{0.I1Name,jdbcType=varchar}
    </if>
    <if test='_parameter.get("0").I2Name != null and _parameter.get("0").I2Name!=""'>
      and level_two_department = #{0.I2Name,jdbcType=varchar}
    </if>
  <if test='_parameter.get("0").I3Name != null and _parameter.get("0").I3Name!=""'>
      and level_three_department = #{0.I3Name,jdbcType=varchar}
    </if>
  <if test='_parameter.get("0").startTime != null and _parameter.get("0").startTime!=""'>
      and to_char(start_time,'yyyy') = #{0.startTime,jdbcType=varchar}
    </if>
  <if test='_parameter.get("0").endTime != null and _parameter.get("0").endTime!=""'>
      and to_char(end_time,'yyyy') = #{0.endTime,jdbcType=varchar}
    </if>
  <if test='_parameter.get("0").siteId != null and _parameter.get("0").siteId!=""'>
      and site_id = #{0.siteId,jdbcType=Integer}
    </if>
  and use_flag = 'Y')
  </trim>
</sql>
  <select id = "findDeptDetailsByPage" resultType= "com.huawei.it.mes.mesplus.inproment.vo.SearchVo">
   <include refid = "searchTable"/>
   select 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
          limit #{1.pageSize} offset #{1.mysqlStartIndex}
   </select>
<select id= "findDeptDetailsByPage" resultType="int">
 <include refid="searchTable"/>
 select count(1) from t
<include refid = "searchDetailsFields"/>
</select>
<sql id="searchDeatilsFields">
  <trim prefix="where" prefixOverrides="and|or">
    <if test = '_parameter.get("0").keyWord!=null and _parameter.get("0").keyWord !=""'>
      and lower(concat(projectId,projectType,projectName,improveMethod,I1Name,I2Name,
    projectManager,I3Name,I4Name,endTime,startTime,projectStatus,archiving,projectProgress,
    projectCodenumber)) like '%' || lower(#{0.keyWord,jdbcType=varchar}) || '%'
    </if>
  </trim>
</sql>