rpad()函数：sql补齐字符

rpad函数从右边对字符串使用指定的字符进行填充

rpad(string,padded_length,[pad_string])

1. string:表示需要被补齐的字符串
2. padded_length：字符的长度，是补齐后返回的字符串的数量，如果这个数量比原字符串的长度要短，rpad函数将会把字符串截取成从左到右的n个字符;
3. pad_string 是个可选参数，这个字符串是要粘贴到string的右边，如果这个参数未写，rpad函数将会在string的右边粘贴空格。

例如：

select *,rpad(order_num,10,'0') as order_num1  from unit

此语句会将三位的order_num后面补上0,补齐十位为止

order_num :998

order_num1:9980000000

这个函数可以用于独立一套order_num层级单位生成整体的排序值。

//在后面补齐十位才可以排序（假设有三层层级）
select *,rpad(o.order_NMMMM,10,'0') as order_num1 
from (SELECT
pi.uuid      ,
pi.name      ,
pi.sex       ,
  (select substr(SYS_CONNECT_BY_PATH(unit_name, '/'))
        from "UNIT"
        where  unit_ID ='0'//根节点的Unit_id为0
        START WITH unit_id = pi.unit_id
        CONNECT BY unit_id = PRIOR SUPERIOR_UNIT_ID) as unit_name,//递归查询单位全名称
        
 (select reverse(SYS_CONNECT_BY_PATH(reverse(order_num),''))as order_num
from "OA_PERSONNEL"."PERSONNEL_UNIT"
where  UNIT_ID ='0'//根节点的Unit_id为0
START WITH unit_id = pi.unit_id
CONNECT BY unit_id = PRIOR SUPERIOR_UNIT_ID)  order_NMMMM,//生成拼接的不够位数的临时order
pu.superior_unit_name,
pu.unit_name pname,
pu.superior_unit_id,
pi.order_num,
pi.UNIT_ID
from
INFO pi
left join
unit pu on pi.UNIT_ID=pu.UNIT_ID
where
pu.invalid='0'
and pi.invalid='0'
and pu.level_code like concat((select level_code
    from OA_PERSONNEL.PERSONNEL_unit u where u.unit_id='0') ,'%')) as o//层级码控制查询到的都是子部门中的人员
ORDER BY order_num1,order_num