Oracle分析函数三(first,last,first_value,last_value)

一、创建表,初始化数据

CREATE TABLE t_emp (
  emp_no VARCHAR2(10),
  emp_name VARCHAR2(30),
  dept_no VARCHAR2(10),
  emp_salary NUMBER
);

INSERT INTO t_emp VALUES('001', '张三',   '10', 2000);
INSERT INTO t_emp VALUES('002', '李四',   '10', 3000);
INSERT INTO t_emp VALUES('003', '王五',   '10', 1500);
INSERT INTO t_emp VALUES('004', '赵六',   '10', 5000);
INSERT INTO t_emp VALUES('005', '王麻子', '20', 4000);
INSERT INTO t_emp VALUES('006', '陈中华', '20', 8000);
INSERT INTO t_emp VALUES('007', '赵无极', '20', 6000);
INSERT INTO t_emp VALUES('008', '田丰',   '10', 5000);

二、取部门工资最小和最大的人

SELECT dept_no,
  MIN(emp_name) keep (dense_rank FIRST ORDER BY emp_salary) min_salary_person,
  MIN(emp_name) keep (dense_rank LAST ORDER BY emp_salary)  max_salary_person,
  wm_concat(emp_name) keep (dense_rank FIRST ORDER BY emp_salary) min_salary_person,
  wm_concat(emp_name) keep (dense_rank LAST ORDER BY emp_salary)  max_salary_person
FROM t_emp
GROUP BY dept_no

运行结果:

DEPT_NO MIN_SALARY_PERSON MAX_SALARY_PERSON MIN_SALARY_PERSON MAX_SALARY_PERSON
10 王五 田丰 王五 赵六,田丰
20 王麻子 陈中华 王麻子 陈中华

三、列出部门所有的人,并增加本部门工资最小和最大人的名字
注意last_value函数,使用order by会加上一个默认窗口RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW,必须修改为窗口的全部记录,否则

SELECT dept_no, emp_name, emp_salary,
  first_value(emp_name) over (PARTITION BY dept_no ORDER BY emp_salary) min_salary_person,
  last_value(emp_name) over (PARTITION BY dept_no ORDER BY emp_salary rows between unbounded preceding and unbounded following) max_salary_person
FROM t_emp

运行结果:

DEPT_NO EMP_NAME EMP_SALARY MIN_SALARY_PERSON MAX_SALARY_PERSON
10 王五 1500 王五 赵六
10 张三 2000 王五 赵六
10 李四 3000 王五 赵六
10 田丰 5000 王五 赵六
10 赵六 5000 王五 赵六
20 王麻子 4000 王麻子 陈中华
20 赵无极 6000 王麻子 陈中华
20 陈中华 8000 王麻子 陈中华

Comments are closed.