(allsal)fromgao.table3;

min(allsal)

-----------

1111.11

63.stddev(distinct|all)

求标准差,all表示对所有的值求标准差,distinct表示只对不同的值求标准差

sql>selectstddev(sal)fromscott.emp;

stddev(sal)

-----------

1182.5032

sql>selectstddev(distinctsal)fromscott.emp;

stddev(distinctsal)

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

1229.951

64.variance(distinct|all)

求协方差

sql>selectvariance(sal)fromscott.emp;

variance(sal)

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

1398313.9

65.groupby

主要用来对一组数行统计

sql>selectdeptno,count(*),sum(sal)fromscott.empgroupbydeptno;

deptnocount(*)sum(sal)

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

1038750

20510875

3069400

66.having

对分组统计再加限制条件

sql>selectdeptno,count(*),sum(sal)fromscott.empgroupbydeptnohavingcount(*)>=5;

deptnocount(*)sum(sal)

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

20510875

3069400

sql>selectdeptno,count(*),sum(sal)fromscott.emphavingcount(*)>=5groupbydeptno;

deptnocount(*)sum(sal)

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

20510875

3069400

67.orderby

用于对查询到的结果行排序输

sql>selectdeptno,ename,salfromscott.emporderbydeptno,saldesc;

deptnoenamesal

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

10king5000

10rk2450

10miller1300

20scott3000

20ford3000

20jones2975

20adams1100

20smith800

30ke2850

30allen1600

30turner1500

30ward1250

30martin1250

30james950

oracle最常用功能函数经典汇总

来源：chinaib收集整理

2004-6-1410:58:00

*sqlgroupfunction

*

s(numcanbeacolumnorex

pression)

(nullvaluesareign

*

ored,defaultbetweendistin

ctandallisall)

*

avg([distinctorall]num)

--averagevalue

count(distinctorall]num)