聚集,分组,行转列

多行 聚集 count() max() avg min 

分组 count()  from ..  group by 

分析 count() over() from 

多行函数 聚集函数

常用聚集函数

是对一组或一批数据进行综合操作后返回一个结果

count 行总数

avg 平均数

sum 列值的和

max 最大值

min 最小值

count([{distinct|all} '列名'|*) 为列值时空不在统计之内

为*时包含空行和重复行

idle> select count(comm) from emp;

COUNT(COMM)

-----------

  4

idle> select count(ename) from emp;

COUNT(ENAME)

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

  14

idle> select count(*) from emp;

  COUNT(*)

----------

14

idle> 

idle> select count(deptno) from emp;

COUNT(DEPTNO)

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

   14

idle> select count(distinct deptno) from emp;

COUNT(DISTINCTDEPTNO)

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

    3

idle> select count(all deptno) from emp;

COUNT(ALLDEPTNO)

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

      14

idle> 

idle> select avg(sal),sum(sal),max(sal),min(sal),count(sal) from emp;

  AVG(SAL)   SUM(SAL) MAX(SAL)   MIN(SAL) COUNT(SAL)

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

2073.21429 29025     5000 800     14

idle> 

上面执行的聚集函数都是对所有记录统计

如果想分组统计(比如统计部门的平均值)需要使用group by 为了限制分组统计的结果需要使用having过滤

GROUP BY 分组统计  9I要排序 10G不排序

求出每个部门的平均工资

idle> select deptno,avg(sal) from emp group by deptno;

    DEPTNO   AVG(SAL)

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

30 1566.66667

20  2175

10 2916.66667

idle> 

分组再排序

idle> select deptno,avg(sal) from emp group by deptno order by deptno ;

    DEPTNO   AVG(SAL)

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

10 2916.66667

20  2175

30 1566.66667

idle>

分组修饰列可以是未选择的列

idle> select avg(sal) from emp group by deptno order by deptno ;

  AVG(SAL)

----------

2916.66667

      2175

1566.66667

idle>

上面执行的分组函数都是对所有记录统计,如果想分组统计(比如统计部门的平均值)需要使用group by 为了限制分组统计的结果需要使用having过滤

GROUP BY 分组统计  9I要排序 10G不排序

求出没个部门的平均工资

idle> select deptno,avg(sal) from emp group by deptno;

    DEPTNO   AVG(SAL)

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

30 1566.66667

20  2175

10 2916.66667

idle> 

分组再排序

idle> select deptno,avg(sal) from emp group by deptno order by deptno ;

    DEPTNO   AVG(SAL)

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

10 2916.66667

20  2175

30 1566.66667

idle>

分组修饰列可以是未选择的列

idle> select avg(sal) from emp group by deptno order by deptno ;

  AVG(SAL)

----------

2916.66667

      2175

1566.66667

idle>

如果在查询中使用了分组函数,任何不在分组函数中的列或表达式必须在group by子句中

因为分组函数是返回一行 而其他列显示多行 显示结果矛盾.

idle> select avg(sal) from emp ;

  AVG(SAL)

----------

2073.21429

idle> select deptno,avg(sal) from emp;

select deptno,avg(sal) from emp

       *

ERROR at line 1:

ORA-00937: not a single-group group function

idle> select deptno,avg(sal) from emp group by deptno ;

    DEPTNO   AVG(SAL)

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

30 1566.66667

20  2175

10 2916.66667

idle> select deptno,avg(sal) from emp group by deptno order by job;

select deptno,avg(sal) from emp group by deptno order by job

                                                         *

ERROR at line 1:

ORA-00979: not a GROUP BY expression

idle> 

group by多条件分组

SCOTT@ora10g> select deptno,job,avg(sal),max(sal) from emp group by deptno,job order by 1;

    DEPTNO JOB        AVG(SAL)   MAX(SAL)

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

10 CLERK    1300       1300

10 MANAGER    2450       2450

10 PRESIDENT    5000       5000

20 ANALYST    3000       3000

20 CLERK     950       1100

20 MANAGER    2975       2975

30 CLERK     950        950

30 MANAGER    2850       2850

30 SALESMAN    1400       1600

9 rows selected.

SCOTT@ora10g>

group by 的过滤

查出平均工资大于2000的部门

idle> select deptno,avg(sal) avg from emp group by deptno where avg >2000;

select deptno,avg(sal) avg from emp group by deptno where avg >2000

                                                    *

ERROR at line 1:

ORA-00933: SQL command not properly ended

group by后不能再接where子句过滤 where过滤只能加到group by前端 这样又不能满足要求

对分组后的过滤要使用having

idle> select deptno,avg(sal) avg from emp group by deptno having avg(sal) >2000;

    DEPTNO   AVG

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

20  2175

10 2916.66667

idle> 

where与having一同使用

idle> select deptno,avg(sal) avg from emp where deptno != 10 group by deptno having avg(sal) >2000;

    DEPTNO   AVG

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

20  2175

idle> 

idle> 

分组函数的嵌套

不嵌套效果

SCOTT@ora10g> select avg(sal),avg(sal) from emp where ename != 'KING' group by job;

  AVG(SAL)   AVG(SAL)

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

    1037.5     1037.5

      1400  1400

2758.33333 2758.33333

      3000  3000

SCOTT@ora10g> 

求每个职位的最高平均工资和最低平均工资

idle> select min(avg(sal)),max(avg(sal)) from emp where ename != 'KING' group by job;

MIN(AVG(SAL)) MAX(AVG(SAL))

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

       1037.5        3000

idle> 

求平均奖金  avg(comm) 即可 因为除了count(*)以外 其他分组函数都不处理空

idle> select avg(comm) from emp;

 AVG(COMM)

----------

       550

idle> 

想求出所有员工的平均 需要使用nvl

idle> select avg(nvl(comm,0)) from emp;

AVG(NVL(COMM,0))

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

      157.142857

idle> 

分组函数的注意事项:

1.分组函数只能出现在select,order by,having子句中

2.分组函数会忽略NULL 除了count(*)

3.分组函数中可以使用ALL或distinct;ALL是默认值,统计所有.加上distinct则只统计不同

4.如果选择的列里有普通列,表达式和分组列 那么普通列和表达式都必须出现在group by中

select min(deptno),max(sal+1),count(*) from emp ;

如下操作:得到t5表中有部分行是重复的,找出重复的行

SQL> create table t5 as select * from emp;

Table created.

SQL> insert into t5 select * from emp where deptno=20;

5 rows created.

SQL> commit;

Commit complete.

SQL>

 

行转列

create table t4(id int,name varchar2(10),subject varchar2(20),grade number);

insert into t4 values(1,'ZORRO','语文',70);

insert into t4 values(2,'ZORRO','数学',80);

insert into t4 values(3,'ZORRO','英语',75);

insert into t4 values(4,'SEKER','语文',65);

insert into t4 values(5,'SEKER','数学',75);

insert into t4 values(6,'SEKER','英语',60);

insert into t4 values(7,'BLUES','语文',60);

insert into t4 values(8,'BLUES','数学',90);

insert into t4 values(9,'PG','数学',80);

insert into t4 values(10,'PG','英语',90);

commit;

SQL> select * from t4;

ID NAME       SUBOBJECT   GRADE

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

         1 ZORRO      语文                         70

         2 ZORRO      数学                         80

         3 ZORRO      英语                         75

         4 SEKER      语文                         65

         5 SEKER      数学                         75

         6 SEKER      英语                         60

         7 BLUES      语文                         60

         8 BLUES      数学                         90

         9 PG         数学                         80

        10 PG         英语                         90

10 rows selected.

SQL> 

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

| name  | 语文 | 数学| 英语|

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

| zorro |  70  | 80  | 75  |

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

| seker |  65  | 75  | 60  |

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

| blues |  60  | 90  | 0   |

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

| PG    |  0   | 80  | 90  |

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

SQL> select name,sum(case when SUBJECT='语文' then GRADE else 0 end) "语文",sum(case when SUBOBJECT='数学' then GRADE else 0 end) "数学",sum(case when SUBJECT='英语' then GRADE else 0 end) "英语" from t4 group by name;

NAME             语文       数学       英语

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

SEKER    65       75  60

BLUES    60       90   0

PG     0       80  90

ZORRO    70       80  75

SQL> 

  1  select name,

  2     max(decode(SUBJECT,'语文',GRADE,0)) "语文" ,

  3     max(decode(SUBJECT,'数学',GRADE,0)) "数学" ,

  4     max(decode(SUBJECT,'英语',GRADE,0)) "英语"

  5* from t4 group by name

SCOTT@orasid> /

NAME                   语文       数学       英语

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

SEKER                    65         75         60

BLUES                    60         90          0

PG                        0         80         90

ZORRO                    70         80         75

SCOTT@orasid>