SELECT A.seq,A.decisionid,A.instid,A.modelid,A.nodeid,A.optrid,A.opttime AS opttime,
A.result,A.re,B.username,C.nodename,D.orgname,E.re AS decisionre
FROM tb_bpm_instancenode A LEFT JOIN tb_bpm_decision E
ON E.decisionid=A.decisionid AND E.modelid=A.modelid AND E.nodeid=A.nodeid,
tb_user B,tb_bpm_node C,tb_organization D
WHERE A.optrid=B.userid AND C.nodeid=A.nodeid AND D.orgcode=B.orgcode
AND A.instid='3BN10120000000030'
ORDER BY A.opttime,nodeid ;
我的问题是这样的 就是说我想将 查询出的nodeid 进行上下相邻两个记录的nodeid的累加 形成新的一个值 num
比如 假设查询出的nodeid为:
1
2
3
4
然后我需要自己错位再相加 那么num就是:(1+2), (2+3), (3+4)
明白我的意思了吧? 请问这样能不能用sql 写出来
2 select ename,sal,lag(sal) over(order by sal) lagsal from emp);ENAME SAL LAGSAL+SAL
---------- ---------- ----------
SMITH 800
JAMES 950 1750
ADAMS 1100 2050
WARD 1250 2350
MARTIN 1250 2500
MILLER 1430 2680
TURNER 1500 2930
ALLEN 1600 3100
CLARK 2450 4050
BLAKE 2850 5300
JONES 2975 5825
SCOTT 3000 5975
FORD 3000 6000
KING 5000 8000已选择14行。
你的需求不是很明确,加完之后记录如何做变化?
WITH tab AS(
SELECT ROWNUM nodeid FROM dual CONNECT BY ROWNUM<=4
)
SELECT nodeid,nodeid+lead(nodeid,1,0)over(ORDER BY nodeid) num FROM tab
NODEID NUM
-----------------
1 3
2 5
3 7
4 9
5 5
nodeid,num
1 3
2 5
3
我就希望是这样的
WITH tab AS(
SELECT ROWNUM nodeid FROM dual CONNECT BY ROWNUM<=4
)
SELECT nodeid,nodeid+lead(nodeid,1)over(ORDER BY nodeid) num FROM tab
NODEID NUM
-----------------
1 3
2 5
3 7
4 9
5