Mybatis 查询中的动态SQL以及复杂SQL映射

1. 映射器mapper

Mybatis的配置文件可以看看：
https://mybatis.org/mybatis-3/zh/configuration.html。
在平常的项目中对于复杂的查询，我们现在就要使用到动态SQL，来定义 SQL 映射语句了。 首先，我们需要告诉 MyBatis 到哪里去找到这些语句。 在自动查找资源方面，Java 并没有提供一个很好的解决方案，所以最好的办法是直接告诉 MyBatis 到哪里去找映射文件。 可以使用相对于类路径的资源引用，或完全限定资源定位符（包括 file:/// 形式的 URL），或类名和包名等。下面的配置会告诉 MyBatis 去哪里找映射文件

<mapper namespace="com.pms.dao.mybatis.StoreOutMapper">

2. SQL映射文件

cache – 该命名空间的缓存配置。
cache-ref – 引用其它命名空间的缓存配置。
resultMap – 描述如何从数据库结果集中加载对象，是最复杂也是最强大的元素。
sql – 可被其它语句引用的可重用语句块。
insert – 映射插入语句。
update – 映射更新语句。
delete – 映射删除语句。
select – 映射查询语句。

2.1. select-映射查询语句

简单的select

<select id="selectPersonById" parameterType="int" resultType="Person">
  SELECT * FROM PERSON WHERE ID = #{id}
</select>

在Peison表中根据id查询一条Person数据，使用Person对象进行封装。

2.2. select常见的元素

(1) id：在命名空间中唯一的标识符，可以被用来引用这条语句。
(2) parameterType：将会传入这条语句的参数的类全限定名或别名。这个属性是可选的，因为 MyBatis 可以通过类型处理器（TypeHandler）推断出具体传入语句的参数，默认值为未设置（unset）。
(3) resultType：期望从这条语句中返回结果的类全限定名或别名。 注意，如果返回的是集合，那应该设置为集合包含的类型，而不是集合本身的类型。 resultType 和 resultMap 之间只能同时使用一个。
(4) resultMap：结果映射是 MyBatis 最强大的特性，可以将查询到的复杂数据或者多表数据映射到一个结果集当中。

<resultMap id="baseResult" type="com.project.dto.ProductionDto">
        <--column属性：用于指定数据库列名。property属性：用于指定实体类属性-->
        <result column="id" property="id"/>
        <result column="serial_number" property="serialNumber"/>
        <result column="type" property="type"/>
        <result column="status" property="status"/>
        <result column="creator" property="creator"/>
        <result column="creator_id" property="creatorId"/>
        <result column="create_time" property="createTime" jdbcType="TIMESTAMP"/>
        <result column="update_user_id" property="updateUserId"/>
        <result column="update_time" property="updateTime" jdbcType="TIMESTAMP"/>
        <result column="update_user" property="updateUser"/>
</resultMap>

2.3. select映射拼接sql

select映射查询语句

<select id="pageProduction" resultMap="baseResult">
        SELECT
        psn.id,
        psn.create_time,
        psn.creator_id,
        psn.creator,
        psn.serial_number,
        psn.specification_model,
        psn.status,
        psn.type,
        psn.update_time,
        psn.update_user_id,
        psn.update_user,
        FROM production AS psn
        LEFT JOIN info AS hi ON psn.info_id=hi.id
        // 一般不建议这样使用，会产生索引问题，可以使用<where>标签代替where 1=1
        WHERE
        1 = 1
        --include中引用了id为pageProductionCondition的sql。
        <include refid="pageProductionCondition"></include>
        <if test="enquiryDto.sortColName != null and enquiryDto.sortColName != ''">
            ORDER BY ${enquiryDto.sortColName}
            <if test="enquiryDto.useAsc == true">
                ASC
            </if>
            <if test="enquiryDto.useAsc == false">
                DESC
            </if>
        </if>
</select>

2.4. sql标签

这个元素可以用来定义可重用的 SQL 代码片段，以便在其它语句中使用。 参数可以静态地（在加载的时候）确定下来，并且可以在不同的 include 元素中定义不同的参数值。此

<sql id="pageProductionCondition">
        <if test="enquiryDto.serialNumber != null and enquiryDto.serialNumber != ''">
            <bind name="pattern1" value="'%' + enquiryDto.serialNumber + '%'"/>
            AND psn.serial_number LIKE #{pattern1}
        </if>
        <if test="enquiryDto.imei != null and enquiryDto.imei != ''">
            <bind name="pattern2" value="'%' + enquiryDto.imei + '%'"/>
            AND hi.imei LIKE #{pattern2}
        </if>
        <if test="enquiryDto.productionBatch != null and enquiryDto.productionBatch != ''">
            AND psn.production_batch = #{enquiryDto.productionBatch}
        </if>
        <if test="enquiryDto.specificationModel != null and enquiryDto.specificationModel != ''">
            AND psn.specification_model = #{enquiryDto.specificationModel}
        </if>
        <if test="enquiryDto.status != null and enquiryDto.status != ''">
            AND psn.status = #{enquiryDto.status}
        </if>
        <if test="enquiryDto.type != null and enquiryDto.type != ''">
            AND psn.type = #{enquiryDto.type}
        </if>
</sql>

2.5. 理解复杂select与sql

DISTINCT, isnull()与CAST()函数

 <select id="getPurchaseApplyPage" resultType="com.project.dto.PurchaseApplyDto">
        SELECT
        	pur_a.apply_id,
        	pur_a.apply_code,
        (
         -- DISTINCT取消重复行
         -- isnull('value',''),表示当查询为空时则为''
         -- CAST函数用于将某种数据类型的表达式显式转换为另一种数据类型。
       （ SELECT DISTINCT 
			equ_d.apply_code +','+
			isnull(CAST(equ_a.use_type2 AS VARCHAR(1)),'') as type+','+
			isnull(CAST(equ_a.model_organ_id AS VARCHAR(6)), '') +','+
			isnull(CAST(equ_d.project_id AS VARCHAR(6)), '') +';'
		FROM 
			pur_equipment_detail equ_d
			--左连接
		LEFT JOIN 
			pur_equipment_apply equ_a ON equ_a.apply_id = equ_d.apply_id
		LEFT JOIN 
			pur_purchase_detail pur_d ON pur_d.apply_code = equ_d.apply_code 
			AND pur_d.model_id = equ_d.model_id
        WHERE 
        	pur_d.apply_id = pur_a.apply_id
        	--FOR XML PATH,将数据转为xml形式，类似于html的table中的数据排版，FOR XML PATH('')将标签省略只显示数据
        FOR XML PATH ('')
        ) 
        --将上面查询都的数据用equipmentApplysInfo来表示
        AS equipmentApplysInfo
        --top（1）显示首条数据
        SELECT TOP(1) supplier.name
        FROM pur_purchase_detail pur_d
        LEFT JOIN crm_customer supplier ON supplier.customer_id = pur_d.customer_id
        WHERE pur_d.apply_id = pur_a.apply_id
        --sum，计算总记录数
        (SELECT ISNULL(SUM(total_price), 0) AS pay_amt
        FROM pur_purchase_detail pur_d
        WHERE pur_d.apply_id = pur_a.apply_id
        ) AS pay_amt,
        --使用 CONVERT() 函数来显示不同的格式。了解具体的格式和styleID可以看一下：https://www.w3school.com.cn/sql/func_convert.asp
        (SELECT CAST(che.check_sate AS VARCHAR(1)) + ',' + CONVERT(VARCHAR(10), che_d.arrival_time, 120) + ';'
        FROM pur_check_detail che_d

        --内连接，INNER JOIN 与 JOIN 是相同的，显示相等的数据；
        --左连接显示左表的所有数据，右连接显示右表的所有数据，全连接显示两个表的所有数据
        INNER JOIN pur_purchase_detail pur_d ON pur_d.detail_id = che_d.purchase_detail_id AND pur_d.apply_id =
        pur_a.apply_id
        LEFT JOIN pur_check che ON che.check_id = che_d.check_id
        FOR XML PATH ('')
        --IN 操作符允许我们在 WHERE 子句中规定多个值，即查询的数据应满足IN中的内容
        ) AS checkInfo IN
        <foreach collection="applyIds" item="applyId" open="(" close=")" index="index" separator=",">#{applyId}
        </foreach>
--动态SQL if标签我们的项目中模糊查询很多都用到了if，如果模糊条件不为空，则执行模糊查询，如果为空就到此为止
--动态SQL bind标签
--在进行模糊查询时，如果使用“${}”拼接字符串，会出现SQL注入问题。如果使用字符串拼接函数或连接符号，不同数据库的拼接函数或连接符号不同。例如 MySQL 的 concat 函数、Oracle 的连接符号“||”，不利于代码的移植。MyBatis 提供了 <bind> 元素来解决这一问题。

--AND 和 OR 可在 WHERE 子语句中把两个或多个条件结合起来。
--如果第一个条件和第二个条件都成立，则 AND 运算符显示一条记录。
--如果第一个条件和第二个条件中只要有一个成立，则 OR 运算符显示一条记录
<if test="req.authOrgans != null">
            OR equ_a.model_organ_id IN
            <foreach collection="req.authOrgans" open="(" close=")" item="organId" separator=",">#{organId}</foreach>
</if>

<if test="req.contractTitle != null">
            INNER JOIN crm_contract_formal contract ON contract.contract_formal_id = pur_a.formal_contract_id
            <bind name="contractTitle" value="'%' + req.contractTitle + '%'"/>
            AND contract.title LIKE #{contractTitle}
        </if>
        
--foreach的属性
 foreach的属性：
item：表示集合中每个元素进行迭代时的别名。
open：表示该语句以什么开始（既然是in条件语句，所以必然以“（”开始）。
separator：表示在每次进行迭代之间以什么符号作为分隔符（既然是in条件语句，所以必须以“.”，作为分隔符）。
close：表示该语句以什么语句结束（既然是in条件语句，所以必然是以“）”结束）。
collection：最关键并最容易出错的属性，需格外注意，该属性必须指定，不同情况下，该属性的值是不一样的。主要有三种情况：
1. 若入参为单参数且参数类型是一个List的时候，collection属性值为list。
2. 若入参为单参数且参数类型是一个数组的时候，collection属性值为array（此处传入参数Integer[] rolelds为数组类型，故此处3. 3. collection属性值设为“array”）。
3. 若传入参数为多参数，就需要把他们封装为一个Map进行处理。select中返回的是一个resultMap（id=“userMapByRole”），该resultMap也进行相应的字段映射。

SQL Server中 join 等效于inner join

--利用Case When Then Else End 多条件判断，例如我们在数据库中查到的是类型代码1，2，3...通过类型代码转为相应表示的文本值；则case后面则是在数据库中查询到的类型代码，满足when条件则为then后面的结果值，如果为else后面的空值或异常值则结束比较。
SELECT DISTINCT
        equ_d.apply_code + ',' + 
        case isnull(CAST (equ_a.use_type2 AS VARCHAR (1)),'')
        when '1' then '生产'
        when '2' then '试验'
        when '3' then '销售'
        when '4' then '公司自用'
        else '' end
        + ';'
        FROM
        pur_equipment_detail equ_d
        LEFT JOIN pur_equipment_apply equ_a ON equ_a.apply_id = equ_d.apply_id
        LEFT JOIN pur_purchase_detail pur_d ON pur_d.apply_code = equ_d.apply_code
        AND pur_d.model_id = equ_d.model_id


 --Row_number()为pay表的每一行添加一个行号，给行号这一列取名’PAGE_ROW_NUMBER’ 在over()方法中将’PAGE_ROW_NUMBER’随机排序，order by rand()--随机抽取数据
–然后将’PAGE_ROW_NUMBER’列 与pay表的所有列 形成一个表PAGE_TABLE_ALIAS  
–where条件。假如当前页(currentPage)是第2页，每页显示10个数据(pageSzie)。那么第一页的数据就是第11-20条  
–所以为了显示第二页的数据，即显示第11-20条数据，那么就让PAGE_ROW_NUMBER大于 10*(2-1) 即：页大小*(当前页-1) 
${iDisplayStart}：页大小*(当前页-1)
        FROM
        (
        SELECT
        ROW_NUMBER ( ) OVER ( ORDER BY RAND( ) ) PAGE_ROW_NUMBER,
        amount,
        totalPrice,
        <foreach collection="lstSubtotalKey" item="key" index="index">
            <if test="index == 0">
                ${key}
            </if>
        </foreach>
        FROM
        pay AS PAGE_TABLE_ALIAS ) AS PAGE_TABLE_ALIAS
        WHERE
        PAGE_ROW_NUMBER > ${iDisplayStart}
        ORDER BY
        PAGE_ROW_NUMBER
        )
        ORDER BY
        <foreach collection="lstSubtotalKey" item="key" separator=",">
            ${key}
        </foreach>
    </select>


<choose><when></when><otherwise></otherwise></choose> 标签组：也是一个用于条件判断的标签组，和<if>的不同之处在于条件从<choose>进入，去匹配<when>中的添加，一旦匹配马上结束；若到找不到匹配项，将执行<otherwise>中的语句；可以理解为<if>是 && 关系 <choose>是 || 关系 

   
   <choose>
            <when test="req.payState != null
					or req.payApplyBeginTime != null or req.payApplyEndTime != null 
					or req.payRealBeginTime != null or req.payRealEndTime != null
                    or req.companyId != null or req.payId != null">
                INNER JOIN hwoa.dbo.cost_pay_record pay ON pay.module_id = 2 AND pay.module_data_id = pur_a.apply_id
                <if test="req.payState != null">
                    AND pay.approval_state = #{req.payState}
                </if>
                <if test="req.payApplyBeginTime != null">
                    AND pay.expected_pay_date >= #{req.payApplyBeginTime}
                </if>
                <if test="req.payApplyEndTime != null">
                    AND pay.expected_pay_date <![CDATA[<]]> dateadd(DAY, 1, #{req.payApplyEndTime})
                </if>
                <if test="req.payRealBeginTime != null">
                    AND pay.real_pay_date >= #{req.payRealBeginTime}
                </if>
                <if test="req.payRealEndTime != null">
                    AND pay.real_pay_date <![CDATA[<]]> dateadd(DAY, 1, #{req.payRealEndTime})
                </if>
                <if test="req.companyId != null">
                    AND pay.company_id = #{req.companyId}
                </if>
                <if test="req.payId != null">
                    AND pay.id = #{req.payId}
                </if>
            </when>
            <otherwise>
                LEFT JOIN hwoa.dbo.cost_pay_record pay ON pay.module_id = 2 AND pay.module_data_id = pur_a.apply_id
            </otherwise>
        </choose>     
ORDER BY

--CONVERT() 函数可以用不同的格式显示日期/时间数据。
--EXISTS 运算符用于判断查询子句是否有记录，如果有一条或多条记录存在返回 True，否则返回 False。
convert(varchar(10),a.review_time,120) pucharseReviewTime,
		(
				SELECT
						cast(cd.check_id as varchar) + ';'			
						FROM
							pur_check_detail cd
						WHERE
						cd.purchase_detail_id = d.detail_id
						and exists(select 1 from pur_check c where c.check_id = cd.check_id and c.check_sate <![CDATA[<]]> 8)
						for xml path('')
			) checkId,

--include 包含了id为purchaseGroupBefore的sql标签
<select id="getPurchaseGroupSize" parameterType="java.util.Map" resultType="Long">
        <include refid="purchaseGroupBefore"></include>
        SELECT COUNT (*)
        FROM
        ( SELECT
        <foreach collection="lstSubtotalKey" item="key" index="index">
            <if test="index == 0">
                ${key}
            </if>
        </foreach>
        FROM pay GROUP BY
        <foreach collection="lstSubtotalKey" item="key" index="index">
            <if test="index == 0">
                ${key}
            </if>
        </foreach>
        ) temp
    </select>

--WITH AS短语，也叫做子查询部分（subquery factoring），可以定义一个SQL片断，该SQL片断会被整个SQL语句用到。可以使SQL语句的可读性更高，也可以在UNION ALL的不同部分，作为提供数据的部分。
--例如：with A as (select * from class)
--这个语句的意思就是，先执行select * from class 得到一个结果，将这个结果记录为A ，在执行select *from A 语句。A 表只是一个别名。

<sql id="purchaseGroupBefore">
        <!-- 明细查询 -->
        WITH detail AS (SELECT
        ISNULL(rs.amount, 0) as amount, ISNULL(rs.avgPrice, 0) as avgPrice, ISNULL(rs.totalPrice, 0) as
        totalPrice,
        m.model_id as modelId, m.model_code as modelCode, m.model as modelName, m.model,
        pt.type_id as parentTypeId, pt.type_name as parentTypeName, t.type_id as typeId, t.type_name as typeName,
        pt.type_name + '-' + t.type_name + '-' + m.model AS material,
        isnull( rs.customerName, '无供应商' ) AS customerName,
        isnull( rs.organName, '无部门' ) AS organName,
        isnull( rs.projectGroupName, '无项目群' ) AS projectGroupName,
        isnull( rs.batchName, '无批次' ) AS batchName,
        isnull( rs.projectName, '非项目' ) AS projectName
        <if test="useProject != null">
            , rs.project_id as projectId, rs.project_name as projectName
        </if>
        <if test="useContract != null">
            , rs.contract_id as contractId, rs.contract_title as contractTitle
        </if>
        <if test="useCustomer != null">
            , rs.customer_id as customerId, rs.customer_name as customerName
        </if>
        FROM (
        SELECT SUM(ptb.true_amount) as amount, avg(ptb.true_price) as avgPrice, SUM(ptb.true_total_price) as totalPrice,
        <if test="useProject != null">
            etb.project_id, etb.project_name,
        </if>
        <if test="useContract != null">
            etb.contract_id, etb.contract_title,
        </if>
        <if test="useCustomer != null">
            etb.customer_id, etb.customer_name,
        </if>
        ptb.model_id,
        ptb.customerName,
        ptb.organName,
        ptb.projectGroupName,
        ptb.batchName,
        ptb.projectName
        from (
        (
        select a.apply_id, a.apply_code, d.model_id, d.project_id, d.project_name, d.contract_id, d.contract_title,
        d.customer_id, d.customer_name from pur_equipment_detail d
        left join pur_equipment_apply a on d.apply_id=a.apply_id where 1=1
        <if test="projectName != null">
            <bind name="pattern" value="'%' + projectName + '%'"/>
            and d.project_name like #{pattern}
        </if>
        <if test="contractTitle != null">
            <bind name="pattern" value="'%' + contractTitle + '%'"/>
            and d.contract_title like #{pattern}
        </if>
        <if test="customerName != null">
            <bind name="pattern" value="'%' + customerName + '%'"/>
            and d.customer_name like #{pattern}
        </if>
        ) etb
        inner join
        (
        select a.apply_id, a.process_time, d.detail_id, d.apply_code, d.model_id, d.true_amount, d.true_price,
        d.true_total_price,
        d.customer_name AS customerName,
        org.name AS organName,
        pg.name AS projectGroupName,
        ba.name AS batchName,
        pro.name AS projectName
        from pur_purchase_detail d
        LEFT JOIN pur_purchase_apply a ON d.apply_id=a.apply_id
        LEFT JOIN hwoa.dbo.config_organ_v org ON org.id = d.organ_id
        LEFT JOIN crm_project_group_v pg ON pg.group_id = a.project_group_id
        LEFT JOIN crm_production_batch_v ba ON ba.batch_id = d.batch_id
        LEFT JOIN crm_project_v pro ON pro.project_id = d.project_id
       <where>
        and a.is_delete=0
        and a.apply_state in (2,7,8)
        <if test="beginTime != null">
            and a.process_time <![CDATA[>=]]> #{beginTime}
        </if>
        <if test="endTime != null">
            and a.process_time <![CDATA[<=]]> #{endTime}
        </if>
        ) ptb on etb.apply_code=ptb.apply_code and etb.model_id=ptb.model_id
        ) group by ptb.model_id,
        ptb.organName,
        ptb.projectGroupName,
        ptb.batchName,
        ptb.projectName,
        ptb.customerName
        <if test="useProject != null">
            , etb.project_id, etb.project_name
        </if>
        <if test="useContract != null">
            , etb.contract_id, etb.contract_title
        </if>
        <if test="useCustomer != null">
            , etb.customer_id, etb.customer_name
        </if>
        ) rs
        inner join pur_goods_model m on m.model_id=rs.model_id
        <if test="modelId != null">
            and m.model_id=#{modelId}
        </if>
        <if test="model != null">
            <bind name="pattern" value="'%' + model + '%'"/>
            and m.model like #{pattern}
        </if>
        join pur_goods_type t on t.type_id=m.type_id
        <if test="typeId != null">
            and t.type_id=#{typeId}
        </if>
        join pur_goods_type pt on pt.type_id=t.parent_id
        <if test="parentId != null">
            and pt.type_id=#{parentId}
        </if>
        </where>
        ),
        <!-- 分组查询 -->
        pay AS (
        SELECT
        <foreach collection="lstSubtotalKey" item="key" separator="," close=",">
            ${key}
        </foreach>
        SUM ( amount ) AS amount,
        SUM ( totalPrice ) AS totalPrice
        FROM
        detail
        GROUP BY
        <foreach collection="lstSubtotalKey" item="key" separator=",">
            ${key}
        </foreach>
        )
    </sql>

3. 查询结果一对多映射

可以使用此resultMap形式接收查询的数据一对多的情况（得到的只有一条数据，映射的多条数据用list存储）
<resultMap id="TaskExecutionResultMap" type="com.huaweisoft.oa.model.worktask.dto.TaskRedundancyDto" >
        <result column="productId" property="productId" jdbcType="INTEGER" />
        <result column="productName" property="productName" jdbcType="VARCHAR" />
        <result column="projectName" property="projectName" jdbcType="VARCHAR" />
        <result column="webSite" property="webSite" jdbcType="VARCHAR"/>
        <result column="taskId" property="taskId" jdbcType="INTEGER" />
        <result column="taskName" property="taskName" jdbcType="VARCHAR" />
        <result column="status" property="status" jdbcType="VARCHAR" />
        <result column="estStarted" property="estStarted" jdbcType="DATE" />
        <result column="deadline" property="deadline" jdbcType="DATE" />
        <result column="finishedDate" property="finishedDate" jdbcType="DATE" />
        <result column="estimate" property="estimate" jdbcType="BIGINT" />
        <result column="workload" property="workload" jdbcType="VARCHAR" />
        <result column="efficiencyScore" property="efficiencyScore" jdbcType="DOUBLE" />
        <result column="qualityScore" property="qualityScore" jdbcType="DOUBLE" />
        <result column="attitudeScore" property="attitudeScore" jdbcType="DOUBLE" />
        <result column="approveId" property="approveId" jdbcType="INTEGER" />
        <result column="approveName" property="approveName" jdbcType="VARCHAR" />
        <result column="approveState" property="approveState" jdbcType="INTEGER" />
        -- 多条数据
        <collection property="workApplyDtoList" ofType="com.huaweisoft.oa.model.workapply.dto.WorkApplyDto">
            <result column="id" property="id" jdbcType="INTEGER"/>
            <result column="title" property="title" jdbcType="VARCHAR" />
            <result column="applyUserId" property="applyUserId" jdbcType="INTEGER" />
            <result column="applyUserName" property="applyUserName" jdbcType="VARCHAR" />
            <result column="applyTime" property="applyTime" jdbcType="DATE"/>
            <result column="timeSpan" property="timeSpan" jdbcType="DOUBLE" />
            <result column="workType" property="workType" jdbcType="INTEGER"/>
        </collection>
    </resultMap>
<select id="findTaskExecutionByCondition" resultMap="TaskExecutionResultMap">
        SELECT
            tr.task_id AS taskId,
            war.project_id AS productId,
            p.name AS productName,
            p.web_site AS webSite,
            war.project_version AS projectName,
            tr.task_name AS taskName,
            tr.status,
            tr.est_started AS estStarted,
            tr.deadline,
            tr.finished_date AS finishedDate,
            tr.estimate,
            tr.workload,
            tr.efficiency_score AS efficiencyScore,
            tr.quality_score AS qualityScore,
            tr.attitude_score AS attitudeScore,
            war.approve_user_id AS approveId,
            approve.name AS approveName,
            tr.approve_state AS approveState,

			-- 以下数据有可能出现不一致导出产生一对多的情况
            war.title,
            war.id,
            war.apply_user_id AS applyUserId,
            apply.name AS applyUserName,
            war.apply_time AS applyTime,
            war.time_span AS timeSpan,
            war.work_type AS workType
        FROM
            task_redundancy tr
        LEFT JOIN work_apply_record war ON war.task_id = tr.task_id
        LEFT JOIN hr_employee apply ON war.apply_user_id = apply.id
        LEFT JOIN hr_employee approve ON war.approve_user_id = approve.id
        LEFT JOIN hr_employee e ON war.create_user_id = e.id
        LEFT JOIN hwoa_purchasing.dbo.crm_project_v p ON war.project_id = p.project_id
        <where>
            <include refid="conditionScreen"></include>
        </where>
        ORDER BY p.name DESC,
                 war.project_version DESC,
        <choose >
            <when test="workApplyPageReq.sortColName == null or workApplyPageReq.sortColName == ''">
                tr.est_started DESC,
                tr.deadline DESC,
                tr.task_name DESC,
            </when>
            <otherwise>
                ${workApplyPageReq.sortColName}
                <choose>
                    <when test="workApplyPageReq.useAsc" >
                        ASC,
                    </when>
                    <otherwise>
                        DESC,
                    </otherwise>
                </choose>
            </otherwise>
        </choose>
            apply.name DESC,
            war.apply_time DESC,
            war.title DESC
    </select>