Oracle数据库——函数 http://www.jb51.net/article/40469.htm

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 ;

上一篇:java基础49 IO流技术(对象输入流/对象输出流)


下一篇:OpenJudge 2810(1543) 完美立方 / Poj 1543 Perfect Cubes