怎么样能做到 当前纪录的某个column 和前一个记录的column比较select column-lag(column,1,0) over(order by rownum) from 表名 where 凭单编号 in (select 凭单编号 from 表名 group by 凭单编号 having count(*)>1)
lag(..) over(...) --是分析函数。 参考:http://download-west.oracle.com/docs/cd/A87860_01/doc/server.817/a85397/function.htm#83619 例子: SELECT ename, hiredate, sal, LAG(sal, 1, 0) OVER (ORDER BY hiredate) as prev_sal FROM emp WHERE job = 'SALESMAN';ENAME HIREDATE SAL PREV_SAL ---------- --------- ---------- ---------- ALLEN 20-FEB-81 1600 0 WARD 22-FEB-81 1250 1600 TURNER 08-SEP-81 1500 1250 MARTIN 28-SEP-81 1250 1500
(select rownum id1,tb.* from tb) a,
(select rownum+1 id2,tb.* from tb) b
where a.id1=b.id2(+);在同一行就可以比较了吧
可能不合实际
想去掉凭单编号相同多余的record
2.做primary key
(select 凭单编号 from 表名 group by 凭单编号 having count(*)>1)
参考:http://download-west.oracle.com/docs/cd/A87860_01/doc/server.817/a85397/function.htm#83619
例子:
SELECT ename, hiredate, sal,
LAG(sal, 1, 0) OVER (ORDER BY hiredate) as prev_sal
FROM emp
WHERE job = 'SALESMAN';ENAME HIREDATE SAL PREV_SAL
---------- --------- ---------- ----------
ALLEN 20-FEB-81 1600 0
WARD 22-FEB-81 1250 1600
TURNER 08-SEP-81 1500 1250
MARTIN 28-SEP-81 1250 1500