oracle创建表:
create table EMPLOYEE
(
empid VARCHAR2(50) not null,
name VARCHAR2(50),
salary NUMBER(5),
sex VARCHAR2(50),
dep VARCHAR2(50)
)
;
comment on table EMPLOYEE
is '员工表';
prompt Loading EMPLOYEE...
insert into EMPLOYEE (empid, name, salary, sex, dep)
values ('1', 'joy', 1500, 'm', '综合部');
insert into EMPLOYEE (empid, name, salary, sex, dep)
values ('2', 'mano', 1800, 'f', '综合部');
insert into EMPLOYEE (empid, name, salary, sex, dep)
values ('3', 'cha', 2000, 'm', '综合部');
insert into EMPLOYEE (empid, name, salary, sex, dep)
values ('4', 'rose', 2100, 'm', '综合部');
insert into EMPLOYEE (empid, name, salary, sex, dep)
values ('5', 'phea', 1400, 'f', '人力资源部');
insert into EMPLOYEE (empid, name, salary, sex, dep)
values ('6', 'arthur', 2100, 'm', '人力资源部');
insert into EMPLOYEE (empid, name, salary, sex, dep)
values ('7', 'sasun', 1000, 'f', '人力资源部');
insert into EMPLOYEE (empid, name, salary, sex, dep)
values ('8', 'jack', 4000, 'm', '开发部');
insert into EMPLOYEE (empid, name, salary, sex, dep)
values ('9', 'maech', 3000, 'm', '开发部');
insert into EMPLOYEE (empid, name, salary, sex, dep)
values ('10', 'gab', 1900, 'm', '人力资源部');
commit;
结果:
1、有一个表Employee,查一个公司里所有超过平均工资的员工。
select m.name, m.salary
from EMPLOYEE m, (select avg(t.salary) salavg from EMPLOYEE t) n
where m.salary > n.salavg;
2、女性员工数大于等于2个人的部门。
select n.dep from (select count(*) numb,t.dep from EMPLOYEE t where t.sex = 'f' group by t.dep) n where n.numb>1;
select n.dep
from (select count(*) numb, t.dep
from EMPLOYEE t
where t.sex = 'f'
group by t.dep) n
where n.numb > 1;
3、分页语句。
SELECT *
FROM (SELECT ROWNUM AS rowno, t.*
FROM Employee t
WHERE 1 = 1
AND ROWNUM <= 10) table_alias
WHERE table_alias.rowno >= 5;
SELECT *
FROM (SELECT a.*, ROWNUM rn
FROM (SELECT *
FROM Employee) a
WHERE ROWNUM <= 10)
WHERE rn >= 5
以下是rank over的用法
1、查公司工资前三的人员
select * from (select rank() over(order by S.salary desc) rk,S.name,S.salary from EMPLOYEE S) T where T.rk<=3;
select *
from (select rank() over(order by S.salary desc) rk, S.name, S.salary
from EMPLOYEE S) T
where T.rk <= 3;
2、对所有的工资进行排序, 整个结果集是一个分组,以name进行排名
SELECT t.name, t.salary, rank() OVER(ORDER BY t.salary) rank
FROM EMPLOYEE t;
3、以t.dep进行分组,对工资进行排名。
SELECT t.dep, t.name,t.salary,
rank() OVER(PARTITION by t.dep ORDER BY t.salary) rank
FROM EMPLOYEE t;
4、以t.sex,t.dep进行分组,在每个组内以b进行排名。
SELECT t.sex,t.dep,t.salary,
rank() OVER(PARTITION by t.sex, t.dep ORDER BY t.salary) rank
FROM EMPLOYEE t;
总结:
1、partition by用于给结果集分组,如果没有指定那么它把整个结果集作为一个分组。
2、Rank 是在每个分组内部进行排名的。
参考:http://www.cnblogs.com/mycoding/archive/2010/05/29/1747065.html
转载请注明: