首页 > 代码库 > oracle笔记

oracle笔记

2024-11-16 20:21:39   202人阅读

1.获取唯一记录：distinct

select distinct e.employee_name,s.salary
from employees e,salary s
where e.employee_id=s.salary;
 
2.分组：group by 
select  e.employee_id, e.employee_name, sum(s.salary) total_salary
from employees e, salary s
where e.employee_id=s.employee_id
group by e.employee_id,e.employee_name;
 
3.过滤分组：having 
where 子句可以过滤from 子句所指定的数据源，但是对于group by 子句所产生的分组无效。为了半分组按一定条件进行过滤。having 子句是依附于group by 子句存在而存在的。
select  e.employee_id, e.employee_name, sum(s.salary) total_salary
from employees e, salary s
where e.employee_id=s.employee_id
group by e.employee_id,e.employee_name;
having (sum(s.salary)) > 10000;
 
4.排序：order by （asc 同时也是默认排序方式，降序 desc)
select distinct e.employee_name, s.salary
from employees e, salary s
where e.employee_id=s.employee_id
order by s.salary desc;
 
5.order by 与 group by
select e.employee_name, sum(s.salary) total_salary
from employees e, salary s
where e.employee_id=s.employee_id
group by e.employee_id,e.employee_name
order by total_salary desc;
注：order by 后面不能接除 e.employee_name, total_salary之外的列进行排序。
 
6.order by 与 distinct
order by 与 distinct 关键字同时使用时，也必须遵循一个规则：order by 子句所指定的排序列，必须出现在
select 表达式中。
select e.employee_name
from employees e,salary s
where e.employee_id=s.employee_id
order by s.salary;-------成功
但：
select distinct e.employee_name
from employee e,salary s
where e.employee_id=s.employee_id
order by s.salary;-------失败

oracle笔记


联系
我们