oracle sql别名
时间：2021-07-01 10:21:17
帮助过：3人阅读
<!
-- 获取已发或待发送的彩信记录列表 -->
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 语句不需要别名
oracle sql别名
标签：
