3,492
社区成员
发帖
与我相关
我的任务
分享
SQL> set linesize 120
SQL> SET SERVEROUTPUT ON;
SQL> SELECT * FROM emp ORDER BY sal;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- -------------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-12月-80 800 20
7900 JAMES CLERK 7698 03-12月-81 950 30
7876 ADAMS CLERK 7788 23-5月 -87 1100 20
7521 WARD SALESMAN 7698 22-2月 -81 1250 500 30
7654 MARTIN SALESMAN 7698 28-9月 -81 1250 1400 30
7934 MILLER CLERK 7782 23-1月 -82 1300 10
7844 TURNER SALESMAN 7698 08-9月 -81 1500 0 30
7499 ALLEN SALESMAN 7698 20-2月 -81 1600 300 30
7782 CLARK MANAGER 7839 09-6月 -81 2450 10
7698 BLAKE MANAGER 7839 01-5月 -81 2850 30
7566 JONES MANAGER 7839 02-4月 -81 2975 20
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- -------------- ---------- ---------- ----------
7788 SCOTT ANALYST 7566 19-4月 -87 3000 20
7902 FORD ANALYST 7566 03-12月-81 3000 20
7839 KING PRESIDENT 17-11月-81 5000 10
已选择14行。
SQL>
SQL> DECLARE
2 TYPE tab_type IS TABLE OF NUMBER;
3 v_sal tab_type;
4 BEGIN
5 --按薪水排升序后求两相邻记录的新水差距
6 SELECT sal BULK COLLECT INTO v_sal FROM emp ORDER BY sal;
7 FOR i IN 1 .. v_sal.count LOOP
8 IF i > 1 THEN
9 dbms_output.put_line(v_sal(i) - v_sal(i - 1));
10 END IF;
11 END LOOP;
12 END;
13 /
150
150
150
0
50
200
100
850
400
125
25
0
2000
PL/SQL 过程已成功完成。
SQL> SELECT t.empno,
2 t.ename,
3 t.sal,
4 sal - lag(t.sal) over(ORDER BY sal) delta_value
5 FROM emp t
6 ORDER BY t.sal;
EMPNO ENAME SAL DELTA_VALUE
---------- ---------- ---------- -----------
7369 SMITH 800
7900 JAMES 950 150
7876 ADAMS 1100 150
7521 WARD 1250 150
7654 MARTIN 1250 0
7934 MILLER 1300 50
7844 TURNER 1500 200
7499 ALLEN 1600 100
7782 CLARK 2450 850
7698 BLAKE 2850 400
7566 JONES 2975 125
EMPNO ENAME SAL DELTA_VALUE
---------- ---------- ---------- -----------
7788 SCOTT 3000 25
7902 FORD 3000 0
7839 KING 5000 2000
已选择14行。
SQL>