oracle 分析函数以及实例解析
1.排名函数：
RANK and DENSE_RANK Functions
语法如下：
RANK ( ) OVER ( [query_partition_clause] order_by_clause )
DENSE_RANK ( ) OVER ( [query_partition_clause]order_by_clause )
在排名函数中order by是不可选字段，表示你要按什么进行排名，partition是可选字段，如果没有partition则表示对全部数据进行排名，如果有partition则表示在分区内进行排序。
我们先来看一个例子：
SQL> SELECT EMPNO,
2 DEPTNO,
3 SUM(SAL) SAL,
4 RANK() OVER(ORDER BY SUM(SAL) DESC)RANK_DESC,
5 RANK() OVER(ORDER BY SUM(SAL))RANK_ASC,
6 DENSE_RANK() OVER(ORDER BY SUM(SAL)DESC) DENSE
7 FROM EMP
8 GROUP BY DEPTNO, EMPNO;
EMPNO DEPTNO SAL RANK_DESC RANK_ASC DENSE
---------- ---------- ---------- ---------- --------------------
7900 30 950 14 1 12
7876 20 1100 13 2 11
7521 30 1250 11 3 10
7654 30 1250 11 3 10
7934 10 1300 10 5 9
7844 30 1500 9 6 8
7499 30 1600 8 7 7
7782 10 2450 7 8 6
7698 30 2850 6 9 5
7566 20 2975 5 10 4
7902 20 3000 3 11 3
EMPNO DEPTNO SAL RANK_DESC RANK_ASC DENSE
---------- ---------- ---------- ---------- --------------------
7788 20 3000 3 11 3
7839 10 5000 2 13 2
7369 20 10000 1 14 1
已选择14行。
在以上这个例子中我们可以看到，oracle默认是按照asc进行排名的，rank函数的排名是有间隙的，比如说上面两个11名，接下来就是13名，而dense_rank是没有间隙的。
接下来看一下partition是如何处理的
SQL> SELECT EMPNO,
2 DEPTNO,
3 SUM(SAL) SAL,
4 RANK() OVER(PARTITION BY deptno ORDERBY SUM(SAL) DESC) rank_par
5 FROM EMP
6 GROUP BY DEPTNO, EMPNO;
EMPNO DEPTNO SAL RANK_PAR
---------- ---------- ---------- ----------
7839 10 5000 1
7782 10 2450 2
7934 10 1300 3
7369 20 10000 1
7788 20 3000 2
7902 20 3000 2
7566 20 2975 4
7876 20 1100 5
7698 30 2850 1
7499 30 1600 2
7844 30 1500 3
EMPNO DEPTNO SAL RANK_PAR
---------- ---------- ---------- ----------
7521 30 1250 4
7654 30 1250 4
7900 30 950 6
已选择14行。
可以看到oracle在deptno内进行了排序，也就是以deptno为分区进行排序。
NTILEFunction
语法如下：
NTILE (expr) OVER([query_partition_clause] order_by_clause)
ntile函数会按照order by对数据进行切片，但是有一个比较有趣的情况，那就是当数据不能平分时，Oracle会将不能平分的数据按照片号从高到低一个一个的分配，例子如下：
SQL> SELECT EMPNO,
2 DEPTNO,
3 SUM(SAL) SAL,
4 ntile(4) OVER(ORDER BY SUM(SAL) DESC)til
5 FROM EMP
6 GROUP BY DEPTNO,EMPNO;
EMPNO DEPTNO SAL TILE
---------- ---------- ---------- ----------
7369 20 10000 1
7839 10 5000 1
7788 20 3000 1
7902 20 3000 1
7566 20 2975 2
7698 30 2850 2
7782 10 2450 2
7499 30 1600 2
7844 30 1500 3
7934 10 1300 3
7654 30 1250 3
EMPNO DEPTNO SAL TILE
---------- ---------- ---------- ----------
7521 30 1250 4
7876 20 1100 4
7900 30 950 4
ROW_NUMBERFunction
ROW_NUMBER ( ) OVER ([query_partition_clause] order_by_clause )
这个函数会根据order by对每一行分配一个特定的编号，从1开始。例子如下：
SQL> SELECT EMPNO,
2 DEPTNO,
3 SUM(SAL) SAL,
4 row_number() OVER(ORDER BY SUM(SAL)DESC) row_number
5 FROM EMP
6 GROUP BY DEPTNO,EMPNO;
EMPNO DEPTNO SAL ROW_NUMBER
---------- ---------- ---------- ----------
7369 20 10000 1
7839 10 5000 2
7788 20 3000 3
7902 20 3000 4
7566 20 2975 5
7698 30 2850 6
7782 10 2450 7
7499 30 1600 8
7844 30 1500 9
7934 10 1300 10
7654 30 1250 11
EMPNO DEPTNO SAL ROW_NUMBER
---------- ---------- ---------- ----------
7521 30 1250 12
7876 20 1100 13
7900 30 950 14
其它一些函数并不常用，请参照官方文档。
2.window函数
先来看看Oracle官方文档的介绍吧
Windowing functions can be used to computecumulative, moving, and centered aggregates. They return a value for each rowin the table, which depends on other rows in the corresponding window. Withwindowing aggregate functions, you can calculate moving andcumulative versions of SUM, AVERAGE, COUNT, MAX,MIN, andmany more functions. They can be used only inthe SELECT and ORDER BY clauses of the query.Windowing aggregate functions include the convenientFIRST_VALUE, which returnsthe first value in the window; and LAST_VALUE, which returns the lastvalue in the window. These functions provide access to more than one row of atable without a self-join.
语法如下：
analytic_function([arguments ])
OVER (analytic_clause)
where analytic_clause=
[ query_partition_clause ]
[ order_by_clause [ windowing_clause] ]
andquery_partition_clause =
PARTITION BY
{ value_expr[, value_expr ]...
}
and windowing_clause =
{ ROWS | RANGE }
{ BETWEEN
{ UNBOUNDED PRECEDING
| CURRENT ROW
| value_expr { PRECEDING | FOLLOWING}
}
AND
{ UNBOUNDED FOLLOWING
| CURRENT ROW
| value_expr { PRECEDING | FOLLOWING}
}
| { UNBOUNDED PRECEDING
| CURRENT ROW
| value_expr PRECEDING
}
}
在了解之前先了解一下rows和range的区别，
rows表示一个物理窗口，也就是排序后，按排序结果的行号对应确定窗口
Range是默认窗口，表示一个行和行之间的逻辑关系（当前行的排序键的value加或减逻辑偏移量得到当前行对应的逻辑窗口的范围）
Cumulative Aggregate Function
下面这个例子表示对一个部门的工资进行累加
SQL> SELECT EMPNO,
2 DEPTNO,
3 SUM(sal),
4 SUM(SAL) OVER(
5 PARTITION BY deptno ORDER BY empno DESCROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
6 ) row_number
7 FROM EMP
8 GROUP BY DEPTNO,EMPNO,sal;
EMPNO DEPTNO SUM(SAL) ROW_NUMBER
---------- -------------------- ----------
7934 10 1300 1300
7839 10 5000 6300
7782 10 2450 8750
7902 20 3000 3000
7876 20 1100 4100
7788 20 3000 7100
7566 20 2975 10075
7369 20 10000 20075
7900 30 950 950
7844 30 1500 2450
7698 30 2850 5300
EMPNO DEPTNO SUM(SAL) ROW_NUMBER
---------- -------------------- ----------
7654 30 1250 6550
7521 30 1250 7800
7499 30 1600 9400
这个例子中，窗口开始于partition by的第一条数据，结束于当前行，oracle默认的结束为currentrow
MovingAggregate Function
SQL> SELECT EMPNO,
2 DEPTNO,
3 SUM(sal),
4 SUM(SAL) OVER(
5 PARTITION BY deptno ORDER BY empno DESC ROWS BETWEEN 1 PRECEDING AND 1Following
6 ) rows_sum
7 FROMEMP
8 GROUP BY DEPTNO,EMPNO,sal;
EMPNO DEPTNO SUM(SAL) ROWS_SUM
---------- -------------------- ----------
7934 10 1300 6300 --这个表示1 PRECEDING，就是向前推进一行
7839 10 5000 8750 --假如这个是当前行
7782 10 2450 7450 --这个表示1 Following，就是向后推进一行
7902 20 3000 4100
7876 20 1100 7100
7788 20 3000 7075
7566 20 2975 15975
7369 20 10000 12975
7900 30 950 2450
7844 30 1500 5300
7698 30 2850 5600
EMPNO DEPTNO SUM(SAL) ROWS_SUM
---------- ---------- --------------------
7654 30 1250 5350
7521 30 1250 4100
7499 30 1600 2850
下面我们来看range是怎么样进行偏移的。
CenteredAggregate Function
SQL> SELECT EMPNO,
2 DEPTNO,
3 SUM(SAL),
4 SUM(SAL) OVER(PARTITION BY DEPTNO ORDER BY SAL DESC RANGE BETWEEN 10
0 PRECEDING AND 300FOLLOWING) ROWS_SUM
5 FROM EMP
6 GROUP BY DEPTNO, EMPNO, SAL;
EMPNO DEPTNO SUM(SAL) ROWS_SUM
---------- ---------- --------------------
7839 10 5000 5000
7782 10 2450 2450
7934 10 1300 1300
7369 20 10000 10000
7788 20 3000 8975
7902 20 3000 8975
7566 20 2975 8975
7876 20 1100 1100
7698 30 2850 2850
7499 30 1600 3100 ----开始处
7844 30 1500 5600 --假如这个是当前行，那么上面就表示的范围是1500-300=1200 1600之间
EMPNO DEPTNO SUM(SAL) ROWS_SUM
---------- -------------------- ----------
7521 30 1250 3450
7654 30 1250 3450--结束处
7900 30 950 950
升序range窗口preceding含义是比当前行小xx值，following含义是比当前行大xx值，降序range窗口preceding含义是比当前行大xx值，following含义是比当前行小xx值。
3.Reporting函数
语法如下：
{SUM |AVG | MAX | MIN | COUNT | STDDEV | VARIANCE ... }
([ALL | DISTINCT] {value expression1[,...] | *})
OVER ([PARTITION BY valueexpression2[,...]])
这个比较简单不在用实例说明了。
RATIO_TO_REPORTFunction
语法如下：RATIO_TO_REPORT( expr ) OVER ( [query_partition_clause] )
这个函数表示expr这个值在expr所在字段和中所占的比例，实例如下：
SQL> SELECT DEPTNO,
2 EMPNO,
3 SUM(SAL) SAL,
4 SUM(SAL) OVER() TOTAL,
5 to_char(RATIO_TO_REPORT(SUM(sal))OVER(),'9990.99') RATIO
6 FROM EMP
7 GROUP BY DEPTNO, EMPNO,sal;
DEPTNO EMPNO SAL TOTAL RATIO
---------- ---------- ---------- ---------- --------
30 7499 1600 38225 0.04 --1600/38225
20 7566 2975 38225 0.08
20 7788 3000 38225 0.08
10 7839 5000 38225 0.13
20 7876 1100 38225 0.03
20 7369 10000 38225 0.26
30 7654 1250 38225 0.03
30 7521 1250 38225 0.03
20 7902 3000 38225 0.08
10 7782 2450 38225 0.06
10 7934 1300 38225 0.03
DEPTNO EMPNO SAL TOTAL RATIO
---------- ---------- ---------- ---------- --------
30 7698 2850 38225 0.07
30 7844 1500 38225 0.04
30 7900 950 38225 0.02
4.LAG/LEAD
语法如下：
{LAG | LEAD} (value_expr [, offset] [, default] ) [RESPECT NULLS|IGNORE NULLS]
OVER ( [query_partition_clause] order_by_clause)
Offset 表示偏移量，当指定ignore nulls在发生偏移时oracle会忽略null。
例子如下：
SQL> SELECT DEPTNO,
2 empno,
3 sal,
4 LAG(sal,1) OVER(ORDER BY EMPNO DESC)LA,
5 LEAD(SAL,1) OVER(ORDER BY EMPNO DESC)LE
6 FROM EMP
7 GROUP BY DEPTNO,empno,sal;
DEPTNO EMPNO SAL LA LE
---------- ---------- ---------- ---------- ----------
10 7934 1300 3000
20 7902 3000 1300 950
30 7900 950 3000 1100
20 7876 1100 950 1500
30 7844 1500 1100 5000
10 7839 5000 1500 3000
20 7788 3000 5000 2450
10 7782 2450 3000 2850
30 7698 2850 2450 1250
30 7654 1250 2850 2975
20 7566 2975 1250 1250
DEPTNO EMPNO SAL LA LE
---------- ---------- ---------- ---------- ----------
30 7521 1250 2975 1600
30 7499 1600 1250 10000
20 7369 10000 1600
5.FIRST_VALUEand LAST_VALUE Functions
语法如下：
FIRST_VALUE|LAST_VALUE( <expr> ) [RESPECT NULLS|IGNORE NULLS] OVER (analytic clause );
这个函数用来返回窗口的第一个值或者最后一个值,但是这个有一个有趣的例子，我们来看看
先来看一个例子:
SQL> SELECT DEPTNO,
2 EMPNO,
3 sal,
4 FIRST_VALUE(SAL) OVER(PARTITION BYDEPTNO ORDER BY EMPNO ROWS betwee
n unbounded preceding and current row) FIRST,
5 LAST_VALUE(SAL) OVER(PARTITION BYDEPTNO ORDER BY empno rows between
unbounded precedingand unbounded following) LAST,
6 LAST_VALUE(SAL) OVER(PARTITION BYDEPTNO ORDER BY empno rows between
unbounded precedingand current row) LAST2,
7 LAST_VALUE(SAL) OVER(PARTITION BYDEPTNO ORDER BY empno) last3
8 FROM EMP
9 ;
DEPTNO EMPNO SAL FIRST LAST LAST2 LAST3
---------- ---------- ---------- ---------- -------------------- ----------
10 7782 2450 2450 1300 2450 2450
10 7839 5000 2450 1300 5000 5000
10 7934 1300 2450 1300 1300 1300
20 7369 10000 10000 3000 10000 10000
20 7566 2975 10000 3000 2975 2975
20 7788 3000 10000 3000 3000 3000
20 7876 1100 10000 3000 1100 1100
20 7902 3000 10000 3000 3000 3000
30 7499 1600 1600 950 1600 1600
30 7521 1250 1600 950 1250 1250
30 7654 1250 1600 950 1250 1250
DEPTNO EMPNO SAL FIRST LAST LAST2 LAST3
---------- ---------- ---------- ---------- -------------------- ----------
30 7698 2850 1600 950 2850 2850
30 7844 1500 1600 950 1500 1500
30 7900 950 1600 950 950 950
看last3字段不是应该返回窗口的最后一个值吗，但是结果总是让人失望的，下面是我在pub上找到的一位大神的解释。
--大神的解释
对于分析函数，大家要注意：
1. 分析函数是在整个SQL查询结束后(SQL语句中的ORDERBY的执行比较特殊)再进行的操作,也就是说
SQL语句中的ORDER BY也会影响分析函数的执行结果,请看:
SQL> select deptno,
2 empno,
3 ename,
4 sal,
5 hiredate,
6 last_value(sal) over(partition by deptno) last_value
7 from emp
8 where deptno =30;
DEPTNO EMPNO ENAME SAL HIREDATE last_value
------ ----- ---------- -------------------- -----------
30 7499ALLEN 1600.00 1981-02-20 950
30 7521WARD 1250.00 1981-02-22 950
30 7654MARTIN 1250.00 1981-09-28 950
30 7698BLAKE 2850.00 1981-05-01 950
30 7844TURNER 1500.00 1981-09-08 950
30 7900JAMES 950.00 1981-12-03 950
6 rows selected
SQL> select deptno,
2 empno,
3 ename,
4 sal,
5 hiredate,
6 last_value(sal) over(partition by deptno) last_value
7 from emp
8 where deptno =30
9 order bydeptno,mgr;
DEPTNO EMPNO ENAME SAL HIREDATE last_value
------ ----- ---------- -------------------- -----------
30 7499ALLEN 1600.00 1981-02-20 2850
30 7521WARD 1250.00 1981-02-22 2850
30 7654MARTIN 1250.00 1981-09-28 2850
30 7900JAMES 950.00 1981-12-03 2850
30 7844 TURNER 1500.00 1981-09-08 2850
30 7698BLAKE 2850.00 1981-05-01 2850
6 rows selected
SQL> select deptno,
2 empno,
3 ename,
4 sal,
5 hiredate,
6 last_value(sal) over(partition by deptno) last_value
7 from emp
8 where deptno =30
9 order bydeptno,mgr desc;
DEPTNO EMPNO ENAME SAL HIREDATE last_value
------ ----- ---------- -------------------- -----------
30 7698 BLAKE 2850.00 1981-05-01 1250
30 7499ALLEN 1600.00 1981-02-20 1250
30 7521WARD 1250.00 1981-02-22 1250
30 7900JAMES 950.00 1981-12-03 1250
30 7844TURNER 1500.00 1981-09-08 1250
30 7654MARTIN 1250.00 1981-09-28 1250
6 rows selected
从上面的结果我们分析得出:
a) 如果SQL语句中的OrderBy满足分析函数分析时要求的排序,那么SQL语句中的排序将先执行,分析
函数分析时就不必再排序
b) 如果SQL语句中的OrderBy不满足分析函数分析时要求的排序，那么SQL语句中的排序将最后在分
析函数分析结束后执行排序
2. 分析函数中包含三个分析子句:分组(Partition By), 排序(OrderBy),窗口(Window)
窗口就是分析函数分析时要处理的数据范围,就拿SUM来说,它是SUM窗口中的记录而不是整个分组中
的记录,因此我们在想得到某个栏位的累计值时,我们需要把窗口指定到该分组中的第一行数据到当
前行,如果你指定该窗口从该分组中的第一行到最后一行,那么该组中的每一个SUM值都会一样,即整
个组的总和.
窗口子句在这里我只说rows方式的窗口,range方式的这里不提,此外滑动窗口也不提.窗口子句中
我们经常用到指定第一行,当前行,最后一行这样的三个属性.第一行是unbounded preceding,当
前行是 current row,最后一行是unbounded following.
出现窗口子句,必须指定Order By子句,如:
last_value(sal) over
(partition by deptno order by sal rowsbetween unbounded preceding and unbounded following)
以上示例指定窗口为整个分组.
当省略窗口子句时:
a) 如果存在Order By则默认的窗口是unboundedpreceding and current row
b) 如果同时省略Order By则默认的窗口是unboundedpreceding and unbounded following
如果省略分组,则把全部记录当成一个组:
a) 如果存在Order By则默认窗口是unboundedpreceding and current row
b) 如果这时省略Order By则窗口默认为unboundedpreceding and unbounded following
来自 <http://www.itpub.net/thread-717608-2-1.html>
Advanced Aggregates for Analysis
LISTAGG Function
这个函数是11g新提出来的，语法如下：
LISTAGG (<expr>[, <delimiter>) WITHIN GROUP (ORDER BY <oby_expression_list>)
Expr表示你要做处理的字段。
delimiter表示分隔符。
oby_expression_list表示要按照什么排序。
看一个例子就明白了listagg做了什么，下面这个sql列出了一个营业部所有的人员的名称，并使用逗号分隔。
SQL> SELECT DEPTNO,
2 LISTAGG(ename,',') WITHINGROUP(ORDER BY DEPTNO) enamelist
3 FROM EMP
4 GROUP BY DEPTNO;
DEPTNO ENAMELIST
------------------------
10CLARK,KING,MILLER
20ADAMS,FORD,JONES,SCOTT,SMITH
30ALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD
FIRST/LAST Functions
语法如下：
aggregate_functionKEEP ( DENSE_RANK LAST ORDER BY
expr [ DESC | ASC ] [NULLS { FIRST |LAST }]
[, expr [ DESC | ASC ] [NULLS { FIRST |LAST }]]...)
[OVER query_partitioning_clause]
下面来看这个例子：
找出emp表中每个部门工资的最大值和最小值
SQL> SELECT DEPTNO,
2 ENAME,
3 EMPNO,
4 SAL,
5 hiredate,
6 MAX(SAL) KEEP(DENSE_RANK FIRST ORDER BY empno) OVER(PARTITION BY DEP
TNO)"min_sal",
7 MIN(SAL) KEEP(DENSE_RANK LAST ORDER BY empno) OVER(PARTITION BY DEPT
NO)"MAX_sal"
8 FROM EMP;
DEPTNO ENAME EMPNO SAL HIREDATE min_sal MAX_sal
---------- -------------------- ---------- -------------- ---------- ----------
10 CLARK 7782 2450 09-6月 -81 2450 1300
10 KING 7839 5000 17-11月-81 2450 1300
10 MILLER 7934 1300 23-1月 -82 2450 1300
20 JONES 7566 2975 02-4月 -81 10000 3000
20 FORD 7902 3000 03-12月-81 10000 3000
20 ADAMS 7876 1100 23-5月 -87 10000 3000
20 SMITH 7369 10000 17-12月-80 10000 3000
20 SCOTT 7788 3000 19-4月 -87 10000 3000
30 WARD 7521 1250 22-2月 -81 1600 950
30 TURNER 7844 1500 08-9月 -81 1600 950
30 ALLEN 7499 1600 20-2月 -81 1600 950
DEPTNO ENAME EMPNO SAL HIREDATE min_sal MAX_sal
---------- -------------------- ---------- -------------- ---------- ----------
30 JAMES 7900 950 03-12月-81 1600 950
30 BLAKE 7698 2850 01-5月 -81 1600 950
30 MARTIN 7654 1250 28-9月 -81 1600 950
一些比较好的例子
连续数问题
要求对ID相同，num连续的，查找最小num以及val求和。
表中数据如下所示：
SQL> select * fromtest_tab;
ID NUM VAL
---------- --------------------
1 1 50
1 2 100
1 3 150
1 5 250
2 1 100
2 3 400
3 1 100
3 2 200
SQL> SELECT ID,MIN(NUM), SUM(VAL)
2 FROM (SELECT ID,
3 NUM,
4 VAL,
5 NUM - ROW_NUMBER()OVER(PARTITION BY ID ORDER BY NUM) RN --使用当前值来减去rownumber
6 FROM TEST_TAB)
7 GROUP BY ID, RN
8 ORDER BY 1, 2
9 ;
ID MIN(NUM) SUM(VAL)
---------- --------------------
1 1 300
1 5 250
2 1 100
2 3 400
3 1 300
模拟SQL*PLUS BREAK 问题
SQL*PLUS提供BREAK命令，就是当前列值与前面相同，则置NULL，这是报表常用的一种手段。
SQL> break on deptno;
SQL> select deptno,empno,ename from emp wheredeptno<30 order by deptno;
DEPTNO EMPNO ENAME
---------- ---------- ----------
10 7934 MILLER
7782 CLARK
7839 KING
20 7902 FORD
7876 ADAMS
7566 JONES
7369 SMITH
7788 SCOTT
已选择8行。
SQL> SELECT DECODE(LAG(DEPTNO, 1) OVER(PARTITIONBY DEPTNO ORDER BY EMPNO),
2 DEPTNO,
3 NULL,
4 DEPTNO) deptno,
5 EMPNO,
6 ENAME
7 FROM EMP
8 WHERE deptno<30;
DEPTNO EMPNO ENAME
---------- ---------- ----------
10 7782 CLARK
7839 KING
7934 MILLER
20 7369 SMITH
7566 JONES
7788 SCOTT
7876 ADAMS
7902 FORD
重复行问题
SQL> select *from duprows;
ID NAME
--------------------
1 name
2 name
1 name
3 name
1 name
--通过以下方式找到重复行
SQL> SELECT *
2 FROM (SELECT ID,
3 NAME,
4 ROW_NUMBER() OVER(PARTITION BYB.ID ORDER BY B.ROWID) RN
5 FROM DUPROWS B) C
6 WHERE C.RN > 1;
ID NAME RN
-------------------- ----------
1 name 2
1 name 3
金额摊派问题
平均分派问题，如何将金额平均分摊，并且小数也分摊掉，避免误差
SQL> select *from demo7_1;
ID AMOUNT
--------------------
1 100
2 50
SQL> select *from demo7_2;
ID PERSONS
--------------------
1 3
2 2
SELECT ID,
PERSONS,
(CASE
WHEN RN <= (AMOUNT - AMOUNT2) * 100THEN
0.01
ELSE
0
END) + JE AS JE,
AMOUNT
--然后排序，与总金额有差额的补0.01
FROM (SELECT T.*,
SUM(JE) OVER(PARTITION BY ID) ASAMOUNT2,
ROW_NUMBER() OVER(PARTITION BYID ORDER BY JE DESC) RN
FROM (
--先展开记录数，用trunc先平均,只舍不入
SELECT TT.*
FROM (SELECT T2.ID,
T2.PERSONS,
TRUNC(T1.AMOUNT/ T2.PERSONS, 2) JE,
T1.AMOUNTAMOUNT
FROM DEMO7_1 T1,DEMO7_2 T2
WHERE T1.ID = T2.ID)TT,
--构造最大的人数序列
(SELECT LEVEL RN
FROM DUAL
CONNECT BY LEVEL <=
(SELECTMAX(PERSONS) MAX_NUM FROM DEMO7_2)) TM
WHERE TT.PERSONS >= TM.RN)T)
ID PERSONS JE AMOUNT
-------------------- ---------- ----------
1 3 33.34 100
1 3 33.33 100
1 3 33.33 100
2 2 25 50
2 2 25 50