爱站程序员基地文章目录
- 一.row_number、rank、dense_rank
- 二.lag、lead
- 三.first_value、last_value、nth_value
- 四.ratio_to_report
- 五.percent_rank、percentile_cont、percentile_dist
- 六.ntile
备注:测试数据库版本为Oracle 11g R2
如需要scott用户下建表及录入数据语句，可参考:
scott建表及录入数据sql脚本
这个blog我们来聊聊Oracle高级分析函数
Oracle的分析函数在复杂查询以及数据仓库中应用得比较频繁
与sql打交道比较多的技术人员都需要掌握
| 函数名 | 函数用途 |
|---|---|
| row_number | 对行进行排序并为每一行增加一个唯一编号。这是一个非确定性函数 |
| rank | 将数据行值按照排序后的顺序进行排名，在有并列的情况下排名值将被跳过 |
| dense_rank | 将数据行值按照排序后的顺序进行排名，在有并列的情况下也不跳过排名值 |
| lag | 访问一个分区或结果集中之前的一行 |
| lead | 访问一个分区或结果集中之后的一行 |
| first_value | 访问一个分区或结果集中第一行 |
| last_value | 访问一个分区或结果集中最后一行 |
| nth_value | 访问一个分区或结果集中的任意一行 |
| ratio_to_report | 计算报告中值的比例 |
| percent_rank | 将计算得到的排名值标准化 |
| percentile_cont | 取出与指定的排名百分比相匹配的值，是percent_rank函数的反函数 |
| percentile_dist | 取出与指定的排名百分比相匹配的值，采用谨慎分布模型 |
| ntile | 将数据行分组为单元 |
分析函数有3个基本组成部分：
1.分区子句
2.排序子句
3.开窗子句
function1 (argument1,argument2,..argumentN)over ([partition-by-clause] [order-by-clause] [windowing-clause])
窗口说明子句的语法:
默认的窗口子句是rows between unbounded preceding and current row。如果你没有显示声明窗口，就将会使用默认窗口。
并不是所有的分析函数都支持开窗子句
[rows | range] between <start expr> and [end expr]whereas<start expr> is [unbounded preceding | current row | n preceding | n following]<end expr> is [unbounded following | current row | n preceding | n following]
一.row_number、rank、dense_rank
row_number语法：
row_number() over (partition-clause order-by-clause)
row_number不支持开窗子句
rank、dense_rank语法同row_number语法
现在需要对分不同部门来看部门内的工资排名，且从大到小排列：
--可以看到deptno为30的员工工资有重复的,重复的工资为1250--row_number() 不关注重复的，直接排名，1-2-3-4-5-6--rank() 重复排名,会跳过，1-2-3-4-4-6--dense_rank() 重复排名,不跳过，1-2-3-4-4-5select a.empno,a.ename,a.deptno,a.sal,row_number() over(partition by a.deptno order by a.sal desc) num,rank() over(partition by a.deptno order by a.sal desc) rank,dense_rank() over(partition by a.deptno order by a.sal desc) dense_rankfrom emp a;
SQL> select a.empno,2 a.ename,3 a.deptno,4 a.sal,5 row_number() over(partition by a.deptno order by a.sal desc) num,6 rank() over(partition by a.deptno order by a.sal desc) rank,7 dense_rank() over(partition by a.deptno order by a.sal desc) dense_rank8 from emp a;EMPNO ENAME DEPTNO SAL NUM RANK DENSE_RANK----- ---------- ------ --------- ---------- ---------- ----------7839 KING 10 5000.00 1 1 17782 CLARK 10 2450.00 2 2 27934 MILLER 10 1300.00 3 3 37788 SCOTT 20 3000.00 1 1 17902 FORD 20 3000.00 2 1 17566 JONES 20 2975.00 3 3 27876 ADAMS 20 1100.00 4 4 37369 SMITH 20 800.00 5 5 47698 BLAKE 30 2850.00 1 1 17499 ALLEN 30 1600.00 2 2 27844 TURNER 30 1500.00 3 3 37654 MARTIN 30 1250.00 4 4 47521 WARD 30 1250.00 5 4 47900 JAMES 30 950.00 6 6 514 rows selected
二.lag、lead
lag语法:
lag (expression, offset, default) over (partition-clause order-by-clause)
lag不支持开窗子句
lead同lag语法
--根据分组，取值上n条和下n条 如果是第一条或最后一条，就给个默认值SELECT a.empno,a.deptno,a.hiredate,a.sal,lag(sal, 1, 0) over(PARTITION BY a.deptno ORDER BY hiredate ASC) pre_sal,lead(sal, 1, 0) over(partition by a.deptno order by hiredate asc) next_sal,lag(sal, 2, 0) over(PARTITION BY a.deptno ORDER BY hiredate ASC) pre2_sal,lead(sal, 2, 0) over(partition by a.deptno order by hiredate asc) next_2salFROM emp a;
SQL> --根据分组，取值上n条和下n条 如果是第一条或最后一条，就给个默认值SQL> SELECT a.empno,2 a.deptno,3 a.hiredate,4 a.sal,5 lag(sal, 1, 0) over(PARTITION BY a.deptno ORDER BY hiredate ASC) pre_sal,6 lead(sal, 1, 0) over(partition by a.deptno order by hiredate asc) next_sal,7 lag(sal, 2, 0) over(PARTITION BY a.deptno ORDER BY hiredate ASC) pre2_sal,8 lead(sal, 2, 0) over(partition by a.deptno order by hiredate asc) next_2sal9 FROM emp a;EMPNO DEPTNO HIREDATE SAL PRE_SAL NEXT_SAL PRE2_SAL NEXT_2SAL----- ------ ------------------------------ --------- ---------- ---------- ---------- ----------7782 10 1981/6/9 星期二 2450.00 0 5000 0 13007839 10 1981/11/17 星期二 5000.00 2450 1300 0 07934 10 1982/1/23 星期六 1300.00 5000 0 2450 07369 20 1980/12/17 星期三 800.00 0 2975 0 30007566 20 1981/4/2 星期四 2975.00 800 3000 0 30007902 20 1981/12/3 星期四 3000.00 2975 3000 800 11007788 20 1987/4/19 星期日 3000.00 3000 1100 2975 07876 20 1987/5/23 星期六 1100.00 3000 0 3000 07499 30 1981/2/20 星期五 1600.00 0 1250 0 28507521 30 1981/2/22 星期日 1250.00 1600 2850 0 15007698 30 1981/5/1 星期五 2850.00 1250 1500 1600 12507844 30 1981/9/8 星期二 1500.00 2850 1250 1250 9507654 30 1981/9/28 星期一 1250.00 1500 950 2850 07900 30 1981/12/3 星期四 950.00 1250 0 1500 014 rows selected
--没有比自己小我的我们设为AAA,没有比自己大的，我们设置为ZZZselect deptno,ename,lag(ename, 1, \'AAA\') over(partition by deptno order by ename) lower_name,lead(ename, 1, \'ZZZ\') over(partition by deptno order by ename) higher_namefrom emp;--部门重复的话值输出第一行的部门编号select (case when deptno= lag(deptno,1)over(partition by deptno order by ename) then null else deptno end) deptno,ename,lag(ename, 1, \'AAA\') over(partition by deptno order by ename) lower_name,lead(ename, 1, \'ZZZ\') over(partition by deptno order by ename) higher_namefrom emp;
SQL> --没有比自己小我的我们设为AAA,没有比自己大的，我们设置为ZZZSQL> select deptno,2 ename,3 lag(ename, 1, \'AAA\') over(partition by deptno order by ename) lower_name,4 lead(ename, 1, \'ZZZ\') over(partition by deptno order by ename) higher_name5 from emp;DEPTNO ENAME LOWER_NAME HIGHER_NAME------ ---------- ---------- -----------10 CLARK AAA KING10 KING CLARK MILLER10 MILLER KING ZZZ20 ADAMS AAA FORD20 FORD ADAMS JONES20 JONES FORD SCOTT20 SCOTT JONES SMITH20 SMITH SCOTT ZZZ30 ALLEN AAA BLAKE30 BLAKE ALLEN JAMES30 JAMES BLAKE MARTIN30 MARTIN JAMES TURNER30 TURNER MARTIN WARD30 WARD TURNER ZZZ14 rows selectedSQL> --部门重复的话值输出第一行的部门编号SQL> select (case when deptno= lag(deptno,1)over(partition by deptno order by ename) then null else deptno end) deptno,2 ename,3 lag(ename, 1, \'AAA\') over(partition by deptno order by ename) lower_name,4 lead(ename, 1, \'ZZZ\') over(partition by deptno order by ename) higher_name5 from emp;DEPTNO ENAME LOWER_NAME HIGHER_NAME---------- ---------- ---------- -----------10 CLARK AAA KINGKING CLARK MILLERMILLER KING ZZZ20 ADAMS AAA FORDFORD ADAMS JONESJONES FORD SCOTTSCOTT JONES SMITHSMITH SCOTT ZZZ30 ALLEN AAA BLAKEBLAKE ALLEN JAMESJAMES BLAKE MARTINMARTIN JAMES TURNERTURNER MARTIN WARDWARD TURNER ZZZ14 rows selected
三.first_value、last_value、nth_value
first_value、last_value语法:
first_value(expression) over (partition-clause order-by-clause windowing-clause)last_value(expression) over (partition-clause order-by-clause windowing-clause)
nth_value语法:
nth_value (measure, n) [ from first | from last ] [ respect nulls | ignore nulls ]over (partitioning-clause order-by-clause windowing-clause)
/*需求:求每个部门工资最高的和工资最低的以及工资第二高的*/--默认不带开窗子句,从第一行到当前行select a.empno,a.deptno,to_char(hiredate,\'yyyy\') hiredate,a.sal,first_value(a.sal) over(partition by a.deptno order by sal) first,last_value(a.sal) over(partition by a.deptno order by sal) last,nth_value(a.sal,2) over(partition by a.deptno order by sal) top_2from emp a;--rows between unbounded preceding and current row 从第一行到当前行select a.empno,a.deptno,to_char(hiredate,\'yyyy\') hiredate,a.sal,first_value(a.sal) over(partition by a.deptno order by sal rows between unbounded preceding and current row) first,last_value(a.sal) over(partition by a.deptno order by sal rows between unbounded preceding and current row) last,nth_value(a.sal,2) over(partition by a.deptno order by sal rows between unbounded preceding and current row) top_2from emp a;--rows between unbounded preceding and unbounded following 从第一行到最后一行select a.empno,a.deptno,to_char(hiredate,\'yyyy\') hiredate,a.sal,first_value(a.sal) over(partition by a.deptno order by sal rows between unbounded preceding and unbounded following) first,last_value(a.sal) over(partition by a.deptno order by sal rows between unbounded preceding and unbounded following) last,nth_value(a.sal,2) over(partition by a.deptno order by sal rows between unbounded preceding and unbounded following) top_2from emp a;--1 preceding and 1 following 当前行的前一行到当前行的后一行select a.empno,a.deptno,to_char(hiredate,\'yyyy\') hiredate,a.sal,first_value(a.sal) over(partition by a.deptno order by sal rows between 1 preceding and 1 following) first,last_value(a.sal) over(partition by a.deptno order by sal rows between 1 preceding and 1 following) last,nth_value(a.sal,2) over(partition by a.deptno order by sal rows between 1 preceding and 1 following top_2from emp a;
SQL> /*2 需求:求每个部门工资最高的和工资最低的以及工资第二高的3 */SQL> --默认不带开窗子句,从第一行到当前行SQL> select a.empno,a.deptno,to_char(hiredate,\'yyyy\') hiredate,a.sal,2 first_value(a.sal) over(partition by a.deptno order by sal) first,3 last_value(a.sal) over(partition by a.deptno order by sal) last,4 nth_value(a.sal,2) over(partition by a.deptno order by sal) top_25 from emp a;EMPNO DEPTNO HIREDATE SAL FIRST LAST TOP_2----- ------ -------- --------- ---------- ---------- ----------7934 10 1982 1300.00 1300 13007782 10 1981 2450.00 1300 2450 24507839 10 1981 5000.00 1300 5000 24507369 20 1980 800.00 800 8007876 20 1987 1100.00 800 1100 11007566 20 1981 2975.00 800 2975 11007788 20 1987 3000.00 800 3000 11007902 20 1981 3000.00 800 3000 11007900 30 1981 950.00 950 9507654 30 1981 1250.00 950 1250 12507521 30 1981 1250.00 950 1250 12507844 30 1981 1500.00 950 1500 12507499 30 1981 1600.00 950 1600 12507698 30 1981 2850.00 950 2850 125014 rows selectedSQL> --SQL> --SQL> --rows between unbounded preceding and current row 从第一行到当前行SQL> select a.empno,a.deptno,to_char(hiredate,\'yyyy\') hiredate,a.sal,2 first_value(a.sal) over(partition by a.deptno order by sal rows between unbounded preceding and current row) first,3 last_value(a.sal) over(partition by a.deptno order by sal rows between unbounded preceding and current row) last,4 nth_value(a.sal,2) over(partition by a.deptno order by sal rows between unbounded preceding and current row) top_25 from emp a;EMPNO DEPTNO HIREDATE SAL FIRST LAST TOP_2----- ------ -------- --------- ---------- ---------- ----------7934 10 1982 1300.00 1300 13007782 10 1981 2450.00 1300 2450 24507839 10 1981 5000.00 1300 5000 24507369 20 1980 800.00 800 8007876 20 1987 1100.00 800 1100 11007566 20 1981 2975.00 800 2975 11007788 20 1987 3000.00 800 3000 11007902 20 1981 3000.00 800 3000 11007900 30 1981 950.00 950 9507654 30 1981 1250.00 950 1250 12507521 30 1981 1250.00 950 1250 12507844 30 1981 1500.00 950 1500 12507499 30 1981 1600.00 950 1600 12507698 30 1981 2850.00 950 2850 125014 rows selectedSQL> --SQL> --SQL> --rows between unbounded preceding and unbounded following 从第一行到最后一行SQL> select a.empno,a.deptno,to_char(hiredate,\'yyyy\') hiredate,a.sal,2 first_value(a.sal) over(partition by a.deptno order by sal rows between unbounded preceding and unbounded following) first,3 last_value(a.sal) over(partition by a.deptno order by sal rows between unbounded preceding and unbounded following) last,4 nth_value(a.sal,2) over(partition by a.deptno order by sal rows between unbounded preceding and unbounded following) top_25 from emp a;EMPNO DEPTNO HIREDATE SAL FIRST LAST TOP_2----- ------ -------- --------- ---------- ---------- ----------7934 10 1982 1300.00 1300 5000 24507782 10 1981 2450.00 1300 5000 24507839 10 1981 5000.00 1300 5000 24507369 20 1980 800.00 800 3000 11007876 20 1987 1100.00 800 3000 11007566 20 1981 2975.00 800 3000 11007788 20 1987 3000.00 800 3000 11007902 20 1981 3000.00 800 3000 11007900 30 1981 950.00 950 2850 12507654 30 1981 1250.00 950 2850 12507521 30 1981 1250.00 950 2850 12507844 30 1981 1500.00 950 2850 12507499 30 1981 1600.00 950 2850 12507698 30 1981 2850.00 950 2850 125014 rows selectedSQL> --SQL> --SQL> --1 preceding and 1 following 当前行的前一行到当前行的后一行SQL> select a.empno,a.deptno,to_char(hiredate,\'yyyy\') hiredate,a.sal,2 first_value(a.sal) over(partition by a.deptno order by sal rows between 1 preceding and 1 following) first,3 last_value(a.sal) over(partition by a.deptno order by sal rows between 1 preceding and 1 following) last,4 nth_value(a.sal,2) over(partition by a.deptno order by sal rows between 1 preceding and 1 following) top_25 from emp a;EMPNO DEPTNO HIREDATE SAL FIRST LAST TOP_2----- ------ -------- --------- ---------- ---------- ----------7934 10 1982 1300.00 1300 2450 24507782 10 1981 2450.00 1300 5000 24507839 10 1981 5000.00 2450 5000 50007369 20 1980 800.00 800 1100 11007876 20 1987 1100.00 800 2975 11007566 20 1981 2975.00 1100 3000 29757788 20 1987 3000.00 2975 3000 30007902 20 1981 3000.00 3000 3000 30007900 30 1981 950.00 950 1250 12507654 30 1981 1250.00 950 1250 12507521 30 1981 1250.00 1250 1500 12507844 30 1981 1500.00 1250 1600 15007499 30 1981 1600.00 1500 2850 16007698 30 1981 2850.00 1600 2850 285014 rows selected
四.ratio_to_report
ratio_to_report语法:
ratio_to_report(exp) over()
测试数据:
DROP TABLE testa;CREATE TABLE testa (area VARCHAR2 (20), month VARCHAR2 (20),amount NUMBER);insert into testa values (\'上海\', \'1月\', 199);insert into testa values (\'上海\', \'2月\', 199);insert into testa values (\'上海\', \'3月\', 155);insert into testa values (\'上海\', \'3月\', 155);insert into testa values (\'上海\', \'4月\', 125);insert into testa values (\'广州\', \'1月\', 75);insert into testa values (\'广州\', \'2月\', 67);insert into testa values (\'北京\', \'1月\', 235);insert into testa values (\'北京\', \'2月\', 330);Commit;
--求每个月销售额占比SELECT area,MONTH,SUM(amount) amount,ROUND(ratio_to_report(SUM(amount)) OVER(PARTITION BY area), 4) * 100 area_pctFROM testaGROUP BY area, MONTH;
SQL> --求每个月销售额占比SQL> SELECT area,2 MONTH,3 SUM(amount) amount,4 ROUND(ratio_to_report(SUM(amount)) OVER(PARTITION BY area), 4) * 100 area_pct5 FROM testa6 GROUP BY area, MONTH;AREA MONTH AMOUNT AREA_PCT-------------------- -------------------- ---------- ----------北京 1月 235 41.59北京 2月 330 58.41广州 1月 75 52.82广州 2月 67 47.18上海 1月 199 23.89上海 2月 199 23.89上海 3月 310 37.21上海 4月 125 15.018 rows selected
五.percent_rank、percentile_cont、percentile_dist
percent_rank语法:
percent_rank(expr) within group (order-by-clause )percent_rank() over ([partition-by-clause] [order-by-clause] )
PERCENTILE_CONT语法:
percent_rank(expr) within group (order-by-clause ) over ([partition-by-clause] [order-by-clause] )
PERCENTILE_DISC语法:
PERCENTILE_DISC(expr) within group (order-by-clause ) over ([partition-by-clause] [order-by-clause] )
–percent_rank函数以0到1之间的分数形式返回某个值在数据分区中的排名
–percent_rank的计算公式为(rank-1)/(n-1)
SELECT a.empno,a.ename,a.deptno,a.sal,percent_rank() over(PARTITION BY a.deptno ORDER BY a.sal DESC) numFROM emp a;
SQL> SELECT a.empno,2 a.ename,3 a.deptno,4 a.sal,5 percent_rank() over(PARTITION BY a.deptno ORDER BY a.sal DESC) num6 FROM emp a;EMPNO ENAME DEPTNO SAL NUM----- ---------- ------ --------- ----------7839 KING 10 5000.00 07782 CLARK 10 2450.00 0.57934 MILLER 10 1300.00 17788 SCOTT 20 3000.00 07902 FORD 20 3000.00 07566 JONES 20 2975.00 0.57876 ADAMS 20 1100.00 0.757369 SMITH 20 800.00 17698 BLAKE 30 2850.00 07499 ALLEN 30 1600.00 0.27844 TURNER 30 1500.00 0.47654 MARTIN 30 1250.00 0.67521 WARD 30 1250.00 0.67900 JAMES 30 950.00 114 rows selected
–percentile_cont
–如计算一个城市或地区中等收入家庭的收入值，中位值是percent_rank为0.5
–percentile_cont(0.5)子句将会返回中位值,如果没有0.5,则取值上下最接近的2个值求平均值
SELECT ename,sal,deptno,PERCENTILE_CONT(0.5) WITHIN GROUP(ORDER BY sal DESC) OVER(PARTITION BY deptno) \"Percentile_Cont\",PERCENT_RANK() OVER(PARTITION BY deptno ORDER BY sal DESC) \"Percent_Rank\"FROM empORDER BY deptno,sal desc;
SQL> SELECT ename,2 sal,3 deptno,4 PERCENTILE_CONT(0.5) WITHIN GROUP(ORDER BY sal DESC) OVER(PARTITION BY deptno) \"Percentile_Cont\",5 PERCENT_RANK() OVER(PARTITION BY deptno ORDER BY sal DESC) \"Percent_Rank\"6 FROM emp7 ORDER BY deptno,sal desc;ENAME SAL DEPTNO Percentile_Cont Percent_Rank---------- --------- ------ --------------- ------------KING 5000.00 10 2450 0CLARK 2450.00 10 2450 0.5MILLER 1300.00 10 2450 1SCOTT 3000.00 20 2975 0FORD 3000.00 20 2975 0JONES 2975.00 20 2975 0.5ADAMS 1100.00 20 2975 0.75SMITH 800.00 20 2975 1BLAKE 2850.00 30 1375 0ALLEN 1600.00 30 1375 0.2TURNER 1500.00 30 1375 0.4WARD 1250.00 30 1375 0.6MARTIN 1250.00 30 1375 0.6JAMES 950.00 30 1375 114 rows selectedSQL>
–percentile_disc
percentile_disc函数在功能上类似percentile_cont函数，只是percentile_cont函数使用了连续分布模型，而percentile_disc函数使用了离散分布模型。
SELECT ename,sal,deptno,PERCENTILE_CONT(0.5) WITHIN GROUP(ORDER BY sal DESC) OVER(PARTITION BY deptno) \"Percentile_Cont\",PERCENTILE_DISC(0.5) WITHIN GROUP(ORDER BY sal DESC) OVER(PARTITION BY deptno) \"Percentile_Disc\",PERCENT_RANK() OVER(PARTITION BY deptno ORDER BY sal DESC) \"Percent_Rank\"FROM empORDER BY deptno,sal desc;
SQL> SELECT ename,2 sal,3 deptno,4 PERCENTILE_CONT(0.5) WITHIN GROUP(ORDER BY sal DESC) OVER(PARTITION BY deptno) \"Percentile_Cont\",5 PERCENTILE_DISC(0.5) WITHIN GROUP(ORDER BY sal DESC) OVER(PARTITION BY deptno) \"Percentile_Disc\",6 PERCENT_RANK() OVER(PARTITION BY deptno ORDER BY sal DESC) \"Percent_Rank\"7 FROM emp8 ORDER BY deptno,sal desc;ENAME SAL DEPTNO Percentile_Cont Percentile_Disc Percent_Rank---------- --------- ------ --------------- --------------- ------------KING 5000.00 10 2450 2450 0CLARK 2450.00 10 2450 2450 0.5MILLER 1300.00 10 2450 2450 1SCOTT 3000.00 20 2975 2975 0FORD 3000.00 20 2975 2975 0JONES 2975.00 20 2975 2975 0.5ADAMS 1100.00 20 2975 2975 0.75SMITH 800.00 20 2975 2975 1BLAKE 2850.00 30 1375 1500 0ALLEN 1600.00 30 1375 1500 0.2TURNER 1500.00 30 1375 1500 0.4WARD 1250.00 30 1375 1500 0.6MARTIN 1250.00 30 1375 1500 0.6JAMES 950.00 30 1375 1500 114 rows selected
六.ntile
Ntile语法:
Ntile(expr) OVER ([ query_partition_clause ] order_by_clause)
Ntile 把数据行分成N个桶。每个桶会有相同的行数，正负误差为1
将员工表emp按照工资分为2、3个桶
--分成2个桶SELECT ENAME, SAL, NTILE(2) OVER (ORDER BY SAL ASC ) FROM EMP;--分成3个桶SELECT ENAME, SAL, NTILE(3) OVER (ORDER BY SAL ASC ) FROM EMP;
SQL> --分成2个桶SQL> SELECT ENAME, SAL, NTILE(2) OVER (ORDER BY SAL ASC ) FROM EMP;ENAME SAL NTILE(2)OVER(ORDERBYSALASC)---------- --------- ---------------------------SMITH 800.00 1JAMES 950.00 1ADAMS 1100.00 1WARD 1250.00 1MARTIN 1250.00 1MILLER 1300.00 1TURNER 1500.00 1ALLEN 1600.00 2CLARK 2450.00 2BLAKE 2850.00 2JONES 2975.00 2SCOTT 3000.00 2FORD 3000.001eeb22KING 5000.00 214 rows selectedSQL> --分成3个桶SQL> SELECT ENAME, SAL, NTILE(3) OVER (ORDER BY SAL ASC ) FROM EMP;ENAME SAL NTILE(3)OVER(ORDERBYSALASC)---------- --------- ---------------------------SMITH 800.00 1JAMES 950.00 1ADAMS 1100.00 1WARD 1250.00 1MARTIN 1250.00 1MILLER 1300.00 2TURNER 1500.00 2ALLEN 1600.00 2CLARK 2450.00 2BLAKE 2850.00 2JONES 2975.00 3SCOTT 3000.00 3FORD 3000.00 3KING 5000.00 314 rows selected
