这个也是从asktom上看到的，绝对还不错
Crete table temp_exception (field1
varchar2(20),
field2
varchar2(240);
Record1
Skipped because of the record status
Record1
No valid value on social security column
Record2
Skipped, no matching record found
Record2
Validation failed for some reason
...
I want to get an output as
Record1
Skipped because of the record status
No valid value on social security column
Record2
Skipped, no matching record found
Validation failed for some reason
tom巧妙运用了lag函数实现了功能
ops$tkyte@ORA10GR2 edit
Wrote file afiedt.buf
1
select decode( lag(deptno) over (order by deptno), deptno, to_number(null),
deptno ) new_deptno,
2
ename
3
from emp
4* order by deptno
ops$tkyte@ORA10GR2 /
NEW_DEPTNO ENAME
---------- ------------------------------
10 CLARK
KING
MILLER
20 JONES
FORD
ADAMS
SMITH
SCOTT
30 WARD
TURNER
ALLEN
JAMES
BLAKE
MARTIN
14 rows selected.