oracle 方法函数记录 #oracle#函数

row_number() over的运用

select * from (select a.*, row_number() over(partition by a.agency_id order by sumv1 desc) rn from ( --视图部分 select agency_id, bill_id, sum(v1) over(partition by agency_id order by bill_id) as sumv1, sum(v2) over(partition by agency_id order by bill_id) as sumv2, sum(v3) over(partition by agency_id order by bill_id ) as sumv3, sum(v4) over(partition by agency_id order by bill_id) as sumv4 from test_zxy) a --视图部分结束 where bill_id in('b11','b12','b13','b14','b15')) b where rn = 1; select * from test_zxy;

listagg

SELECT listagg('h.' || T.COLUMN_NAME ||',') within group(ORDER BY T.COLUMN_NAME) as concat_clo_name FROM USER_TAB_COLUMNS T WHERE T.TABLE_NAME='SAL_PERSON' and T.COLUMN_NAME !='STATUS' ";

拆分字符串 并行专列

select regexp_substr('1,2,3,4,5','[^,]+',1,rownum) from dual

 connect by rownum<=length('1,2,3,4,5')-length(replace('1,2,3,4,5',','))+1；