Oracle之数据操作__分组统计查询

 一. 统计函数

      

1. COUNT(*)，COUNT(字段)，COUNT(DISTNCT 字段)

SELECT COUNT(*),COUNT(ename),COUNT(comm),COUNT(DISTINCT job) FROM emp;

                   

#在使用COUNT(字段)的时候，如果列存在了 null，那么null是不会进行统计的。如果使用的DISTINCT，那么列上有重复的，重复的记录也不统计。使用COUNT(*)，最方便。
不过，COUNT(字段),这个字段最好是一个 不是null的进行统计，例如：did,empno

#在所有的统计函数，只有COUNT()函数可以在表中没有任何数据的时候，依然返回内容

2.

SELECT * FROM bonus;
SELECT count(*) FROM bonus;

        

SELECT count(ename),avg(sal),sum(sal),max(sal),min(sal) FROM bonus;

         

二. 单字段分组统计

          

 1. 统计每种职位的最低工资和最高工资

SELECT job,MIN(sal),MAX(sal) FROM EMP GROUP BY job;

2. 分组注意事项

    

#注意事项一
SELECT did,count(empno) FROM emp;
SELECT count(empno) FROM emp; #正确

#注意事项二
SELECT did,ename,count(empno) FROM emp GROUP BY did;
SELECT did,count(empno) FROM emp GROUP BY did;   #正确

#注意事项三
SELECT did,max(avg(sal)) FROM emp GROUP BY did;
SELECT max(avg(sal)) FROM emp GROUP BY did;    #正确

 3. 查询每个部门的名称，部门人数，部门平均工资，平均服务年限

SELECT d.dname,count(e.did),round(avg(e.sal),2),
     round(avg(months_between(sysdate,e.hiredate)/12),2) avgyear
FROM emp e,dept d
where e.did(+)=d.did
GROUP BY d.dname;

            

 ### 子句顺序：FROM, WHERE, GROUP BY, SELECT, ORDER BY

4. 查询出公司各个工资等级雇员的数量，平均工资

4.1 首先：

SELECT s.grade,e.empno,e.sal
FROM salgrade s,emp e
WHERE e.sal BETWEEN s.losal AND s.hisal;

         #GRADE 有重复

4.2 最后结果：

SELECT s.grade,count(e.empno),round(avg(e.sal),2)
FROM salgrade s,emp e
WHERE e.sal BETWEEN s.losal AND s.hisal
GROUP BY s.grade;

 5. 统计出领取佣金与不领取佣金的雇员的 平均工资，平均雇佣年限，雇员人数

SELECT '不领取佣金',round(avg(sal),2) avgsal,
    round(avg(months_between(sysdate,hiredate)/12),2) avgyear,
    count(empno) 人数 FROM emp WHERE comm IS NOT NULL
    UNION
SELECT '领取佣金',ROUND(avg(sal),2) avgsal,
    round(avg(months_between(sysdate,hiredate)/12),2) avgyear,
    count(empno) 人数 FROM emp WHERE comm IS NULL

            

三.多字段分组统计

                  

1. 要求查询出每个部门的详细信息

SELECT d.did,d.dname,d.loc,
         NVL(count(e.empno),0) count,NVL(round(avg(sal),2),0) avg,
         NVL(sum(sal),0) sum,NVL(max(sal),0) max,NVL(min(sal),0) min
FROM emp e,dept d
WHERE e.did(+)=d.did
GROUP BY d.did,d.dname,d.loc ORDER BY d.did ;

                      

四. HAVING子句

 A. HAVING子句的使用

                     

　　　　　　　　###     HAVING子句一定要与GROUP BY子句使用

1. 查询出所有平均工资大于1000的职位信息，平均工资，雇员人数

SELECT job,round(avg(sal),2),count(did)
FROM emp
GROUP BY job
HAVING avg(sal)>1000;

           

 ##完整的子句语法：FROM, WHERE, GROUP BY, HAVING, SELECT, ORDER BY

 2. 列出至少有一个员工的所有部门编号，名称，并统计出这些部门的员工的平均工资，最低工资，最高工资，最后按照部门id,升序排列。

SELECT d.did,d.dname,d.loc,ROUND(avg(e.sal),2),MIN(e.sal),MAX(e.sal)
FROM emp e,dept d
WHERE e.did(+)=d.did
GROUP BY d.did,d.dname,d.loc
HAVING count(e.did)>1 
ORDER BY d.did;

                

B. HAVING与WHERE子句的区别

语句执行流程：

       

 HAVING与WHERE子句的区别描述：

# WHERE:是在分组之前使用（可以没有GROUP BY）,不允许使用统计函数


# HAVING:是在分组之后（必须结合 GROUP BY），允许使用统计函数

 1. 显示非销售人员工作名称以及从事同一工作雇员的月工资的总和，并且要满足从事同一工作的雇员的月工资合计大于$2000输出结果按月工资的合计降序排列

SELECT job,SUM(sal) sum
FROM emp
WHERE job<>'CLERK'
GROUP BY job
HAVING sum(sal)>2000
ORDER BY sum DESC;

                       

                    

#问题

1. ORA-02270: 此列列表的唯一关键字或主键不匹配

　　1、可能是字段类型不匹配，请先仔细检查，确定无误。
　　2、外键关联的必须是主键！！！