常用oracle 函数

1.oracle的 substr函数的用法：  取得字符串中指定起始位置和长度的字符串   substr( string, start_position, [ length ] )。 例：substr('This is a test',  6,  2)     would  return 'is'      substr('This is a test',  6)     would  return 'is a test'       substr('TechOnTheNet',  - 3,  3)     would  return 'Net' 2.将Oracle中同一列的多行记录拼接成一个字符串 用 wm_concat函数: select wm_concat(space_full_name) from ZD_TOBACCO_STORAGE_SPACE where TOBACCO_ID =' ' group by TOBACCO_ID 需要 排序时： select ssd.tobacco_id,max(r) from (select ssd0.space_code,ssd0.tobacco_id, wm_concat(ssd0.space_name) over (partition by ssd0.tobacco_id order by to_number(ssd0.space_code)) r from zd_tobacco_storage_space_detai ssd0)ssd group by ssd.tobacco_id 3.查询不包含汉字的（ like '%\%' 是包含 ，not like 不包含） where asciistr(t.tobacco_code) not like '%\%' 4.查询每条数据当天最后一条 select wh.unit_id, wh.client_id, wh.client_name, wh.large_address_id, wh.large_address_name, wh.tobacco_type, wh.tobacco_state, wh.tobacco_number,wh.tobacco_weight,wh.change_time, row_number() over( partition by wh.tobacco_id order by wh.change_time desc) rn 根据wh.tobacco_id group by再根据wh.change_time时间倒序查，该分组rn从1开始 from view_TobaccoReceive_Record wh 5.四舍五入 round(to_number(sum(b.AFWEI))/to_number(decode(sum(b.BFWEI),0,1,sum(b.BFWEI))),5) 6.类似于三目运算 decode(sum(b.BFWEI),0,1,sum(b.BFWEI)) 当sum(b.BFWEI)=0时，取值为1，当sum(b.BFWEI)！=0时，取值sum(b.BFWEI)