|
已选择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行。
|