欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

oracle函数

程序员文章站 2022-04-12 21:12:59
oracle函数 1.oracle的substr函数的用法: 取得字符串中指定起始位置和长度的字符串 substr( string, start_position, [ len...

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)