sql记录3

<select id="selectByWhere" parameterType="com.walmart.aloha.productmanage.entity.param.ParamWorkHours"
        resultType="com.walmart.aloha.productmanage.entity.result.ResultWorkHours">
    SELECT
    result.project_id,
    result.user_id,
    result.hourStatus as status,
    result.countHours,
    result.pm_user_id,
    result.pmName,
    result.sap_id,
    result.project_name,
    result.user_type,
    result.english_name,
    result.chinese_name,
    result.domain,
    result.role,
    result.minBeginDate ,
    result.maxEndDate
    FROM (
    SELECT
    work_hours.project_id,
    work_hours.user_id,
    work_hours.hourStatus,
    work_hours.countHours,
    work_hours.pm_user_id,
    work_hours.pmName,
    work_hours.sap_id,
    work_hours.project_name,
    user_info.user_type,
    user_info.english_name,
    user_info.chinese_name,
    user_info.domain,
    user_info.role,
    resour_allo.minBeginDate ,
    resour_allo.maxEndDate,
    ROW_NUMBER () OVER ( PARTITION BY work_hours.project_id, work_hours.user_id ORDER BY work_hours.project_id DESC
    ) theRow
    FROM
    (
    SELECT twh.project_id,
    twh.user_id,
    twh.hour_status AS hourStatus,tu.english_name as pmName ,tp.project_name ,tp.pm_user_id,tp.sap_id,
    sum(twh.workhours) over(PARTITION BY twh.project_id,twh.user_id ORDER BY
    twh.project_id DESC) as countHours ,ROW_NUMBER() OVER( PARTITION BY twh.project_id,twh.user_id ORDER BY
    twh.project_id DESC)RN FROM tbl_workhours as twh inner join tbl_project as tp On twh.project_id = tp.project_id
    left JOIN tbl_user as tu ON tp.pm_user_id = tu.user_id WHERE twh.valid = 0
    AND tp.valid = 0
    AND tu.valid = 0
    <if test="hourStatus != null and  hourStatus != '' ">
        AND twh.hour_status = #{hourStatus,jdbcType=VARCHAR}
    </if>
    <if test="pmUserId != null and  pmUserId != '' ">
        AND tp.pm_user_id = #{pmUserId,jdbcType=VARCHAR}
    </if>
    <if test="userId != null and  userId != '' ">
        AND twh.user_id = #{userId,jdbcType=VARCHAR}
    </if>
    ) as work_hours,
    (SELECT tra.project_id,tra.user_id,
    min(tra.min_ra_date) AS minBeginDate,
    max(tra.max_ra_date) AS maxEndDate
    ,ROW_NUMBER() OVER( PARTITION BY tra.project_id,tra.user_id ORDER BY tra.project_id DESC)RN
    FROM tbl_resource_allocation as tra,tbl_workhours as twh WHERE tra.valid = 0 AND twh.valid=0 AND twh.project_id
    = tra.project_id AND tra.user_id = twh.user_id
    <if test="hourStatus != null and  hourStatus != '' ">
        AND twh.hour_status = #{hourStatus,jdbcType=VARCHAR}
    </if>
    <if test="userId != null and  userId != '' ">
        AND twh.user_id = #{userId,jdbcType=VARCHAR}
    </if>
    <if test="beginDate != null and endDate != null">
        AND ( tra.min_ra_date
        BETWEEN #{beginDate,jdbcType=DATE} AND #{endDate,jdbcType=DATE}
        OR tra.max_ra_date BETWEEN #{beginDate,jdbcType=DATE} AND #{endDate,jdbcType=DATE})
    </if>
    GROUP BY tra.project_id,tra.user_id
    ) as resour_allo,
    (SELECT
    tu.user_id,
    tu.user_type,
    tu.english_name,
    tu.chinese_name,
    tu.domain,
    tu.STATUS,
    twh.project_id,
    tu.role,ROW_NUMBER() OVER( PARTITION BY twh.project_id,twh.user_id ORDER BY twh.project_id DESC)RN FROM
    tbl_workhours as twh INNER JOIN tbl_user as tu on twh.user_id = tu.user_id WHERE tu.valid = 0 AND twh.valid = 0
    <if test="hourStatus != null and  hourStatus != '' ">
        AND twh.hour_status = #{hourStatus,jdbcType=VARCHAR}
    </if>
    <if test="userId != null and  userId != '' ">
        AND twh.user_id = #{userId,jdbcType=VARCHAR}
    </if>
    ) as user_info
    WHERE work_hours.RN = 1
    AND resour_allo.RN = 1
    AND user_info.RN = 1
    AND work_hours.project_id = resour_allo.project_id
    AND work_hours.user_id = user_info.user_id
    AND work_hours.user_id = resour_allo.user_id
    AND work_hours.project_id = user_info.project_id
    )as result
    <if test="startRow != null and  endRow != null ">
        WHERE result.theRow between #{startRow} and #{endRow}
    </if>
</select>