oracle笔记

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笔记

标签：使用   desc   方式   distinct   过滤   nbsp   div   esc   employees