Oracle分析函数使用的总结(学练结合)

Oracle分析函数使用总结

1.使用评级函数

评级函数（ranking function）用于计算等级、百分点、n分片等等，下面是几个常用到的评级函数：RANK（）：返回数据项在分组中的排名。特点：在排名相等的情况下会在名次中留下空位DENSE_RANK():与RANK不同的是它在排名相等的情况下不会在名次中留下空位

CUME_DIST()：返回特定值相对于一组值的位置：他是“cumulative distribution”(累积分布)的简写PERCENT_RANK()：返回某个值相对于一组值的百分比排名

NTILE():返回n分片后的值，比如三分片、四分片等等

ROW_NUMBER():为每一条分组纪录返回一个数字

下面我们分别举例来说明这些函数的使用

1）RANK（）与DENSE-RANK（）

首先显示下我们的源表数据的结构及部分数据：

SQL> desc all_sales;

名称是否为空? 类型

----------------------------------------- -------- -----------

YEAR NOT NULL NUMBER(38)

MONTH NOT NULL NUMBER(38)

PRD_TYPE_ID NOT NULL NUMBER(38)

EMP_ID NOT NULL NUMBER(38)

AMOUNT NUMBER(8,2)

SQL> select * from all_sales where rownum<11;

YEAR MONTH PRD_TYPE_ID EMP_ID AMOUNT

---------- ---------- ----------- ---------- ----------

2003 1 1 21 10034.84

2003 2 1 21 15144.65

2003 3 1 21 20137.83

2003 4 1 21 25057.45

2003 5 1 21 17214.56

2003 6 1 21 15564.64

2003 7 1 21 12654.84

2003 8 1 21 17434.82

2003 9 1 21 19854.57

2003 10 1 21 21754.19

已选择10行。

好接下来我们将举例来说明上述函数的使用：首先是RANK（）与DENSE-RANK（）的使用：SQL> select

2 prd_type_id,sum(amount),

3 RANK() OVER (ORDER BY SUM(amount) DESC) AS rank,

4 DENSE_RANK() OVER (ORDER BY SUM(amount) DESC) AS dense_rank

5 from all_sales

6 where year=2003

7 group by prd_type_id

8 order by rank;

PRD_TYPE_ID SUM(AMOUNT) RANK DENSE_RANK

----------- ----------- ---------- ----------

5 1 1

1 905081.84

2 2

3 478270.91 3 3

4 402751.16 4 4

2 186381.22 5 5

注意：这里PRD_TYPE_ID列为5的SUM(AMOUNT)的值为空，RANK（）和DENSE-RANK在这一行的返回值为1。因为默认状态下RANK（）和DENSE-RANK（）在递减排序中将空值指定为最高排名1，而在递增排序中则把它指定为最低排名。这里还有一个问题就是我们的例子中没有SUM(AMOUNT)相等的值，如果有的话RANK与DENSE-RANK将表现出区别比如上面的例子如果PRD_TYPE_ID为4的SUM （AMOUNT）的值也为：478270.91的话，那么上面语句的输出则为：

PRD_TYPE_ID SUM(AMOUNT) RANK DENSE_RANK

----------- ----------- ---------- ----------

5 1 1

1 905081.84

2 2

3 478270.91 3 3

4 478270.91 3 3

2 186381.22 5 4

此外这里还有两个参数来限制空值的排序即：NULLS FIRST和NULLS LAST

我们还以上面的例子来看：

SQL> select

2 prd_type_id,sum(amount),

3 RANK() OVER (ORDER BY SUM(amount) DESC NULLS LAST) AS rank,

4 DENSE_RANK() OVER (ORDER BY SUM(amount) DESC NULLS LAST) AS dense_rank

5 from all_sales

6 where year=2003

7 group by prd_type_id

8* order by rank

PRD_TYPE_ID SUM(AMOUNT) RANK DENSE_RANK

----------- ----------- ---------- ----------

1 905081.84 1 1

3 478270.91 2 2

4 402751.16 3 3

2 186381.22 4 4

5 5 5

可以看出刚才我们不使用NULLS LAST时PRD_TYPE_ID为5的空值的排序位于第一，现在则位于第五。接下来来看分析函数与PARTITION BY子句的结合使用：

当需要把分组划分为子分组时，那么我们便可以结合PRA TITION BY子句和分析函数同时使用。如下例根据月份划分销量：

SQL> select

2 prd_type_id,month,SUM(amount),

3 RANK() OVER (PARTITION BY month ORDER BY SUM(amount) DESC) AS rank

4 from all_sales

5 where year=2003

6 and amount IS NOT NULL

7 GROUP BY prd_type_id,month

8* ORDER BY month,rank

PRD_TYPE_ID MONTH SUM(AMOUNT) RANK

----------- ---------- ----------- ----------

1 1 38909.04 1

3 1 24909.0

4 2

4 1 17398.43 3

2 1 14309.04 4

1 2 70567.9 1

4 2 17267.9 2

3 2 15467.9 3

2 2 13367.9 4

1 3 91826.98 1

4 3 31026.98 2

3 3 20626.98 3

PRD_TYPE_ID MONTH SUM(AMOUNT) RANK

----------- ---------- ----------- ----------

2 3 16826.98 4

1 4 120344.7 1

3 4 23844.7 2

4 4 16144.7 3

2 4 15664.7 4

1 5 97287.36 1

4 5 20087.36 2

3 5 18687.36 3

2 5 18287.36 4

1 6 57387.84 1

4 6 33087.84 2

PRD_TYPE_ID MONTH SUM(AMOUNT) RANK

----------- ---------- ----------- ----------

3 6 19887.8

4 3

2 6 14587.84 4

3 7 81589.0

4 1

1 7 60929.04 2

2 7 15689.04 3

4 7 12089.04 4

1 8 75608.9

2 1

3 8 62408.92 2

4 8 58408.92 3

2 8 16308.92 4

1 9 85027.4

2 1

PRD_TYPE_ID MONTH SUM(AMOUNT) RANK

----------- ---------- ----------- ----------

4 9 49327.42 2

3 9 46127.42 3

2 9 19127.42 4

1 10 105305.2

2 1

4 10 75325.14 2

3 10 70325.29 3

2 10 13525.14 4

1 11 55678.38 1

3 11 46187.38 2

4 11 42178.38 3

2 11 16177.84 4

PRD_TYPE_ID MONTH SUM(AMOUNT) RANK

----------- ---------- ----------- ----------

3 12 48209.0

4 1

1 1

2 46209.04 2

4 12 30409.0

5 3

2 12 12509.04 4

已选择48行。

接下来我们再来看分析函数与我们上次学的ROLLUP、CUBE、GROUPING SETS的结合使用：SELECT

prd_type_id,SUM(amount),

RANK() OVER (ORDER BY SUM(amount) DESC NULLS LAST) AS rank

FROM all_sales

WHERE year=2003

GROUP BY ROLLUP(prd_type_id)

ORDER BY rank;

PRD_TYPE_ID SUM(AMOUNT) RANK

1972485.13 1 （注：RULLUP的总计排在了最前）

1 905081.84 2

oracle函数

许多软件公司都理解开发不依赖于特定数据库类型(例如Oracle、SQL Server、DB2)的应用程序的重要性，它可以让客户们选择自己习惯的平台。一般来说，软件开发人员都能够识别出他们的负责数据库维护的客户，和必须使用现有平台和个性化的客户。 关于Oracle和SQL Server之间的区别，已经有很多篇文章从企业的角度和数据库管理员的角度出发描写了两者之间的一般差别。在这篇文章里面，我将会从应用程序的角度向你描述SQL erver 和oracle平台之间的区别，并且将会讨论开发不依赖于数据库环境的应用程序的几种可能的方法。与此同时，我不会再论讨对于应用程序来说，两个平台之间显而易见的区别，例如表的分割和索引。 定义通用接口和语言有很少的几种通用语言和接口可以让应用程序不依赖于数据库，想来也可以以同样的方式应用在关系型数据库上面: ANSI是美国国家标准局定义的，这是一家志愿者成员的组织(用私人基金运转)，他们在有关设备和程序等广泛领域内开发了国家承认的标准。在数据库领域， ANSI定义了编写SQL命令的标准，假设命令可以运行在任何的数据库上，而不需要更改命令的语法。 ODBC是开放数据库连接(ODBC)接口，微软定义的，它可以让应用程序访问数据库管理系统(DBMS)中的数据，使用SQL作为访问数据的标准。 ODBC允许最大的互联性，这意味着一个单个的应用程序可以访问不同的数据库管理系统。然后，应用程序终端用户可以添加开放数据库连接(ODBC)数据库驱动来将应用程序与他们所选择的数据库管理系统链接上。 OLE DB 是ODBC的继承者，是一组允许例如基于VB、C++、Access等任何可以连接到类似SQL Server、Oracle、DB2、MySQL等后台的“前台”的软件组件。在许多种情况下，OLE DB组件都比原有的ODBC提供了好得多的性能。 JDBC(Java数据库连接)应用程序接口是Java编程语言和广泛范围的数据库，SQL数据库和其他表列数据源(例如电子表格或者普通文本文件)之间，进行不依赖于数据库的连接的行业标准。JDBD应用程序接口为基于SQL的数据库访问提供了调用级别的应用程序接口。真实世界中的通用接口不幸的是，并不是所有数据库级别的命令都是ANSI，每个数据库平台都有自己的扩展功能。对于ANSI或者通用接口，一般来说都代表着几本功能，因此也可能意味着丧失了性能方面的竞争力。对于小型数据库和小型应用程序来说，要维护对数据库的通用访问是简单的，但是当数据库和/或应用程序变得越来越大，越来越复杂，你就不得不向代码中添加功能。 SQL Server和Oracle的常用函数对比 ---------数学函数 1.绝对值 S:select abs(-1) value O:select abs(-1) value from dual 2.取整(大) S:select ceiling(-1.001) value O:select ceil(-1.001) value from dual

Oracle 常见函数(一)——数值函数

Oracle常见数值函数 ----***特别说明***： x 可以是纯的数值，也可以是数值型表达式/* ABS(x)返回x绝对值 eg. */ selectabs(100),abs(-100) from dual; /* sign(x)判断x的正负，正数返回1，负数返回-1,0返回0； eg. */ selectsign(100),sign(-100),sign(0) from dual;

/* round(x[,n])对x进行四舍五入，保留n位小数，其中n采用其整数部分； 没有n时默认四舍五入到整数位，n为负数时，四舍五入保留小数点左边n位(补零)， eg. */ selectround(5555.6666, 2.1), round(5555.6666, -2.6), round(5555.6666) from dual; /* trunc(x)对x进行直接截取，保留n位小数，其中n采用其整数部分； 没有n时默认截取到整数位，n为负数时，截取保留小数点左边n位(补零)， eg. */ selecttrunc(5555.66666,2.1), trunc(5555.66666,-2.6), trunc(5555.033333) from dual; /* ceil(x)对x进行向上取整，返回不小于x的最小整数(可以是整数x本身)。

eg. */ selectceil(3.1), ceil(2.8+1.3), ceil(0) from dual; /* floor(x)对x进行向下取整，返回不大于x的最大整数(可以是整数x本身)。eg. */ selectfloor(3.1), floor(2.8+1.3), floor(0) from dual; /* mod(x,y)求x除以y的余数，x，y为数字型表达式。 eg. */ selectmod(23,8),mod(24,8) from dual;

Oracle函数详解(经典)

Oracle常用函数/过程说明主要介绍Oracle的系统函数、过程和包。 SQL常用函数： 数值函数： ABS Purpose 返回绝对值 Returns the absolute value of n. Example SELECT ABS(-15) "Absolute" FROM DUAL; Absolute ---------- 15 CEIL Purpose 取最小整数 Returns smallest integer greater than or equal to n. Example SELECT CEIL(15.7) "Ceiling" FROM DUAL;

Ceiling ---------- 16 * MOD Syntax MOD(m,n) Purpose 取余 Returns remainder of m divided by n. Returns m if n is 0. Example SELECT MOD(11,4) "Modulus" FROM DUAL; Modulus ---------- 3 * ROUND Syntax ROUND(n[,m]) Purpose 取四舍五入信息 Returns n rounded to m places right of the decimal point; if m is omitted, to 0 places. m can be negative to round off digits left of the decimal point. m must be an integer.

ORACLE常用函数和一些项目中常用的SQL语句

Oracle笔记 实现中英文转换 Alter session set nls_language=‘ADMIN’ 从已知表中复制数据和结构 Create table test as select * from dept； 从已知表中复制数据和结构，但是不包括数据 Create table test as select * from dept where 1=2 插入数据 Insert into test select * from dept 二、运算符 +——*/可以在select 语句中使用 ||连接两个字段，select deptno ||dname from dept 比较运算付：> >= = !=< <= 逻辑运算符：not ，or 集合运算符：intersect，union，union all，minus， 使用ord er by 的时候必须使用位置序号，不能使用列名 Select *from emp intersect select * from emp where deptno=10； Select *from emp minus select * from emp where deptno=10； ---不包括重复行 Select *from emp where deptno=10 union select * from emp where deptno in（10，20）；----包括重复行 Select *from emp where deptno=10 union all select * from emp where deptno in（10，20）； 三、常用ORACLE函数

oracle中常用函数大全

oracle中常用函数大全 1、数值型常用函数 函数返回值样例显示 ceil(n) 大于或等于数值n的最小整数select ceil(10.6) from dual; 11 floor(n) 小于等于数值n的最大整数select ceil(10.6) from dual; 10 mod(m,n) m除以n的余数,若n=0,则返回m select mod(7,5) from dual; 2 power(m,n) m的n次方select power(3,2) from dual; 9 round(n,m) 将n四舍五入,保留小数点后m位select round(1234.5678,2) from dual; 1234.57 sign(n) 若n=0,则返回0,否则,n>0,则返回1,n<0,则返回-1 select sign(12) from dual; 1 sqrt(n) n的平方根select sqrt(25) from dual ; 5 2、常用字符函数 initcap(char) 把每个字符串的第一个字符换成大写select initicap('mr.ecop') from dual; Mr.Ecop lower(char) 整个字符串换成小写select lower('MR.ecop') from dual; mr.ecop replace(char,str1,str2) 字符串中所有str1换成str2 select replace('Scott','s','Boy') from dual; Boycott substr(char,m,n) 取出从m字符开始的n个字符的子串select substr('ABCDEF',2,2) from dual; CD length(char) 求字符串的长度select length('ACD') from dual; 3 || 并置运算符select 'ABCD'||'EFGH' from dual; ABCDEFGH 3、日期型函数 sysdate当前日期和时间select sysdate from dual;

Oracle常用函数及使用案例(珍藏版)

Oracle常用函数及使用案例（珍藏版） 一：sql函数： lower（char）:将字符串转化为小写的格式。 upper（char）:将字符串转化为大写的格式。 length（char）：返回字符串的长度。 substr（char，m，n）：取字符串的字串。 案例1.将所有员工的名字按小写的方式显示 select lower(ename),sal from emp; 案例2.将所有员工的名字按大写的方式显示。 select upper(ename),sal from emp; 案例3.显示正好为五个字符的的员工的姓名。 select * from emp where length(ename)=5; 案例4.显示所有员工姓名的前三个字符。 select substr(ename,1,3) from emp;//从名字的第一个字符开始取，向后取三个字符。 案例5.以首字母为大写的方式显示所有员工的姓名。 （1）首字母大写：select upper(substr(ename,1,1)) from emp; （2）完成后面字母小写。select lower(substr(ename,2,length(ename)-1)) from emp; (3)合并select upper(substr(ename,1,1))||lower(substr(ename,2,length(ename)-1)) from emp; 案例6.以首字母为小写的方式显示所有员工的姓名。（需要有较高的灵活度，细心分析和清晰思路） （1）首字母小写：select upper(substr(ename,1,1)) from emp; （2）完成后面字母大写。select lower(substr(ename,2,length(ename)-1)) from emp; (3)合并select lower(substr(ename,1,1))||upper(substr(ename,2,length(ename)-1)) from emp; 案例7.函数（替换）：replace(char1,search_string,replace_string); 显示所有员工的姓名，用“我要替换A”替代所有“A”。 select replace(ename,'A','我是老鼠')from emp; 案例8.以首字母为小写的方式显示所有员工的姓名。 select replace(ename,substr(ename,1,1),lower(substr(ename,1,1)))from emp; 案例9.以首字母为大写的方式显示所有员工的姓名。 Select replace(ename,substr(ename,2,length(ename)-1),lower(substr(ename,2,length(ename) -1)))from emp; 二：数学函数：(在财务中用的比较多) ronud（sal）用于四舍五默认取整； ronud（sal，1）用于四舍五留一位小数。 trunc（sal）取整，忽略小数。截去小数部分。 trunc（sal，1）截取；小数点留一位，之后的右边的省去。 trunc（sal，-1）截取；只留整数，个位数取零。 floor（sal）向下最接近取整；比如1.1值为1.

oracle常用函数列表速查 (1)

Oracle常用函数列表速查 PL/SQL单行函数和组函数详解 函数是一种有零个或多个参数并且有一个返回值的程序。在SQL中Oracle内建了一系列函数，这些函数都可被称为SQL或PL/SQL语句，函数主要分为两大类： 单行函数 组函数 本文将讨论如何利用单行函数以及使用规则。 SQL中的单行函数 SQL和PL/SQL中自带很多类型的函数，有字符、数字、日期、转换、和混合型等多种函数用于处理单行数据，因此这些都可被统称为单行函数。这些函数均可用于SELECT,WHERE、ORDER BY 等子句中，例如下面的例子中就包含了TO_CHAR,UPPER,SOUNDEX等单行函数。 SELECT ename,TO_CHAR(hiredate,'day,DD-Mon-YYYY')FROM empWhere UPPER(ename) Like 'AL%'ORDER BY SOUNDEX(ename) 单行函数也可以在其他语句中使用，如update的SET子句，INSERT的V ALUES子句，DELET 的WHERE子句,认证考试特别注意在SELECT语句中使用这些函数，所以我们的注意力也集中在SELECT语句中。 NULL和单行函数 在如何理解NULL上开始是很困难的，就算是一个很有经验的人依然对此感到困惑。NULL值表示一个未知数据或者一个空值，算术操作符的任何一个操作数为NULL值，结果均为提个NULL值,这个规则也适合很多函数，只有CONCAT,DECODE,DUMP,NVL,REPLACE在调用了NULL参数时能够返回非NULL值。在这些中NVL函数时最重要的，因为他能直接处理NULL值，NVL有两个参数：NVL(x1,x2),x1和x2都式表达式，当x1为null时返回X2,否则返回x1。 下面我们看看emp数据表它包含了薪水、奖金两项，需要计算总的补偿 column name emp_id salary bonuskey type pk nulls/unique nn,u nnfk table datatype number number numberlength 11.2 11.2 不是简单的将薪水和奖金加起来就可以了，如果某一行是null值那么结果就将是null，比如下面的例子： update empset salary=(salary+bonus)*1.1 这个语句中，雇员的工资和奖金都将更新为一个新的值，但是如果没有奖金，即salary + null,那么就会得出错误的结论，这个时候就要使用nvl函数来排除null值的影响。 所以正确的语句是： update empset salary=(salary+nvl(bonus,0)*1.1

oracle常用函数习题及答案

--1、选择部门30中的雇员 select * from emp where deptno=30; --2、列出所有办事员的姓名、编号和部门 select ename,empno,dname from emp e inner join dept d on e.deptno = d.deptno where job=upper('clerk?); --3、找出佣金高于薪金的雇员 select * from emp where comm>sal; --4、找出佣金高于薪金60%的雇员 select * from emp where comm>sal*0.6 --5、找出部门10中所有经理和部门20中的所有办事员的详细资料 select * from emp where (deptno=10 and job=upper('manager')) or (deptno=20 and job=upper('clerk ')); --6、找出部门10中所有经理、部门20中所有办事员，既不是经理又不是办事员但其薪金>=2000的所有雇员的详细资料 select * from emp where (deptno=10 and job=upper('manager')) or (deptno=20 and job=upper('clerk ')) or (job<>upper(…manager?) and job<>upper(…clerk?) and sal>=2000) --7、找出收取佣金的雇员的不同工作 select distinct job from emp where comm>0; --8、找出不收取佣金或收取的佣金低于100的雇员 select * from emp where nvl(comm,0)<100; --9、找出各月最后一天受雇的所有雇员 select * from emp where hiredate= last_day(hiredate); --10、找出早于25年之前受雇的雇员

oracle常用函数介绍及其使用

Oracle函数 Oracle SQL提供了用于执行特定操作的专用函数，这些函数大大增强了SQL语言的功能。函数可以接受零个或者多个输入参数，并返回一个输出结果。Oracle数据库中主要使用两种类型的函数： 1.单行函数：对每一个函数应用在表的记录中时，只能输入一行结果，返回一个结 果，比如：MOD(x,y)返回x除以y的余数（x和y可以是两个整数，也可以是表中 的整数列）。常用的单行函数有： 字符函数：对字符串操作。 数字函数：对数字进行计算，返回一个数字。 转换函数：可以将一种数据类型转换为另外一种数据类型。 日期函数：对日期和时间进行处理。 2.聚合函数：聚合函数同时可以对多行数据进行操作，并返回一个结果。比如SUM(x) 返回结果集中x列的总合。 目录大纲 Oracle函数 (1) ?字符函数 (2) ?数字函数 (3) ?日期函数 (4) ?转换函数 (6) ?其他单行函数 (8) ?聚合函数 (9)

字符函数接受字符参数，这些参数可以是表中的列，也可以是一个字符串表达式。下表列出了常用的字符函数。 表1 字符函数 表2 字符函数示例

数字函数接受数字参数，参数可以来自表中的一列，也可以是一个数字表达式。 表3 数字函数 说明： 1.ROUND(X[,Y])，四舍五入。 在缺省y时，默认y=0；比如：ROUND(3.56)=4。 y是正整数，就是四舍五入到小数点后y位。ROUND(5.654,2)=5.65。 y是负整数，四舍五入到小数点左边|y|位。ROUND(351.654,-2)=400。 2.TRUNC(x[,y])，直接截取，不四舍五入。 在缺省y时，默认y=0；比如：TRUNC (3.56)=3。 y是正整数，就是四舍五入到小数点后y位。TRUNC (5.654,2)=5.65。 y是负整数，四舍五入到小数点左边|y|位。TRUNC (351.654,-2)=300。

Oracle常用函数

数值函数

日期函数 SELECT TO_CHAR(sysdate,'YYYY-MM-DD HH24:MI:SS AM DY') FROM dual; SELECT TO_CHAR(sysdate,'YYYY"年"MM"月"DD"日"') FROM dual; 其他函数

5.高级查询（多表联合查询） 例子： SELECT job 职务 , SUM(sal) 工资总和 FROM emp WHERE job != 'PRESIDENT' GROUP BY job HAVING SUM(sal)>4500 ORDER BY SUM(sal); 分析函数 以下三个分析函数用于计算一个行在一组有序行中的排位，序号从1开始 ROW_NUMBER返回连续的排位，不论值是否相等 RANK具有相等值的行排位相同，序数随后跳跃 DENSE_RANK 具有相等值的行排位相同，序号是连续的 例子： SELECT deptno, ename, sal, comm, RANK() OVER (PARTITION BY deptno ORDER BY sal DESC, comm) RANK FROM emp; SELECT ename, job, deptno, sal, ROW_NUMBER() OVER (ORDER BY sal DESC) AS SAL_RANK FROM SCOTT.EMP; SELECT d.dname, e.ename, e.sal, DENSE_RANK() OVER (PARTITION BY e.deptno ORDER BY e.sal DESC) AS DENRANK FROM emp e, dept d WHERE e.deptno = d.deptno;

ORACLE内置函数使用手册

ORACLE内置函数使用手册 ASCII()——返回字符的ASCCII码； CHAR()——当给定ASCII码返回字符； concat()——连接函数，相当于运算符“||” e.g:select CONCAT(CONCAT(name,'is a'),job) "Job" From emp result:James is a recorder initcap()——返回函数内的单词，每个词首字母大写，其他字母转化为小写，不影响非字母字符； e.g:select INITCAP('the emp') Form Emp result:The Emp INSTR(char1,char2[,n[,m]])——返回char1中包含char2的位置。n为负数时，从右往左查，第m次的结果被返回，返回其下标，缺省值都是1，若没找到，返回0。 e.g: select INSTR('CORPORATE FLOOR','OR',3,2) from table result:14 select INSTR('CORPORATE FLOOT','OR',-3,2) FROM TABLE RESULT:2 INSTRB类似INSTR,区别于，对于单字节字符系统，返回的值以字节为单位； LENGTH(CHAR)——以字节返回字符串char的长度，包括填充的字符。若char有数据型char，则字符串的长度包括其结尾的空格。若char为NULL，则该函数返回NULL； LENGTHB()——类似LENGTH(),区别于，对于单字节字符集，返回的值以字节为单位； lower()——转化小写 e.g:select lower('HELLO') from table result:hello lpad(char1,length[,char2])—— char1左边增长到length，左边补char2。char2未设定时，默认空格，当char2小于length时，仅前几个被复制 例：select lpad('hugo',10,'*#') from table result：*#*#*#hugo LTRIM('char [,set])——删除char左边出现的set字符。 例：Select ltrim('abaabhi','ab') 运行返回：aabhi

oracle常用函数使用大全

oracle常用函数使用大全 一、运算符 算术运算符：+ - * / 可以在select 语句中使用 连接运算符：|| select deptno|| dname from dept; 比较运算符：> >= = != < <= like between is null in 逻辑运算符：not and or 集合运算符：intersect ，union，union all，minus 要求：对应集合的列数和数据类型相同 查询中不能包含long 列 列的标签是第一个集合的标签 使用order by时，必须使用位置序号，不能使用列名 例：集合运算符的使用： intersect ，union，union all，minus select * from emp intersect select * from emp where deptno=10 ; select * from emp minus select * from emp where deptno=10; select * from emp where deptno=10 union select * from emp where deptno in (10,20); --不包括重复行 select * from emp where deptno=10 union all select * from emp where deptno in (10,20); --包括重复行 二.ORACLE日期时间函数大全 TO_DATE格式(以时间:2007-11-02 13:45:25为例) Year: yy two digits 两位年显示值:07 yyy three digits 三位年显示值:007 yyyy four digits 四位年显示值:2007 Month: mm number 两位月显示值:11 mon abbreviated 字符集表示显示值:11月,若是英文版,显示nov month spelled out 字符集表示显示值:11月,若是英文版,显示november

Oracle分析函数使用总结

Oracle分析函数使用总结 1.使用评级函数 评级函数（ranking function）用于计算等级、百分点、n分片等等，下面是几个常用到的评级函数：RANK（）：返回数据项在分组中的排名。特点：在排名相等的情况下会在名次中留下空位DENSE_RANK():与RANK不同的是它在排名相等的情况下不会在名次中留下空位 CUME_DIST()：返回特定值相对于一组值的位置：他是“cumulative distribution”(累积分布)的简写PERCENT_RANK()：返回某个值相对于一组值的百分比排名 NTILE():返回n分片后的值，比如三分片、四分片等等 ROW_NUMBER():为每一条分组纪录返回一个数字 下面我们分别举例来说明这些函数的使用 1）RANK（）与DENSE-RANK（） 首先显示下我们的源表数据的结构及部分数据： SQL> desc all_sales; 名称是否为空? 类型 ----------------------------------------- -------- ----------- YEAR NOT NULL NUMBER(38) MONTH NOT NULL NUMBER(38) PRD_TYPE_ID NOT NULL NUMBER(38) EMP_ID NOT NULL NUMBER(38) AMOUNT NUMBER(8,2) SQL> select * from all_sales where rownum<11; YEAR MONTH PRD_TYPE_ID EMP_ID AMOUNT ---------- ---------- ----------- ---------- ---------- 2003 1 1 21 10034.84 2003 2 1 21 15144.65 2003 3 1 21 20137.83 2003 4 1 21 25057.45 2003 5 1 21 17214.56 2003 6 1 21 15564.64 2003 7 1 21 12654.84 2003 8 1 21 17434.82 2003 9 1 21 19854.57 2003 10 1 21 21754.19 已选择10行。 好接下来我们将举例来说明上述函数的使用：首先是RANK（）与DENSE-RANK（）的使用：SQL> select 2 prd_type_id,sum(amount), 3 RANK() OVER (ORDER BY SUM(amount) DESC) AS rank, 4 DENSE_RANK() OVER (ORDER BY SUM(amount) DESC) AS dense_rank 5 from all_sales

ORACLE常用的函数

ORACLE常用函数 ORACLE常用数值函数转换函数字符串函数介绍。 数值函数： abs(m)m的绝对值 mod(m,n)m被n除后的余数 power(m,n)m的n次方 round(m[,n])m四舍五入至小数点后n位的值（n缺省为0） trunc(m[,n])m截断n位小数位的值（n缺省为0） -------------------------------------------------------------------------------- 字符函数： initcap(st)返回st将每个单词的首字母大写，所有其他字母小写 lower(st)返回st将每个单词的字母全部小写 upper(st)返回st将每个单词的字母全部大写 concat(st1,st2)返回st为st2接st1的末尾（可用操作符"||"） lpad(st1,n[,st2]) 返回右对齐的st,st为在st1的左边用st2填充直至长度为n,st2的缺省为空格 rpad(st1,n[,st2]) 返回左对齐的st,st为在st1的右边用st2填充直至长度为n,st2的缺省为空格 ltrim(st[,set]) 返回st,st为从左边删除set中字符直到第一个不是set中的字符。缺省时，指的是空格 rtrim(st[,set]) 返回st,st为从右边删除set中字符直到第一个不是set中的字符。缺省时，指的是空格 replace(st,search_st[,replace_st]) 将每次在st中出现的search_st用replace_st 替换，返回一个st。缺省时，删除search_st substr(st,m[,n]) n=返回st串的子串，从m位置开始，取n个字符长。缺省时，一直返回到st末端 length(st) 数值，返回st中的字符数 instr(st1,st2[,m[,n]]) 数值，返回st1从第m字符开始，st2第n次出现的位置，m 及n的缺省值为1 例： 1. select initcap('THOMAS'),initcap('thomas') from test; initca initca

常用函数汇总

Excel 常用函数汇总 一. 统计函数 1.SUM 求和 计算选定区域的总和，只对数值起作用，忽略空白单元格、本文和逻辑值。 2.SUMIF 条件求和 参数1. Range表示条件所在的区域 参数2. Criteria表示具体的条件，如果是字符，需要用引号，如：本科，>8000等 参数3. Sum range表示需要求和的区域，如果求和区域和条件区域相同，可以缺省。 3.DSUM 多条件求和 数据库求和，需要求和的表格必须是数据清单 参数1. Database 表示需要求和的数据清单，注意，一定要选择字段行 参数2. Field表示需要求和的列，一般选择字段名或者列号 参数3. Criteria表示条件所在的区域，应该先将条件输入到一个独立的区域，条件在同一行表示“并且”关系，不同行表示“或者”关系。 4.A VERAGE 求平均值 计算选定区域的算术平均数，只对数值起作用，忽略空白单元格、本文和逻辑值。 5.A VERAGEIF 按条件计算平均值 该函数在2013及以后的版本才可以使用，以前的版本可以用SUMIF/COUNTIF替代参数1. Range表示条件所在的区域 参数2. Criteria表示具体的条件，如果是字符，需要用引号 参数3. Average range表示需要求平均值的区域，如果求平均值区域和条件区域相同，可以缺省该参数。 6.DA VERAGE 多条件求平均值 数据库求平均值，需要求平均值的表格必须是数据清单 参数1. Database 表示需要求平均值的数据清单，注意，一定要选择字段行 参数2. Field表示需要求平均值的列，一般选择字段名或者列号 参数3. Criteria表示条件所在的区域，应该先将条件输入到一个独立的区域，条件在同一行表示“并且”关系，不同行表示“或者”关系。 7.COUNT 计数 统计选定区域内包含数值单元格的个数，只对数值起作用，忽略空白单元格、本文和逻辑值。如果计算结果为8，表示选定的区域内有8个单元格包含数值。 8.COUNTA 计数 统计选定区域内非空单元格的个数，忽略空白单元格。如果计算结果为8，表示选定的区域内有8个单元格非空白。

Oracle常用函数一览

Oracle常用函数一览 2008年06月02日星期一04:54 PL/SQL单行函数和组函数详解 函数是一种有零个或多个参数并且有一个返回值的程序。在SQL中Oracle内建了一系列函数，这些函数都可被称为SQL或PL/SQL语句，函数主要分为两大类： 单行函数 组函数 本文将讨论如何利用单行函数以及使用规则。 SQL中的单行函数 SQL和PL/SQL中自带很多类型的函数，有字符、数字、日期、转换、和混合型等多种函数用于处理单行数据，因此这些都可被统称为单行函数。这些函数均可用于SELECT,WHERE、ORDER BY等子句中，例如下面的例子中就包含了TO_CHAR,UPPER,SOUNDEX等单行函数。 SELECT ename,TO_CHAR(hiredate,'day,DD-Mon-YYYY')FROM empWhere UPPER(ename) Like 'AL%'ORDER BY SOUNDEX(ename) 单行函数也可以在其他语句中使用，如update的SET子句，INSERT的VALUES子句，DELET的WHERE 子句,认证考试特别注意在SELECT语句中使用这些函数，所以我们的注意力也集中在SELECT语句中。 NULL和单行函数 在如何理解NULL上开始是很困难的，就算是一个很有经验的人依然对此感到困惑。NULL值表示一个未知数据或者一个空值，算术操作符的任何一个操作数为NULL值，结果均为提个NULL值,这个规则也适合很多函数，只有CONCAT,DECODE,DUMP,NVL,REPLACE在调用了NULL参数时能够返回非NULL值。在这些中NVL函数时最重要的，因为他能直接处理NULL值，NVL有两个参数：NVL(x1,x2),x1和x2都式表达式，当x1为null时返回X2,否则返回x1。 下面我们看看emp数据表它包含了薪水、奖金两项，需要计算总的补偿 column name emp_id salary bonuskey type pk nulls/unique nn,u nnfk table datatype number number numberlength 11.2 11.2 不是简单的将薪水和奖金加起来就可以了，如果某一行是null值那么结果就将是null，比如下面的例子：update empset salary=(salary+bonus)*1.1 这个语句中，雇员的工资和奖金都将更新为一个新的值，但是如果没有奖金，即salary + null,那么就会得出错误的结论，这个时候就要使用nvl函数来排除null值的影响。 所以正确的语句是： update empset salary=(salary+nvl(bonus,0)*1.1 单行字符串函数 单行字符串函数用于操作字符串数据，他们大多数有一个或多个参数，其中绝大多数返回字符串 ASCII() c1是一字符串，返回c1第一个字母的ASCII码，他的逆函数是CHR() SELECT ASCII('A') BIG_A,ASCII('z') BIG_z FROM empBIG_A BIG_z65 122 CHR(＜i＞)[NCHAR_CS] i是一个数字，函数返回十进制表示的字符

SQLServer和Oracle常用函数对比

MSSQL 绝对值abs(-1) 取整(大)ceiling(-1.001) 取整（小）floor(-1.001) 取整（截取）cast(-1.002 as int) 四舍五入round(1.23456,4) e为底的幂Exp(1) 取e为底的对数log(2.7182818284590451) 取10为底对数log10(10) 取平方SQUARE(4) 取平方根SQRT(4) 求任意数为底的幂power(3,4) 取随机数rand() 取符号sign(-8) 圆周率PI() 求集合最大值max() 求集合最小值min(value) 如何处理null值IsNull(F2,10) 求字符序号ascii('a') 从序号求字符char(97) 连接11'+'22'+'33' 字符位置 --返回3CHARINDEX('s','sdsq',2) 模糊子串的位置 -- 返回2,参数去掉中 间%则返回7 patindex('%d%q%','sdsfasdqe')求子串substring('abcd',2,2) 子串代替 返回 aijklmnef STUFF('abcdef', 2, 3, 'ijklmn')子串全部替换 长度len,datalength 大小写转换lower,upper 单词首字母大写 左补空格（LPAD的 第一个参数为空格 则同space函数） space(10)+'abcd' 右补空格（RPAD的 第一个参数为空格 则同space函数） 'abcd'+space(10) 删除空格ltrim,rtrim 重复字符串REPLICATE('abcd',2) 系统时间getdate() 求日期convert(char(10),getdate(),20)求时间convert(char(8),getdate(),108) 取日期时间的其他部分DATEPART 和 DATENAME 函数 SQLServer和Oracle常用