开发工具与关键技术:PLSQL Developer、Oracle、SQL*plus
(一)在Oracle查询语句中使用group by 子句可对表格数据进行分组;
(二)在分组后的数据中不可通过where子句进行过滤,须使用having子句。
组函数类型表:
在select 列表中所有未包含在组函数中的列都应该包含在 group by 子句中。
以下为分组查询:
(1)在employees(员工表)中查询公司各个部门在1995-1998年之间,每年雇用的人数:
select department_id as 部门号,to_char(hire_date,‘yyyy’) as 年份, count(department_id)
from employees
where to_char(hire_date,‘yyyy’) in (1995,1996,1997,1998)
group by to_char(hire_date,‘yyyy’),department_id
代码执行结果:
该查询的错误示范(红色标注):
select department_id as 部门号,to_char(hire_date,‘yyyy’) as 年份, count(department_id)
from employees
group by to_char(hire_date,‘yyyy’),department_id
where to_char(hire_date,‘yyyy’) in (1995,1996,1997,1998)
代码执行结果:
正确应为:having to_char(hire_date,‘yyyy’) in (1995,1996,1997,1998)
(2)在employees(员工表)中最低工资小于10000的部门,列出工种为’CLERK’的员工的部门号,最低工资,最高工资:
select department_id as 部门号 ,min(salary) as 最低工资 ,max(salary) as 最高工资
from employees
where job_id like ‘%CLERK%’
group by department_id
having min(salary) < 10000
代码执行结果:
该查询的错误示范(红色标注):
select department_id as 部门号 ,min(salary) as 最低工资,max(salary) as 最高工资
from employees
where job_id like ‘%CLERK%’ and min(salary) < 10000
group by department_id
代码执行结果:
修正可将where改为having;
错误原因:where子句中不能出现组函数。