一段包含很多基础的整合的SQL语句

对于业务的生疏和自身SQL基础的薄弱，导致我在工作中出现很多难题。后来同事写的SQL语句，让我明白SQL也是语言，不需要畏惧。

下面的SQL语句记下来，仅供自己学习和参考。

d主表，l子表。按照查询条件来查找的话，正常情况下，子表内重复记录，那么子表和主表关联查询也会产生重复记录。而我必须只要得到最新修改的那条，且查询条件不变的话。先根据查询条件查到主键最大，即最新的主键值，主键是id，然后把id作为查询条件即可。

SELECT  d.DISPATCH_ID dispatchId,
		         d.DISPATCH_TYPE dispatchType,
		         d.DISPATCH_CREATOR dispatchCreator,
		         d.SINGER singer,
		         d.RELEASE_CENTER releaseCenter,
		         d.RELEASE_CENTER_NAME releaseCenterName,
		         d.CONTACT_PERSON contactPerson,
		         d.COPY_CENTER copyCenter,
		         d.COPY_CENTER_NAME copyCenterName,
		         d.RECEIVING_CENTER receivingCenter,
		         d.RECEIVING_CENTER_NAME receivingCenterName,
		         d.RELEASE_DATE releaseDate,
		         to_char(d.RELEASE_DATE,'yyyy-mm-dd hh:mi:ss') releaseDateStr,
		         d.RELEASE_PHONE releasePhone,
		         d.RELEASE_FAX releaseFax,
		         d.RELEASE_CONTACT releaseContact,
		         d.RECEVING_PERSON_CITY recevingPersonCity,
		         d.RECEVING_DATE_CITY recevingDateCity,
		         d.IMPORT_LEVEL importLevel,
		         d.SECRET_LEVEL secretLevel,
		         d.NEXT_TYPE nextType,
		         d.FEED_BACK feedBack,
		         d.EXECUTE_BEGIN_DATE executeBeginDate,
		         to_char(d.EXECUTE_BEGIN_DATE,'yyyy-mm-dd hh:mi:ss') executeBeginDateStr,
		         d.EXECUTE_END_DATE executeEndDate,
		         to_char(d.EXECUTE_END_DATE,'yyyy-mm-dd hh:mi:ss') executeEndDateStr,
		         d.SUMMARY summary,
		         d.CONTENT content,
		         d.RECOMMEND recommend,
		         d.RECEVING_PERSON_PROVINCE recevingPersonProvince,
		         d.RECEVING_DATE_PROVINCE recevingDateProvince,
		         d.STATUS ,
			 decode(status,'0','无效','1','创建','2','待审核','3','审核不通过','4','审核成功待批复','5','批复不通过','6','批复成功待执行','7','执行完成') 
		         statusName,
		         d.ATTACHMENT attachment,
		         d.REVIEWER_NAME reviewerName,
		         d.REVIEWER_ID reviewerId,
		         d.REVIEWER_BRCH_NAME reviewerBrchName,
		         d.REPLYER_ID replyerId,
		         d.REPLYER_NAME replyerName,
		         d.REPLYER_BRCH_NAME replyerBrchName,
		         d.REPLYER_BRCH_NO replyerBrchNo,
		         d.REVIEWER_BRCH_NO reviewerBrchNo,
		         d.ATTACHMENT_NAME attachmentName,
		         d.DISPATCH_CREATOR_NAME dispatchCreatorName,
		         d.DISPATCH_CREATOR_BRCH dispatchCreatorBrch,
		         d.DISPATCH_CREATOR_BRCH_NAME dispatchCreatorBrchName,
		         l.locked_flag lockedFlag,
		         l.recommend recommendList,
		         l.type type,
		         l.receive_id receiveId,
		         l.receive_name receiveName,
		         l.receive_brch_id receiveBrchId,
		         l.receive_brch_name receiveBrchName,
		         l.receive_brch_level receiveBrchLevel,
		         to_char(l.oper_date,'yyyy-mm-dd hh:mi:ss') operDateStr,
		         l.CURRENT_STATUS currentStatus,
		         l.deal_flag dealFlag,
		         l.copy_flag copyFlag
		  FROM   t_wydd_dispatch d, t_wydd_dispatch_list l 
		  where  d.dispatch_id = l.dispatch_id 
			and d.DISPATCH_ID = #{dispatchList.dispatchId}  
<!-- 			and d.RECEVING_PERSON_CITYIDS like concat('%',concat(#{dispatchList.recevingPersonCityIDs,jdbcType= VARCHAR},'%'))  -->
			and l.receive_id = #{dispatchList.receiveId} 
			AND l.DISPATCH_LIST_PK =
                (SELECT MAX (DISPATCH_LIST_PK) DISPATCH_LIST_PK 
                   FROM t_wydd_dispatch_list 
                  WHERE DISPATCH_ID = #{dispatchList.dispatchId} and receive_id = #{dispatchList.receiveId} ) 
<!-- 			and l.current_status = #{dispatchList.currentStatus} -->