这个也是从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.
|
