oracle sql别名

为sql的字段起一个别名，常以为是可有可无的，但是有时候是必要的。

例如在ibatis中

 <!-- 获取已发或待发送的彩信记录列表 -->

     <resultMap id="mmsListByRecordTypeMap" class="richinfo.mms.bean.req.MmsRecordReq" >

         <result column="groupid" property="groupId" />

         <result column="subject" property="title" />

         <result column="sendnumber" property="userNumber" />

         <result column="destnumbers" property="destNumber" />

         <result column="istime" property="isTimming" typeHandler="richinfo.mms.util.MyBatisTypeHandlerCallback"/>

         <result column="statuss" property="status" />

         <result column="startsendtime" property="sendTime" />

         <result column="totalsize" property="totalSize" />

     </resultMap>

     <select id="getMmsListByRecordType" resultMap="mmsListByRecordTypeMap"

         resultClass="map">

         select * from (select b.*,rownum r from ( select

         uin,FN_GetDecryptUserNumber(sendnumber) sendnumber,subject,to_char(startsendtime, 'yyyy-mm-dd hh24:mi:ss')

         startsendtime,istime,showfileid,totalsize,showtype,groupid,

         wmsys.wm_concat(FN_GetDecryptUserNumber(destnumber)) destnumbers,wmsys.wm_concat(nvl(status,

         0)) statuss from mms_send_his_record where 1=1

         <isEqual prepend="and" property="recordType" compareValue="0">

             issave=1

         </isEqual>

         <isEqual prepend="and" property="recordType" compareValue="1">

         <![CDATA[

             (istime=0 or (istime=1 and startsendtime<=sysdate)) and issave=1

         ]]>

         </isEqual>

         <isEqual prepend="and" property="recordType" compareValue="2">

         <![CDATA[

             istime=1 and startsendtime>sysdate

             ]]>

         </isEqual>

         and uin = #uin#

         <include refid="condition" />

         <![CDATA[

          group by groupid,uin,FN_GetDecryptUserNumber(sendnumber),subject,startsendtime,istime,showfileid,totalsize,showtype order by startsendtime desc) b

           where rownum <= #endRecord#

          ]]>

         <isNotEmpty property="destNumber">

         <![CDATA[

             and instr(destnumbers,#destNumber#)>0  or instr(destnumbers,fn_encrypt_function(#destNumber#))>0

             ]]>

         </isNotEmpty>

         ) where r > #startRecord#

     </select>

1、必须为第15、17行起别名

　　如果不为第15、17行起别名的话，执行sql语句就会出问题。因为这个select的查询结果集映射在了mmsListByRecordTypeMap上，这时候

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

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

2、group by 语句不需要别名

相关文章