数据库查询语句2

一聚组函数的使用

聚组函数是从一组中返回汇总信息，聚组函数有SUM COUNT COUNT DISTINCT MAX MIN AVG SUM等

1 计算max ,min,avg,sum

select min(sal),max(sal),avg(sal),sum(sal) from emp;

select ename,job,sal from emp where sal=(select max(sal) from emp);

2 count 与 count distinct

select count(*) from emp;

select count(job) from emp;

select  count(distinct job) from emp;

注意select 字句中的目标要一致性

不能把单行函数与聚组函数混合使用

select ename,sum(sal) from emp; 错误语法

3使用 group by分组

select job,sum(sal) from emp group by job;

select jbo,count(*) from emp group by job;

select deptno,sum(sal),max(sal) from emp group by deptno;

4多条件分组

select deptno,job,count(*) from emp group by deptno,job;

5 使用having 选择满足条件

select deptno,sum(sal) from emp group by deptno having sum(sal)>8000;

查询语句的总结

select 列1,列2 from 基表 where 表达样条件 group by 分组列,分组列2 having 分组条件表达式 group by 排序列1 排序列2

select deptno,sum(sal) from emp where job!='CLERK' group by deptno having sum(sal)>3000 order by sum(sal);