oracle sql別名

標籤：

為sql的欄位起一個別名，常以為是可有可無的，但是有時候是必要的。

例如在ibatis中

 1 <!-- 擷取已發或待發送的多媒體訊息記錄列表 --> 2     <resultMap id="mmsListByRecordTypeMap" class="richinfo.mms.bean.req.MmsRecordReq" > 3         <result column="groupid" property="groupId" /> 4         <result column="subject" property="title" /> 5         <result column="sendnumber" property="userNumber" /> 6         <result column="destnumbers" property="destNumber" /> 7         <result column="istime" property="isTimming" typeHandler="richinfo.mms.util.MyBatisTypeHandlerCallback"/> 8         <result column="statuss" property="status" /> 9         <result column="startsendtime" property="sendTime" />10         <result column="totalsize" property="totalSize" />11     </resultMap>12     <select id="getMmsListByRecordType" resultMap="mmsListByRecordTypeMap"13         resultClass="map">14         select * from (select b.*,rownum r from ( select 15         uin,FN_GetDecryptUserNumber(sendnumber) sendnumber,subject,to_char(startsendtime, ‘yyyy-mm-dd hh24:mi:ss‘) 16         startsendtime,istime,showfileid,totalsize,showtype,groupid, 17         wmsys.wm_concat(FN_GetDecryptUserNumber(destnumber)) destnumbers,wmsys.wm_concat(nvl(status, 18         0)) statuss from mms_send_his_record where 1=1 19         <isEqual prepend="and" property="recordType" compareValue="0">20             issave=1 21         </isEqual>22         <isEqual prepend="and" property="recordType" compareValue="1">23         <![CDATA[24             (istime=0 or (istime=1 and startsendtime<=sysdate)) and issave=1  25         ]]>26         </isEqual>27         <isEqual prepend="and" property="recordType" compareValue="2">28         <![CDATA[29             istime=1 and startsendtime>sysdate 30             ]]>31         </isEqual>32         and uin = #uin# 33         <include refid="condition" />34         <![CDATA[35          group by groupid,uin,FN_GetDecryptUserNumber(sendnumber),subject,startsendtime,istime,showfileid,totalsize,showtype order by startsendtime desc) b 36           where rownum <= #endRecord# 37          ]]>38         <isNotEmpty property="destNumber">39         <![CDATA[40             and instr(destnumbers,#destNumber#)>0  or instr(destnumbers,fn_encrypt_function(#destNumber#))>041             ]]>42         </isNotEmpty>43         ) where r > #startRecord# 44     </select>

 1、必須為第15、17行起別名

　　如果不為第15、17行起別名的話，執行sql語句就會出問題。因為這個select的查詢結果集映射在了mmsListByRecordTypeMap上，這時候

 如果不起別名，FN_GetDecryptUserNumber(sendnumber)和sendnumber是不相等的，結果就不能映射在userNumber上。

 所以select查詢的列名和resultMap的column上的名字是必須相等的。

2、group by 語句不需要別名

 

oracle sql別名