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#))>0
41             ]]>
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 语句不需要别名

 

posted @ 2015-11-16 21:45  YOU_CAN  阅读(1291)  评论(0)  编辑  收藏  举报