최근 날짜로 그룹화 하여 검색

    SELECT AA.* 
      FROM
      (SELECT 
         B.REGISTCOM AS MEMCONAME
         , C.RESUMENAM
         , A.CONTRPATH
         , A.CONTFILNM
         , A.COMSIGNDT
         , DATE_FORMAT(FRESIGNDT, '%Y.%m.%d') AS FRESIGNDT
         , A.MEMBERNUM
         , A.PRJTMGTSN
         , A.FREELNCNM
         <if test='userType.equals("F")'> 
         , CASE WHEN (DATEDIFF(DATE_FORMAT(A.FRESIGNDT, '%Y.%m.%d'), NOW()) <![CDATA[ < ]]>-7) THEN 1
            ELSE 0 END AS DWNLIMIT
         ,  CONTRPATH AS FILENAME
         </if>
      FROM
         JOBCNTRCT A
         LEFT JOIN JOBPRJLST B ON A.MEMBERNUM = B.MEMBERNUM AND A.PRJTMGTSN = B.PRJTMGTSN
         LEFT JOIN JOBMEMBER C ON A.FREELNCNM = C.MEMBERNUM 
      WHERE 
         <if test='userType.equals("C")'>
            A.MEMBERNUM = #{MEMBERNUM} 
         </if>
         <if test='userType.equals("F")'>
            A.FREELNCNM = #{MEMBERNUM}
         </if>
         <if test='PRJTMGTSN != null and !PRJTMGTSN.equals("")'>
             AND A.PRJTMGTSN = #{PRJTMGTSN}
         </if>
	         AND A.REGISTRDT = (
		         SELECT
		            MAX(REGISTRDT)
		         FROM
		            JOBCNTRCT
		         WHERE
		            PRJTMGTSN = A.PRJTMGTSN
	         )
      GROUP BY
           A.PRJTMGTSN
     ORDER BY
      <choose>
          <when test='userType.equals("F") and sort != null and  sort.equals("korean")'> 
               B.PROJECTNM ASC
          </when>
          <when test='userType.equals("C") and sort != null and sort.equals("korean")'> 
               C.RESUMENAM ASC
          </when>
          <otherwise>   <!-- 최신순 default -->
            A.REGISTRDT DESC
          </otherwise>
      </choose>
      )AA
      LIMIT #{PAGECNT}, #{AMOUNT}
   </select>
   
   <select id="getContractCount" parameterType="Map" resultType="int">
      SELECT 
         COUNT(AA.CNTRCTSNM)
      FROM
      (SELECT 
         A.CNTRCTSNM
      FROM
         JOBCNTRCT A
         LEFT JOIN JOBPRJLST B ON A.MEMBERNUM = B.MEMBERNUM AND A.PRJTMGTSN = B.PRJTMGTSN
         LEFT JOIN JOBMEMBER C ON A.FREELNCNM = C.MEMBERNUM 
      WHERE 
         <if test='userType.equals("C")'>
            A.MEMBERNUM = #{MEMBERNUM} 
         </if>
         <if test='userType.equals("F")'>
            A.FREELNCNM = #{MEMBERNUM} 
         </if>
         <if test='PRJTMGTSN != null and !PRJTMGTSN.equals("")'>
             AND A.PRJTMGTSN = #{PRJTMGTSN}
         </if>
            AND A.REGISTRDT = (
               SELECT
                  MAX(REGISTRDT)
               FROM
                  JOBCNTRCT
               WHERE
                  PRJTMGTSN = A.PRJTMGTSN
            )
      GROUP BY
           A.PRJTMGTSN
      )AA