oracle强化练习之分组函数，oracle分组函数
1. 显示平均工资为>2000的职位
select job,avg(sal) from emp group by job having avg(sal)>2500;
2. 计算工资在2000以上，各种职位的平均工资大于3000的职位及平均工资
Select job,avg(sal) From emp where sal>2000 group by job having avg(sal)>3000;
3. 找每个部门的最高和最低的工资
Select deptno,max(sal),min(sal) From emp group by deptno;
4. 找每个部门中每种职位的最高和最低的工资
select deptno,max(sal),min(sal) from emp group by job,deptno;
select deptno,job,max(sal),min(sal) from emp where job is notnull group by job,deptno;
5. 显示出工作名称(job)中包含"MAN"的员工平均工资，最高工资，最低工资及工资的和
select avg(sal),max(sal),min(sal),sum(sal) from emp where job like '%MAN%';
6. 显示出20号部门的员工人数
select count(*) from emp where deptno=20;
7. 显示出平均工资大于2000的部门名称及平均工资
Select dname,avg(sal) From dept d,emp e where e.deptno=d.deptno group by dname having avg(sal)>2000;
8. 显示每个部门每种工作平均工资大于2500的部门及工作
Select deptno,job From emp group by deptno,job having avg(sal)>2500;
9. 显示出工作名称中包含"MAN"，并且平均工资大于1000的工作名称及平均工资
Select job,avg(sal) From emp group by job having avg(sal)>1000 and job like’%MAN%’;
10. 显示出平均工资最高的的部门平均工资
11. 列出最低工资大于1500的各种工作
Select job From emp group by job having min(sal)>1500;
12. 列出各部门的员工数量及平均工作年限
Select deptno,count(*),avg((sysdate-hiredate)/365) From emp group by deptno;
Select deptno,avg((months_between(sysdate,hiredate))/12) from emp group by deptno;
SELECT *
FROM dept_costs
WHERE dept_total > (SELECT dept_avg
FROM avg_cost)
ORDER BY department_name;
GROUP BY 扩展
带有ROLLUP和CUBE操作的GROUP BY子句
- 使用带有ROLLUP和CUBE操作的GROUP BY子句产生多种分组结果
- ROLLUP产生n + 1种分组结果，其是对GROUP BY子句的扩展
- CUBE产生2的n次方种分组结果，其是对GROUP BY子句的扩展
注：其中的n指的是group_by_expression的数目。
ROLLUP操作符 ：ROLLUP产生n + 1种分组结果，顺序是从右向左
SELECT [column,] group_function(column). . .
FROM table
[WHERE condition]
[GROUP BY [ROLLUP] group_by_expression]
[HAVING having_expression]
[ORDER BY column];
CUBE操作符：CUBE会产生类似于笛卡尔集的分组结果
SELECT [column,] group_function(column). . .
FROM table
[WHERE condition]
[GROUP BY [CUBE] group_by_expression]
[HAVING having_expression]
[ORDER BY column];
GROUPING函数：能够实现更加直观的分组结果显示提示
SELECT [column,] group_function(column) . , [GROUPING(group_by_expression)]..
FROM table
[WHERE condition]
[GROUP BY [ROLLUP] [CUBE] group_by_expression]
[HAVING having_expression]
[ORDER BY column];
- GROUPING函数可以和CUBE或ROLLUP结合使用
- 使用GROUPING函数，可以找到哪些列在该行中参加了分组
- 使用GROUPING函数, 可以区分空值产生的原因
- GROUPING函数返回0 或1
GROUPING SETS：
- GROUPING SETS是对GROUP BY子句的进一步扩充
- 使用GROUPING SETS可以实现在同一个查询中定义多个分组集
- Oracle 对GROUPING SETS子句指定的分组集进行分组后用UNION ALL操作将各分组结果结合起来
- Grouping set 的优点：
– 只进行一次分组即可
– 不必书写复杂的UNION语句
– GROUPING SETS中包含的分组项越多性能越好
以下例子实现了对department_id, job_id分组，对job_id, manager_id分组，最终形成两个分组：
SELECT department_id, job_id, manager_id, avg(salary......余下全文>>
常用的有：
COUNT() 返回查寻的行数
例如：select count(*) from table;
MAX() 返回表达式的最大值
例如：select a, max(b) from table group by a;
MIN() 返回表达式的最小值
例如：select a, min(b) from table group by a;
SUM() 返回表达式的总合
例如：select a, sum(b) from table group by a;
AVG() 返回表达式的平均值
例如：select a, avg(b) from table group by a;
此外还有分析函数over，是用来处理复杂sql的，这个涉及到的东西就有很多了。一两句话说不清楚，如果有关于这方面的问题，可以发求助或追问。