Oracle: 对数据分组（max，min，avg，sum等）的想法
来源：互联网 发布：深圳华夏软件学校骗 编辑：程序博客网 时间：2024/06/05 11:50
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 10Executed 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 10Executed in 0.035 seconds
//针对【三】的范例SQL> select avg(sal),max(sal), deptno from emp group by deptno; AVG(SAL) MAX(SAL) DEPTNO---------- ---------- ------1566.66666 2850 301979.16666 3000 202916.66666 5000 10Executed in 0.051 seconds
//having 的用法SQL> select avg(sal),deptno from emp group by deptno having avg(sal)<2000; AVG(SAL) DEPTNO---------- ------1566.66666 301979.16666 20Executed 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 101979.16666 3000 20Executed 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 2975ADAMS CLERK 13200 1100 1100JAMES CLERK 11400 950 950Jonny clerk 12000 1000 1000CLARK MANAGER 29400 2450 2450SMITH CLERK 9600 800 800TURNER SALESMAN 18000 1500 1500FORD ANALYST 36000 3000 3000MARTIN SALESMAN 31800 1250 1250ALLEN SALESMAN 22800 1600 1600BLAKE MANAGER 34200 2850 2850KING PRESIDENT 60000 5000 5000MILLER CLERK 15600 1300 1300WARD SALESMAN 21000 1250 1250SCOTT ANALYST 36000 3000 300015 rows selectedExecuted 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 5000FORD ANALYST 36000 3000 3000SCOTT ANALYST 36000 3000 3000JONES MANAGER 35700 2975 2975BLAKE MANAGER 34200 2850 2850MARTIN SALESMAN 31800 1250 1250CLARK MANAGER 29400 2450 2450ALLEN SALESMAN 22800 1600 1600WARD SALESMAN 21000 1250 1250TURNER SALESMAN 18000 1500 1500MILLER CLERK 15600 1300 1300ADAMS CLERK 13200 1100 110012 rows selectedExecuted in 0.098 seconds
0 0
- Oracle: 对数据分组（max，min，avg，sum等）的想法
- 6.组函数（avg(),sum(),max(),min(),count()）、多行函数，分组数据（group by，求各部门的平均工资），分组过滤(having和where)，sql优化
- 组函数（avg(),sum(),max(),min(),count()）、多行函数，分组数据（group by，求各部门的平均工资），分组过滤(having和where)，sql优化 .
- mongodb 中max、min、sum、avg等函数用法
- mysql中group by子句和聚合函数MAX(),MIN(),SUM(),AVG()等的使用
- oracle中的聚合函数count、max、min、sum、avg等等
- MySQL之汇总数据（AVG,COUNT,MAX,MIN,SUM)
- oracle中的聚合函数count、max、min、sum、avg以及NVL函数的用法
- group by与avg(),max(),min(),sum()函数的关系
- Oracle中有关Group by 中avg();sum();min();max();count();的运用整理(Oracle的执行顺序)
- Oracle中有关Group by 中avg();sum();min();max();count();的运用整理(Oracle的执行顺序)
- SQL中count()/max() /min()/sum()avg()/sum()等函数用法
- Mysql对检索结果进行汇总COUNT,AVG,MIN,MAX,SUM
- 聚合函数（sum，count，max，avg等）ZT
- Oracle分析函数三——SUM,AVG,MIN,MAX,COUNT
- Oracle分析函数三——SUM,AVG,MIN,MAX,COUNT
- Oracle分析函数三——SUM,AVG,MIN,MAX,COUNT
- Oracle分析函数三——SUM,AVG,MIN,MAX,COUNT
- 问题集合
- View类的XML属性、相关方法及说明
- 获取 properties的值
- LeetCode 6. ZigZag Conversion 详细解析
- oracle
- Oracle: 对数据分组（max，min，avg，sum等）的想法
- 泛型<T> T
- 开源图表库MPAndroidChart之饼状图和柱状图
- sql教程
- js中(function(){})()的用法
- 添物不花钱学计算机及编程（预备篇）— 总述
- wampserver中Apache配置虚拟主机方法
- js中用参数传递方法名
- C#学习笔记之条形码和二维码
