这个也是从asktom上看到的,绝对还不错
Crete table temp_exception (field1varchar2(20),
field2varchar2(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
1select decode( lag(deptno) over (order by deptno), deptno, to_number(null),
deptno ) new_deptno,
2ename
3from 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.