华南俳烁实业有限公司

分組函數(shù)計算

count

--求某列的最大值

select max(salary) from emp_ning;

select max(s1) from ielts_ning;

select max(calculate_ning((s1+s2+s3+s4)/4))

from ielts_ning;

select min(salary) from emp_ning

select min(s1) from ielts_ning;

--組函數(shù)忽略空值

select avg(nvl(salary, 0))from emp_ning;

select sum(salary) / count(salary)from emp_ning;

count / sum / avg / max / min

 select deptno, count(*) from emp_ning where deptno is not null group by deptno;

--沒有g(shù)roup by短語,語法錯誤

 select deptno, count(*) from emp_ning where deptno is not null;

--沒有語法錯誤,信息不全

 select count(*) from emp_ning where deptno is not null group by deptno;

--查詢各個部門中的最多人數(shù)

 select max(count(*)) from emp_ning where deptno is not null group by deptno;

--哪個部門的人數(shù)最多?

select deptno, count(*) from emp_ning where deptno is not null group by deptno having count(*) = 4;

--查找人數(shù)最多的部門號

--10  6

select deptno, count(*) from emp_ning where deptno is not null group by deptno

having count(*) = ( select max(count(*)) from emp_ning where deptno is not null group by deptno);

--人數(shù)最多的部門的名字和工作地點

select dname, location from dept_ning where deptno = 10;

select dname, location from dept_ning

where deptno = ( select deptno, count(*) from emp_ning where deptno is not null group by deptno

         having count(*) = ( select max(count(*)) from emp_ning where deptno is not null group by deptno));

select 列, 組函數(shù)

from 表

where 條件

group by ...

having...

order by...

--表里沒有的數(shù)據(jù),需要計算的數(shù)據(jù)做條件,用having

--表里有的數(shù)據(jù), 做條件,用where

--所有的組函數(shù)做條件,必須用having

10   10000

20   5000

30   8000

7000

--哪些部門的平均工資比整個機構(gòu)的平均工資高?

--查詢的條件是平均工資,是組函數(shù)計算出來的結(jié)--果,所以使用having, 而不是where

select deptno, avg(nvl(salary,0)) from emp_ning group by deptno having avg(nvl(salary,0)) > (

select avg(nvl(salary,0)) from emp_ning);

--哪個部門的人數(shù)超過5個人?

select deptno, count(*) from emp_ning group by deptno having count(*) > 5;

--哪個部門的薪水總和比部門20的薪水總和高?

select deptno, sum(salary) from emp_ning group by deptno

having sum(salary) > ( select sum(salary) from emp_ning where deptno = 20);

name job     salary

a   android  8000

b   java     5000

c   c++      6000

d   java     10000

e   android  9000

....

select job, avg(salary) from mytable group by job;