SQL之数字操作

--注：
--   以下程序均可在mysql上正常运行，不保证在其他数据库中运行的正确性

--求平均 avg （会忽略NULL值）
select deptno, avg(sal) as avg_salary from emp group by deptno;

--求最大最小值 min max
select min(sal) as min_sal, max(sal) as max_sal from emp;

--求每个部门的最大最小薪水值
select deptno, min(sal) as min_sal, max(sal) as max_sal from emp group by deptno;

--求和 sum
select sum(sal) as total_sal from emp;
select deptno, sum(sal) as total_dept from emp group by deptno;

--求一个表的行数
select count(*) from emp;
select deptno, count(*) as emp_dept from emp group by deptno;

--求某列的个数
select count(comm) from emp;

--生成累计和（使用标量子查询）
select e.ename, e.sal, (select sum(d.sal) from emp d where d.empno<=e.empno) as running_total from emp e order by running_total;

--生成累乘积: 使用exp(sum(ln(sal)))实现
select exp(sum(ln(deptno))) from dept;

--求出现最频繁的值
select sal from emp where deptno=20 group by sal having count(*)>=all(select count(*) from emp where deptno=20 group by sal);