oracle使用中的语法

1.分组排序取一条

row_number() over(partition by 分组字段 order by 排序字段 asc) r where r = 1

2.分组取最大主键Id

SELECT MAX(row_id) AS contract_number from CX_BUSI_OPERA group by contract_number

3.分组拼接字符

SELECT ACCOUNT_ID, to_char(wm_concat(CHANNELNAME)) as CHANNEL_NAME FROM
(SELECT DISTINCT T.ACCOUNT_ID, T.CHANNELNAMEFROM CX_ACC_SOURCE T WHERE T.ACCOUNT_ID='1-MOQ1WT')
GROUP BY ACCOUNT_ID

4.分割字符串（只能作为一个单独的字段展示，table值就为table）

SELECT * FROM table(Split(分割字段, ','))
例：SELECT * FROM table(Split('123@324', '@'))

5.列转行函数

listagg(LAST_NAME, '；') within group(order by ROW_ID)

6.extract函数截取时间

select extract(year from date'2011-05-17') year from dual;
select extract(month from date'2011-05-17') month from dual;

7.保留两位小数，转千分位，拼接%

concat(TO_CHAR(TO_CHAR(字段,'fm99999999999999999999999999990.00'),'FM999,999,999,999,990.00'),'%')

8.创建表和索引

-- Create table
create table CUSTOMER_AUTH_CONFIG
(
row_id VARCHAR2(64) not null,
created DATE,
created_by VARCHAR2(20),
last_upd DATE,
last_upd_by VARCHAR2(20),
acc_id VARCHAR2(64),
acc_name VARCHAR2(64),
emp_id VARCHAR2(64),
login VARCHAR2(64),
last_name VARCHAR2(64),
state VARCHAR2(10)
)
comment on column CUSTOMER_AUTH_CONFIG.last_upd
is '最后更新时间';
alter table CUSTOMER_AUTH_CONFIG add constraint CUSTOMER_AUTH_CONFIG_PK primary key (ROW_ID);
--创建普通索引
CREATE INDEX CUSTOMER_AUTH_CONFIG_uindex ON CUSTOMER_AUTH_CONFIG (acc_id, emp_id);
--创建唯一索引
（CREATE unique INDEX CUSTOMER_AUTH_CONFIG_uindex ON CUSTOMER_AUTH_CONFIG (acc_id);）
-- 授权
grant select on SIEBEL.CUSTOMER_AUTH_CONFIG to ecrm_query,gf_geninfo,ecrm_tableau,soau;

　　

9.自增的序列号

ROW_NUMBER() OVER(ORDER BY 字段 DESC) RN

10.全角半角转换

select to_multi_byte('（tes）') from dual、select to_multi_byte('１２３４５23232') from dual;

11.分组求和

sum(统计字段名) over(partition by 分组字段1)

12.一个表从另一个表更新数据

merge into ECRM_TA_VENDOR_SHARES a
using (select b.ACCOUNT_ID,b.ACCOUNT_NAME from cx_account_v b group by b.ACCOUNT_ID,b.ACCOUNT_NAME )c
on (to_single_byte(a.vendor_fullname) = to_single_byte(c.ACCOUNT_NAME))
when matched then
update set a.account_id = c.ACCOUNT_ID

13.oracle匹配包含a—-b格式的数据

LIKE '%a%b'

14.oracle是否包含

instr(holder_name_parent,'广发证券资产管理') >0