1.知識點：可以對照下面的錄屏進行閱讀
SQL> --組函數類型：avg，count，max，min，sum SQL> --工資總額 SQL> select sum(sal) from emp; SQL> --員工人數 SQL> select count(*) from emp; SQL> --平均工資 SQL> select sum(sal)/count(*) 一, avg(sal) 二 from emp; SQL> --平均獎金的三種方式：二三方法一樣，一方法不一樣； SQL> select sum(comm)/count(*) 一, sum(comm)/count(comm) 二, avg(comm) 三 from emp; SQL> --造成一方法和二三方法結果不同的原因：空值 4. 組函數會自動濾空，只統計不為空 SQL> select count(*),count(comm) from emp; --結果不同，count(comm)只統計comm不為空的個數 SQL> --可以在組函數中嵌套濾空函數，來屏蔽組函數的濾空功能 SQL> select count(*),count(nvl(comm,0)) from emp; --結果相同，count(comm)統計所有結果 SQL> --求各個部門的平均工資 SQL> select deptno,avg(sal) 2 from emp 3 group by deptno; SQL> --語法 SQL> --在select列表中所有未包含在組函數中的列都應該包含在group by子句中 SQL> --包含在group by子句中的列不必包含在select列表中 SQL> --按部門，不同的職位統計平均工資 SQL> select deptno,job,avg(sal) 2 from emp 3 group by deptno,job 4 order by 1 SQL> --group by多列：先按照第一列分；如果第一列相同，再按照第二列分 SQL> --having：過濾分組 SQL> --查詢平均工資大於2000的部門 SQL>select deptno,avg(sal) 2 from emp 3 group by deptno 4 having avg(sal)>2000 SQL> --having和where的區別 SQL> --求10號部門的平均工資 SQL> select deptno,avg(sal) 2 from emp 3 group by deptno 4 having deptno=10; SQL> --SQL優化 3. 盡量使用where，例外：如果條件中含有組函數，只能使用having SQL> --group by的增強：做部門報表可能用到 SQL> -- group by deptno,job + group by deptno + group by null = group by rollup(deptno,job) SQL> -- group by rollup(a,b) = group by a,b + group by a + group by null SQL> select deptno,job,sum(sal) 2 from emp 3 group by rollup(deptno,job); SQL> --SQLPLUS支持報表功能 SQL> break on deptno skip 2 --break on deptno：按照部門號分段，相同的部門號只顯示一次；skip 2：不同的部門之間空2行
2.在Sqlplus下實際執行的結果錄屏：
SQL> host cls
SQL> --工資總額
SQL> select sum(sal) from emp;
SUM(SAL)
----------
29025
SQL> --員工人數
SQL> select count(*) from emp;
COUNT(*)
----------
14
SQL> --平均工資
SQL> select sum(sal)/count(*) 一, avg(sal) 二 from emp;
一 二
---------- ----------
2073.21429 2073.21429
SQL> --平均獎金
SQL> select sum(comm)/count(*) 一, sum(comm)/count(comm) 二, avg(comm) 三 from emp;
一 二 三
---------- ---------- ----------
157.142857 550 550
SQL> --空值 4. 組函數會自動濾空
SQL> select count(*),count(comm) from emp;
COUNT(*) COUNT(COMM)
---------- -----------
14 4
SQL> select count(*),count(nvl(comm,0)) from emp;
COUNT(*) COUNT(NVL(COMM,0))
---------- ------------------
14 14
SQL> --可以在組函數中嵌套濾空函數，來屏蔽組函數的濾空功能
SQL> host cls
SQL> --求各個部門的平均工資
SQL> set linesize 150
SQL> col sal for 9999
SQL> select * from emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- -------------- ----- ---------- ----------
7369 SMITH CLERK 7902 17-12月-80 800 20
7499 ALLEN SALESMAN 7698 20-2月 -81 1600 300 30
7521 WARD SALESMAN 7698 22-2月 -81 1250 500 30
7566 JONES MANAGER 7839 02-4月 -81 2975 20
7654 MARTIN SALESMAN 7698 28-9月 -81 1250 1400 30
7698 BLAKE MANAGER 7839 01-5月 -81 2850 30
7782 CLARK MANAGER 7839 09-6月 -81 2450 10
7788 SCOTT ANALYST 7566 13-7月 -87 3000 20
7839 KING PRESIDENT 17-11月-81 5000 10
7844 TURNER SALESMAN 7698 08-9月 -81 1500 0 30
7876 ADAMS CLERK 7788 13-7月 -87 1100 20
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- -------------- ----- ---------- ----------
7900 JAMES CLERK 7698 03-12月-81 950 30
7902 FORD ANALYST 7566 03-12月-81 3000 20
7934 MILLER CLERK 7782 23-1月 -82 1300 10
已選擇14行。
SQL> select deptno,avg(sal)
2 from emp
3 group by depnto;
group by depnto
*
第 3 行出現錯誤:
ORA-00904: "DEPNTO": 標識符無效
SQL> ed
已寫入 file afiedt.buf
1 select deptno,avg(sal)
2 from emp
3* group by deptno
SQL> /
DEPTNO AVG(SAL)
---------- ----------
30 1566.66667
20 2175
10 2916.66667
SQL> --語法
SQL> select deptno,job,avg(sal)
2 from emp
3 group by depnto;
group by depnto
*
第 3 行出現錯誤:
ORA-00904: "DEPNTO": 標識符無效
SQL> ed
已寫入 file afiedt.buf
1 select deptno,job,avg(sal)
2 from emp
3* group by deptno
SQL> /
select deptno,job,avg(sal)
*
第 1 行出現錯誤:
ORA-00979: 不是 GROUP BY 表達式
SQL> ed
已寫入 file afiedt.buf
1 select deptno,job,avg(sal)
2 from emp
3* group by deptno,job
SQL> --group by多列：先按照第一列分；如果第一列相同，再按照第二列分
SQL> ed
已寫入 file afiedt.buf
1 select deptno,job,avg(sal)
2 from emp
3 group by deptno,job
4* order by 1
SQL> /
DEPTNO JOB AVG(SAL)
---------- --------- ----------
10 CLERK 1300
10 MANAGER 2450
10 PRESIDENT 5000
20 ANALYST 3000
20 CLERK 950
20 MANAGER 2975
30 CLERK 950
30 MANAGER 2850
30 SALESMAN 1400
已選擇9行。
SQL> --按部門，不同的職位統計平均工資
SQL> host cls
SQL> --having
SQL> select deptno,avg(sal)
2 from emp
3 group by depnto;
group by depnto
*
第 3 行出現錯誤:
ORA-00904: "DEPNTO": 標識符無效
SQL> ed
已寫入 file afiedt.buf
1 select deptno,avg(sal)
2 from emp
3* group by deptno
SQL> /
DEPTNO AVG(SAL)
---------- ----------
30 1566.66667
20 2175
10 2916.66667
SQL> --查詢平均工資大於2000的部門
SQL> ed
已寫入 file afiedt.buf
1 select deptno,avg(sal)
2 from emp
3 group by deptno
4* having avg(sal)>2000
SQL> /
DEPTNO AVG(SAL)
---------- ----------
20 2175
10 2916.66667
SQL> --having 過濾分組
SQL> host cls
SQL> --having和where的區別
SQL> --求10號部門的平均工資
SQL> select deptno,avg(sal)
2 from emp
3 group by deptno
4 having deptno=10;
DEPTNO AVG(SAL)
---------- ----------
10 2916.66667
SQL> select deptno,avg(sal)
2 from emp
3 where deptno=10
4 group by deptno;
DEPTNO AVG(SAL)
---------- ----------
10 2916.66667
SQL> --SQL優化 3. 盡量使用where
SQL> 例外：如果條件中含義組函數，只能使用having
SP2-0734: 未知的命令開頭 "例外：如果..." - 忽略了剩余的行。
SQL> -- 例外：如果條件中含義組函數，只能使用having
SQL> host cls
SQL> --group by的增強
SQL> /*
SQL> group by deptno,job
SQL> +
SQL> group by deptno
SQL> +
SQL> group by null
SQL>
SQL> =
SQL>
SQL> group by rollup(deptno,job)
SQL>
SQL> group by rollup(a,b)
SQL> =
SQL> group by a,b
SQL> +
SQL> group by a
SQL> +
SQL> group by null
SQL>
SQL> */
SQL> select deptno,job,sum(sal)
2 from emp
3 group by rollup(deptno,job);
DEPTNO JOB SUM(SAL)
---------- --------- ----------
10 CLERK 1300
10 MANAGER 2450
10 PRESIDENT 5000
10 8750
20 CLERK 1900
20 ANALYST 6000
20 MANAGER 2975
20 10875
30 CLERK 950
30 MANAGER 2850
30 SALESMAN 5600
DEPTNO JOB SUM(SAL)
---------- --------- ----------
30 9400
29025
已選擇13行。
SQL> --SQLPLUS支持報表功能
SQL> break on deptno skip 2
SQL> select deptno,job,sum(sal)
2 from emp
3 group by rollup(deptno,job);
DEPTNO JOB SUM(SAL)
---------- --------- ----------
10 CLERK 1300
MANAGER 2450
PRESIDENT 5000
8750
20 CLERK 1900
ANALYST 6000
MANAGER 2975
10875
DEPTNO JOB SUM(SAL)
---------- --------- ----------
30 CLERK 950
MANAGER 2850
SALESMAN 5600
9400
29025
已選擇13行。
SQL> break on null
SQL> /
DEPTNO JOB SUM(SAL)
---------- --------- ----------
10 CLERK 1300
10 MANAGER 2450
10 PRESIDENT 5000
10 8750
20 CLERK 1900
20 ANALYST 6000
20 MANAGER 2975
20 10875
30 CLERK 950
30 MANAGER 2850
30 SALESMAN 5600
DEPTNO JOB SUM(SAL)
---------- --------- ----------
30 9400
29025
已選擇13行。
SQL> spool off
