ALTER SESSION SET NLS_SORT=''; 排序影响整个会话
Oracle9i之前,中文是按照二进制编码进行排序的。   在oracle9i中新增了按照拼音、部首、笔画排序功能。设置NLS_SORT值   SCHINESE_RADICAL_M 按照部首(第一顺序)、笔划(第二顺序)排序   SCHINESE_STROKE_M 按照笔划(第一顺序)、部首(第二顺序)排序   SCHINESE_PINYIN_M 按照拼音排序
oracle9i中新增了按照拼音、部首、笔画排序功能
拼音 SELECT * FROM TEAM ORDER BY NLSSORT(列名,'NLS_SORT = SCHINESE_PINYIN_M')
笔划 SELECT * FROM TEAM ORDER BY NLSSORT(列名,'NLS_SORT = SCHINESE_STROKE_M')
部首 SELECT * FROM TEAM ORDER BY NLSSORT(列名,'NLS_SORT = SCHINESE_RADICAL_M')
忽略大小写:SELECT * FROM TEAM ORDER BY NLSSORT(列名,'NLS_SORT =BINARY_CI')
1、设置NLS_SORT参数值
SCHINESE_RADICAL_M 按照部首(第一顺序)、笔划(第二顺序)排序
SCHINESE_STROKE_M 按照笔划(第一顺序)、部首(第二顺序)排序
SCHINESE_PINYIN_M 按照拼音排序
2、Session级别的设置,修改ORACLE字段的默认排序方式:
按拼音:alter session set nls_sort = SCHINESE_PINYIN_M;
按笔画:alter session set nls_sort = SCHINESE_STROKE_M;
按偏旁:alter session set nls_sort = NLS_SORT=SCHINESE_RADICAL_M;
3、语句级别设置排序方式:
按照笔划排序 select * from dept order by nlssort(name,'NLS_SORT=SCHINESE_STROKE_M');
按照部首排序 select * from dept order by nlssort(name,'NLS_SORT=SCHINESE_RADICAL_M');
按照拼音排序,此为系统的默认排序方式
select * from dept order by nlssort(name,'NLS_SORT=SCHINESE_PINYIN_M');
4、修改系统参数(数据库所在操作系统):
set NLS_SORT=SCHINESE_RADICAL_M ;
export NLS_SORT (sh)
setenv NLS_SORT SCHINESE_RADICAL_M (csh)
HKLC\SOFTWARE\ORACLE\home0\NLS_SORT (win注册表)
     <select id="EquipmentList" parameterType="java.util.HashMap" resultMap="resultSimDevInfoMap">
select * from
(
select A.*,rownum RN from
(
SELECT t.dev_id,t.dev_name,t.mac_addr,t.org_id,get_org_name(t.org_id) org_name FROM bsec_dev_info t
<where>
t.IS_ENABLED=1 and t.IS_DELETED = 0
and t.org_id in (SELECT ORG_ID FROM SYS_ORG START WITH ORG_ID = (select t.org_id from SYS_USER t where t.user_id=#{userId,jdbcType=NUMERIC}) CONNECT BY PARENT_ID = PRIOR ORG_ID )
<if test="devName !='' and devName !=null">
and t.dev_name like '%'||#{devName,jdbcType=VARCHAR}||'%'
</if>
</where>
order by NLSSORT(t.dev_name,'NLS_SORT = SCHINESE_PINYIN_M')
) A
)
where RN between #{pagination.startIndex} and #{pagination.endIndex}
</select>
05-06 03:20