create table dep(
deptno varchar2(50) primary key,
deptname varchar2(50)
);
insert into dep values ('1','人事部');
insert into dep values ('2','技术部');
insert into dep values ('3','采购部');
commit;
create table dep_emp(
deptno varchar2(50),
empno varchar2(50),
ename varchar2(50),
sal number(10,2)
);
alter table dep_emp add constraint deptno_fk foreign key (deptno) references dep(deptno) on delete cascade;
insert into dep_emp values ('1','011','张三',3000);
insert into dep_emp values ('1','012','李四',3400);
insert into dep_emp values ('2','021','王五',4300);
insert into dep_emp values ('2','021','张三',3200);
insert into dep_emp values ('3','031','李四',3400);
insert into dep_emp values ('3','032','王五',2300);
commit;
--select * from user_tables;
--select * from dep;
--select * from dep_emp;
--显示各部门员工的工资,并附带显示该部分的最高工资。
SELECT E.DEPTNO,
E.EMPNO,
E.ENAME,
E.SAL,
LAST_VALUE(E.SAL)
OVER(PARTITION BY E.DEPTNO
ORDER BY E.SAL ROWS
--unbounded preceding and unbouned following针对当前所有记录的前一条、后一条记录,也就是表中的所有记录
--unbounded:不受控制的,无限的
--preceding:在...之前
--following:在...之后
BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) MAX_SAL
FROM DEP_EMP E;
--按照deptno分组,然后计算每组值的总和
SELECT EMPNO,
ENAME,
DEPTNO,
SAL,
SUM(SAL) OVER(PARTITION BY DEPTNO ORDER BY ENAME) max_sal
FROM DEP_EMP;
--对各部门进行分组,并附带显示第一行至当前行的汇总
SELECT EMPNO,
ENAME,
DEPTNO,
SAL,
--注意ROWS BETWEEN unbounded preceding AND current row 是指第一行至当前行的汇总
SUM(SAL) OVER(PARTITION BY DEPTNO
ORDER BY ENAME
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) max_sal
FROM DEP_EMP;
--当前行至最后一行的汇总
SELECT EMPNO,
ENAME,
DEPTNO,
SAL,
--注意ROWS BETWEEN current row AND unbounded following 指当前行到最后一行的汇总
SUM(SAL) OVER(PARTITION BY DEPTNO
ORDER BY ENAME
ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) max_sal
FROM DEP_EMP;
--当前行的上一行(rownum-1)到当前行的汇总
SELECT EMPNO,
ENAME,
DEPTNO,
SAL,
--注意ROWS BETWEEN 1 preceding AND current row 是指当前行的上一行(rownum-1)到当前行的汇总
SUM(SAL) OVER(PARTITION BY DEPTNO
ORDER BY ENAME ROWS
BETWEEN 1 PRECEDING AND CURRENT ROW) max_sal
FROM DEP_EMP;
--当前行的上一行(rownum-1)到当前行的下辆行(rownum+2)的汇总
SELECT EMPNO,
ENAME,
DEPTNO,
SAL,
--注意ROWS BETWEEN 1 preceding AND 1 following 是指当前行的上一行(rownum-1)到当前行的下辆行(rownum+2)的汇总
SUM(SAL) OVER(PARTITION BY DEPTNO
ORDER BY ENAME
ROWS BETWEEN 1 PRECEDING AND 2 FOLLOWING) max_sal
FROM DEP_EMP;
select d.deptno,sum(d.sal),grouping(d.deptno) from dep_emp d group by cube(d.deptno); --分组后还有对于薪水的总体总计
select d.deptno,d.empno,sum(d.sal),grouping(d.empno) from dep_emp d group by rollup(d.deptno,d.empno); --分组后每组的总计和总体的总计
select d.deptno,sum(d.sal),grouping(d.deptno) from dep_emp d group by d.deptno; --普通分组
--cube,总计显示在上面(过于深奥,比rollup更细)
select d.deptno,sum(d.sal),grouping(d.deptno) from dep_emp d group by cube(d.deptno); --分组后还有对于薪水的总体总计
select d.deptno,d.empno,sum(d.sal),grouping(d.empno) from dep_emp d group by cube(d.deptno,d.empno); --分组后每组的总计和总体的总计
--grouping用作小计(次数),可以看做树型的层级
--partition by 暂看成分组(不是很理解)
--ratio_to_report(所占百分比)
select dense_rank()over(order by d.deptno desc),ratio_to_report(d.deptno)over() from dep_emp d; --连续排名
select rank()over( order by d.deptno desc),ratio_to_report(d.deptno)over() from dep_emp d; --非连续排名