1====分析函数 相当于把分组后的结果加到每一行里
SELECT t.loan_contract_no,t.loan_name,t.loan_amount,ROWNUM,
row_number() over (PARTITION BY t.area_no ORDER BY t.loan_amount DESC),--行号
round(avg(t.loan_amount) over (PARTITION BY t.area_no ORDER BY t.loan_amount DESC)),
round(SUM(t.loan_amount) over (PARTITION BY t.area_no ORDER BY t.loan_amount DESC)),
round(rank() over (PARTITION BY t.area_no ORDER BY t.loan_amount DESC)),--排名不连续
round(dense_rank() over (PARTITION BY t.area_no ORDER BY t.loan_amount DESC)),--排名序号连续
round(MAX(t.loan_amount) over (PARTITION BY t.area_no ORDER BY t.loan_amount DESC)),
round(MIN(t.loan_amount) over (PARTITION BY t.area_no )),--不能加 ORDER BY t.loan_amount DESC
round(first_value(t.loan_amount) over (PARTITION BY t.area_no )),
round(last_value(t.loan_amount) over (PARTITION BY t.area_no )),
lag(t.loan_amount,1,1) over ( ORDER BY t.loan_amount DESC),--最大用1填充
lead(t.loan_amount,1,0) over ( ORDER BY t.loan_amount DESC)--最小用0填充
FROM account_info t WHERE t.grant_loan_date>'2016-04-01';
select t.*
from scott.emp t ORDER BY t.deptno,t.sal;
SELECT t.deptno,t.ename,t.sal,last_value(t.sal) over (PARTITION BY t.deptno ORDER BY t.sal ROWS BETWEEN 2 preceding AND 2 following ) from scott.emp t;
SELECT t.deptno,t.ename,t.sal,MAX(t.sal) over (PARTITION BY t.deptno ORDER BY t.sal ROWS between unbounded preceding AND unbounded following ) from scott.emp t;
SELECT t.empno,t.deptno,t.ename,t.sal,SUM(t.sal) over (PARTITION BY t.deptno ORDER BY t. ename) from scott.emp t ORDER BY 2,3;
SELECT t.empno,t.deptno,t.ename,t.sal,SUM(t.sal) over (PARTITION BY t.deptno ORDER BY t. ename ROWS BETWEEN unbounded preceding AND CURRENT ROW) from scott.emp t ORDER BY 2,3;
1、instr
在Oracle/PLSQL中，instr函数返回要截取的字符串在源字符串中的位置。只检索一次，就是说从字符的开始
到字符的结尾就结束。
语法如下：
instr( string1, string2 [, start_position [, nth_appearance ] ] )
参数分析：
string1
源字符串，要在此字符串中查找。
string2
要在string1中查找的字符串.
start_position
代表string1 的哪个位置开始查找。此参数可选，如果省略默认为1. 字符串索引从1开始。如果此参数为正，从左到右开始检索，如果此参数为负，从右到左检索，返回要查找的字符串在源字符串中的开始索引。
nth_appearance
代表要查找第几次出现的string2. 此参数可选，如果省略，默认为 1.如果为负数系统会报错。
注意：
如果String2在String1中没有找到，instr函数返回0.
SELECT code , name , dept, occupation FROM staff WHERE code IN ('A10001','A10002');
或者：
SELECT code , name , dept, occupation FROM staff WHERE code = 'A10001' OR code = 'A10002';
或者
SELECT code , name , dept, occupation FROM staff WHERE
instr('A10001,A10002',code)>0;
SELECT code, name, dept, occupation FROM staff WHERE instr(code, '001') > 0;
等同于
SELECT code, name, dept, occupation FROM staff WHERE code LIKE '%001%' ;
//计算年龄
SELECT Extract(year from sysdate)-Extract(year FROM to_date(i.DATE_OF_BIRTH,'yyyy-mm-dd')) FROM crf_p2p_loan_idcard_info i ;