Oracle: 对数据分组（max，min，avg，sum等）的想法

1. 分组函数只能出现在选择列表（select），having，order by 子句中；

2.如果在select语句中同时包含有group by，having，order by，那么，他们的优先级是，group by，having和order by

3.在选择列种，如果有列，表达式和分组函数，那么，这些列和表达式，必须有一个出现在group by子句中！

EMPNO ENAME      JOB         MGR HIREDATE          SAL      COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------
 7698 BLAKE      MANAGER    7839 1981/5/1      2850.00               30
 7499 ALLEN      SALESMAN   7698 1981/2/20     1600.00    300.00     30
 7844 TURNER     SALESMAN   7698 1981/9/8      1500.00      0.00     30
 7654 MARTIN     SALESMAN   7698 1981/9/28     1250.00   1400.00     30
 7521 WARD       SALESMAN   7698 1981/2/22     1250.00    500.00     30
 7900 JAMES      CLERK      7698 1981/12/3      950.00               30
 7788 SCOTT      ANALYST    7566 1987/4/19     3000.00               20
 7902 FORD       ANALYST    7566 1981/12/3     3000.00               20
 7566 JONES      MANAGER    7839 1981/4/2      2975.00               20
 7876 ADAMS      CLERK      7788 1987/5/23     1100.00               20
 7370 Jonny      clerk      7902 1999/11/11    1000.00               20
 7369 SMITH      CLERK      7902 1980/12/17     800.00               20
 7839 KING       PRESIDENT       1981/11/17    5000.00               10
 7782 CLARK      MANAGER    7839 1981/6/9      2450.00               10
 7934 MILLER     CLERK      7782 1982/1/23     1300.00               10

</pre><pre name="code" class="sql">//以年薪排序
SQL> select empno,ename,job, sal,comm,  sal*12+nvl(comm,0)*12 "年薪"from emp order by "年薪" desc;
EMPNO ENAME      JOB             SAL      COMM         年薪
----- ---------- --------- --------- --------- ----------
 7839 KING       PRESIDENT   5000.00                60000
 7902 FORD       ANALYST     3000.00                36000
 7788 SCOTT      ANALYST     3000.00                36000
 7566 JONES      MANAGER     2975.00                35700
 7698 BLAKE      MANAGER     2850.00                34200
 7654 MARTIN     SALESMAN    1250.00   1400.00      31800
 7782 CLARK      MANAGER     2450.00                29400
 7499 ALLEN      SALESMAN    1600.00    300.00      22800
 7521 WARD       SALESMAN    1250.00    500.00      21000
 7844 TURNER     SALESMAN    1500.00      0.00      18000
 7934 MILLER     CLERK       1300.00                15600
 7876 ADAMS      CLERK       1100.00                13200
 7370 Jonny      clerk       1000.00                12000
 7900 JAMES      CLERK        950.00                11400
 7369 SMITH      CLERK        800.00                 9600

//数据分组
SQL> select max(sal), min(sal) from emp;
  MAX(SAL)   MIN(SAL)
---------- ----------
      5000        800

//针对第一条的范例
SQL> select * from emp where sal=(select max(sal) from emp);
EMPNO ENAME      JOB         MGR HIREDATE          SAL      COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------
 7839 KING       PRESIDENT       1981/11/17    5000.00               10
Executed in 0.03 seconds

SQL> select * from emp where sal=(select max(sal) from emp) or sal = (select min(sal) from emp);
EMPNO ENAME      JOB         MGR HIREDATE          SAL      COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------
 7369 SMITH      CLERK      7902 1980/12/17     800.00               20
 7839 KING       PRESIDENT       1981/11/17    5000.00               10
Executed in 0.035 seconds

//针对【三】的范例
SQL> select avg(sal),max(sal), deptno from emp group by deptno;
  AVG(SAL)   MAX(SAL) DEPTNO
---------- ---------- ------
1566.66666       2850     30
1979.16666       3000     20
2916.66666       5000     10
Executed in 0.051 seconds

//having 的用法
SQL> select avg(sal),deptno from emp group by deptno having avg(sal)<2000;
  AVG(SAL) DEPTNO
---------- ------
1566.66666     30
1979.16666     20
Executed in 0.039 seconds

//语句的集合
SQL> select avg(sal),max(sal),deptno from emp
  2  group by deptno
  3  having avg(sal)>1800
  4  order by avg(sal) desc; 
  AVG(SAL)   MAX(SAL) DEPTNO
---------- ---------- ------
2916.66666       5000     10
1979.16666       3000     20
Executed in 0.036 seconds

//group by 后跟表达式
SQL> select ename,job,sal*12+nvl(comm,0)*12 as "Subtotal Sal",avg(sal),max(sal) from emp
  2  group by ename,job,sal*12+nvl(comm,0)*12;
ENAME      JOB       Subtotal Sal   AVG(SAL)   MAX(SAL)
---------- --------- ------------ ---------- ----------
JONES      MANAGER          35700       2975       2975
ADAMS      CLERK            13200       1100       1100
JAMES      CLERK            11400        950        950
Jonny      clerk            12000       1000       1000
CLARK      MANAGER          29400       2450       2450
SMITH      CLERK             9600        800        800
TURNER     SALESMAN         18000       1500       1500
FORD       ANALYST          36000       3000       3000
MARTIN     SALESMAN         31800       1250       1250
ALLEN      SALESMAN         22800       1600       1600
BLAKE      MANAGER          34200       2850       2850
KING       PRESIDENT        60000       5000       5000
MILLER     CLERK            15600       1300       1300
WARD       SALESMAN         21000       1250       1250
SCOTT      ANALYST          36000       3000       3000
15 rows selected
Executed in 0.123 seconds

<pre name="code" class="sql">
//全套
SQL> select ename,job,sal*12+nvl(comm,0)*12 as "Subtotal Sal",avg(sal),max(sal) from emp
  2  group by ename,job,sal*12+nvl(comm,0)*12
  3  having avg(sal)> 1000
  4  order by "Subtotal Sal" desc;
ENAME      JOB       Subtotal Sal   AVG(SAL)   MAX(SAL)
---------- --------- ------------ ---------- ----------
KING       PRESIDENT        60000       5000       5000
FORD       ANALYST          36000       3000       3000
SCOTT      ANALYST          36000       3000       3000
JONES      MANAGER          35700       2975       2975
BLAKE      MANAGER          34200       2850       2850
MARTIN     SALESMAN         31800       1250       1250
CLARK      MANAGER          29400       2450       2450
ALLEN      SALESMAN         22800       1600       1600
WARD       SALESMAN         21000       1250       1250
TURNER     SALESMAN         18000       1500       1500
MILLER     CLERK            15600       1300       1300
ADAMS      CLERK            13200       1100       1100
12 rows selected
Executed in 0.098 seconds